XML Bulk Load XSD and Identity column problem

Here is further detail on the problem - please can anyone help?

Hi,
we are using XML Bulk Load to insert a large amount of data into a SQL 2000 database. I am having a problem with an Identity column which is in a table I am inserting into but is not mapped in the schema or in the original file.

The table structures is:

ProductDescription Table -

ProductDescriptionRowID - int - 4 - identity - No Nulls
ProductID - int - 4 - Nulls Allowed
DescriptionPhraseID - nvarcher - 10 - Nulls Allowed

Example of the XML is:

<Root>
<Products>
<Product>
<ProductID>169</ProductID>
<ProductName>Product Full Text Name</ProductName>
<ProductDescriptions>
<Description>00001082</Description>
</ProductDescriptions>
<CountryID>gbr</CountryID>
<PaintType>00000006</PaintType>
<PaintSubType>00000005</PaintSubType>
<SchemeAreas></SchemeAreas>
<ApplicationMethods>
<ApplicationMethod>A</ApplicationMethod>
<ApplicationMethod>B</ApplicationMethod>
<ApplicationMethod>R</ApplicationMethod>
</ApplicationMethods>
<Schemes>
<SchemeID>67</SchemeID>
<SchemeID>71</SchemeID>
<SchemeID>5090</SchemeID>
<SchemeID>5096</SchemeID>
<SchemeID>5106</SchemeID>
</Schemes>
<BulkCodes>
<BulkCode>CPA097</BulkCode>
<BulkCode>CPA098</BulkCode>
<BulkCode>CPA099</BulkCode>
</BulkCodes>
</Product>
</Products>
<Schemes>
<Scheme>
<SchemeID>5106</SchemeID>
<Description>Scheme Description Text</Description>
<Reference>Reference ID</Reference>
<CountryID>gbr</CountryID>
<ApplicationAreas>
<ApplicationArea>00000030</ApplicationArea>
<ApplicationArea>00000031</ApplicationArea>
</ApplicationAreas>
<ProjectTypes>
<ProjectType>N</ProjectType>
</ProjectTypes>
<Substrates>
<Substrate>00000005</Substrate>
</Substrates>
<Systems>
<System>00000002</System>
</Systems>
</Scheme>
</Schemes>
</Root>

I need to have the identity column incremented every time each Product Description is entered into the table. Currently in order to get the Bulk Load to run I am having to delete the column from the table and then add it once the bulk load has completed. How can I either edit the XSD file (pasted below) or the program code to allow this behaviour?

<?xml version="1.0" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:annotation>
<xs:appinfo>
<sql:relationship name="rel_productDescription" parent="Products" parent-key="ProductID" child="ProductDescription"
child-key="ProductID" />
<sql:relationship name="rel_countryProducts" parent="Products" parent-key="ProductID" child="CountryProducts"
child-key="ProductID" />
<sql:relationship name="rel_productPaintType" parent="Products" parent-key="ProductID" child="PaintTypes"
child-key="ProductID" />
<sql:relationship name="rel_productPaintSubType" parent="Products" parent-key="ProductID" child="PaintSubTypes"
child-key="ProductID" />
<sql:relationship name="rel_productPaintTypePaintSubType" parent="PaintTypes" parent-key="ProductID"
child="PaintSubTypes" child-key="ProductID" />
<sql:relationship name="rel_productSchemeAreas" parent="Products" parent-key="ProductID" child="SchemeAreas"
child-key="ProductID" />
<sql:relationship name="rel_productApplicationMethods" parent="Products" parent-key="ProductID" child="ApplicationMethods"
child-key="ProductID" />
<sql:relationship name="rel_productSchemes" parent="Products" parent-key="ProductID" child="ProductSchemes"
child-key="ProductID" />
<sql:relationship name="rel_productBulkCodes" parent="Products" parent-key="ProductID" child="ProductBulkCodes"
child-key="ProductID" />
<sql:relationship name="rel_schemeReference" parent="Schemes" parent-key="SchemeID" child="SchemeReference"
child-key="SchemeID" />
<sql:relationship name="rel_countrySchemes" parent="Schemes" parent-key="SchemeID" child="CountrySchemes"
child-key="SchemeID" />
<sql:relationship name="rel_schemeApplicationArea" parent="Schemes" parent-key="SchemeID" child="ApplicationAreas"
child-key="SchemeID" />
<sql:relationship name="rel_schemeProjectTypes" parent="Schemes" parent-key="SchemeID" child="ProjectTypes"
child-key="SchemeID" />
<sql:relationship name="rel_schemeSubstrate" parent="Schemes" parent-key="SchemeID" child="Substrates"
child-key="SchemeID" />
<sql:relationship name="rel_schemeSystem" parent="Schemes" parent-key="SchemeID" child="Systems" child-key="SchemeID" />
</xs:appinfo>
</xs:annotation>
<xs:element name="root" sql:is-constant="true">
<xs:complexType>
<xs:sequence>
<xs:element name="Products" sql:is-constant="true">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="Product" sql:relation="Products">
<xs:complexType>
<xs:sequence>
<xs:element name="ProductID" type="xs:unsignedInt" sql:field="ProductID" />
<xs:element name="ProductName" type="xs:string" sql:field="ProductName" />
<xs:element name="ProductDescriptions" sql:is-constant="true">
<xs:complexType>
<xs:sequence>
<!--<xs:element minOccurs="0" maxOccurs="1" name="Description" type="xs:string" sql:relation="ProductDescription"
sql:relationship="rel_productDescription" sql:field="DescriptionPhraseID" />-->
<xs:element minOccurs="0" maxOccurs="unbounded" name="Description" type="xs:string" sql:relation="ProductDescription"
sql:relationship="rel_productDescription" sql:field="DescriptionPhraseID" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="CountryID" type="xs:string" sql:relation="CountryProducts" sql:relationship="rel_countryProducts"
sql:field="CountryCode" />
<xs:element name="PaintType" type="xs:string" sql:relation="PaintTypes" sql:relationship="rel_productPaintType"
sql:field="PaintTypePhraseID" />
<xs:element name="PaintSubType" type="xs:string" sql:relation="PaintSubTypes" sql:relationship="rel_productPaintSubType"
sql:field="PaintSubTypePhraseID" />
<xs:element name="SchemeAreas" sql:is-constant="true">
<xs:complexType>
<xs:sequence minOccurs="0">
<xs:element maxOccurs="unbounded" name="SchemeArea" type="xs:string" sql:relation="SchemeAreas"
sql:relationship="rel_productSchemeAreas" sql:field="SchemeAreaPhraseID" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="ApplicationMethods" sql:is-constant="true">
<xs:complexType>
<xs:sequence minOccurs="0">
<xs:element maxOccurs="unbounded" name="ApplicationMethod" type="xs:string" sql:relation="ApplicationMethods"
sql:relationship="rel_productApplicationMethods" sql:field="ApplicationMethodPhraseID" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Schemes" sql:is-constant="true">
<xs:complexType>
<xs:sequence minOccurs="0">
<xs:element maxOccurs="unbounded" name="SchemeID" type="xs:unsignedInt" sql:relation="ProductSchemes"
sql:relationship="rel_productSchemes" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="BulkCodes" sql:is-constant="true">
<xs:complexType>
<xs:sequence minOccurs="0">
<xs:element maxOccurs="unbounded" name="BulkCode" type="xs:string" sql:relation="ProductBulkCodes"
sql:relationship="rel_productBulkCodes" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Schemes" sql:is-constant="true">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="Scheme" sql:relation="Schemes">
<xs:complexType>
<xs:sequence>
<xs:element name="SchemeID" type="xs:string" sql:field="SchemeID" />
<xs:element name="Description" type="xs:string" sql:field="Description" />
<xs:element name="Reference" type="xs:string" sql:relation="SchemeReference" sql:relationship="rel_schemeReference"
sql:field="Reference" />
<xs:element name="CountryID" type="xs:string" sql:relation="CountrySchemes" sql:relationship="rel_countrySchemes"
sql:field="CountryCode" />
<xs:element name="ApplicationAreas" sql:is-constant="true">
<xs:complexType>
<xs:sequence minOccurs="0">
<xs:element maxOccurs="unbounded" name="ApplicationArea" type="xs:string" sql:relation="ApplicationAreas"
sql:relationship="rel_schemeApplicationArea" sql:field="ApplicationAreaPhraseID" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="ProjectTypes" sql:is-constant="true">
<xs:complexType>
<xs:sequence minOccurs="0">
<xs:element maxOccurs="unbounded" name="ProjectType" type="xs:string" sql:relation="ProjectTypes"
sql:relationship="rel_schemeProjectTypes" sql:field="ProjectTypePhraseID" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Substrates" sql:is-constant="true">
<xs:complexType>
<xs:sequence minOccurs="0">
<xs:element name="Substrate" type="xs:string" sql:relation="Substrates" sql:relationship="rel_schemeSubstrate"
sql:field="SubstratePhraseID" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Systems" sql:is-constant="true">
<xs:complexType>
<xs:sequence minOccurs="0">
<xs:element name="System" type="xs:string" sql:relation="Systems" sql:relationship="rel_schemeSystem"
sql:field="SystemPhraseID" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

Program Code

public class SQLBulkLoadWrapper
{
public const int MAXLENGTH = 1024;

[DllImport("OLE32.DLL", EntryPoint="CreateStreamOnHGlobal")]
extern public static int CreateStreamOnHGlobal( int hGlobalMemHandle, bool fDeleteOnRelease, out UCOMIStream pOutStm);

protected SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class _bulkLoader;

public SQLBulkLoadWrapper()
{
this._bulkLoader = new SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class();
}

// set the bulk loader properties here...
public SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class BulkLoader
{
set { this._bulkLoader = value; }
get { return this._bulkLoader; }
}

public void Load(Stream source, string schemaPath)
{
UCOMIStream dataStream;
CreateStreamOnHGlobal(0, true, out dataStream);

byte[] dataBytes = new byte[MAXLENGTH];
int read = 0;

while ((read = source.Read(dataBytes, 0, MAXLENGTH)) > 0)
{
dataStream.Write(dataBytes, read, IntPtr.Zero);
}

source.Close();
dataStream.Seek(0, 0, System.IntPtr.Zero);
this._bulkLoader.Execute(schemaPath, dataStream);
}
}

Console App Code:

public class SQLBulkLoad
{
protected MPYA.Utilities.Xml.SQLBulkLoadWrapper _loader;
protected string _schemaPath;
protected string _xmlPath;

public SQLBulkLoad(string schemaPath,
string xmlPath,
string connectionString,
string errorLogPath)
{
this._loader = new MPYA.Utilities.Xml.SQLBulkLoadWrapper();
this._loader.BulkLoader.ConnectionString = connectionString;
this._loader.BulkLoader.ErrorLogFile = errorLogPath;
this._loader.BulkLoader.IgnoreDuplicateKeys = true;

this._schemaPath = schemaPath;
this._xmlPath = xmlPath;
}

public void LoadXml()
{
if (File.Exists(this._schemaPath) == false)
throw new ArgumentException("The specified schema file could not be found.");

DirectoryInfo xmlDirectory = new DirectoryInfo(this._xmlPath);

if (xmlDirectory.Exists == false)
throw new ArgumentException("The specified xml path does not exist.");

FileInfo[] files = xmlDirectory.GetFiles("*.xml");
foreach (FileInfo f in files)
{
Stream s = new FileStream(f.FullName, FileMode.Open, FileAccess.Read);
this._loader.Load(s, this._schemaPath);
}
}
}

Thanks for taking the time to look and help.

Andrew

AndrewWestgarth at 2007-10-8 >

I have a similar error and have posted the issue. Where do we set the KeepIdentity propery within SSIS?

I also tried editing the XSD and got this error:

"The component has detected potential metadata corruption during validation.
Error at Data Flow Task [XML Source [1]]: There was an error setting up the mapping. 'sql' is an undeclared namespace. Line 8, position 71.

It may not be possible to recover to a valid state using a component-specific editor. Do you want to use the Advanced Editor dialog box for editing this component? "

This is my XSD:

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="ComputerStatus">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="computer">
<xs:complexType>
<xs:attribute name="GUID" type="xs:string" use="optional" sql:field="GUID" />
<xs:attribute name="WSUSServer" type="xs:string" use="optional" sql:field="WSUSServer" />
<xs:attribute name="WSUSGroup" type="xs:string" use="optional" sql:field="WSUSGroup" />
<xs:attribute name="computerName" type="xs:string" use="optional" sql:field="computerName" />
<xs:attribute name="OSBuild" type="xs:unsignedShort" use="optional" sql:field="OSBuild" />
<xs:attribute name="OSSP" type="xs:unsignedByte" use="optional" sql:field="OSSP" />
<xs:attribute name="Model" type="xs:string" use="optional" sql:field="Model" />
<xs:attribute name="Make" type="xs:string" use="optional" sql:field="Make" />
<xs:attribute name="BIOS" type="xs:string" use="optional" sql:field="BIOS" />
<xs:attribute name="Processor" type="xs:string" use="optional" sql:field="Processor" />
<xs:attribute name="LastReportedStatus" type="xs:string" use="optional" sql:field="LastReportedStatus" />
<xs:attribute name="LastSyncTime" type="xs:string" use="optional" sql:field="LastSyncTime" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

Phil-UT at 2007-10-8 >