SQLXMLBulkLoad via VBScript

Question

I'm running the following code:

    Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
    objBL.ConnectionString = "provider=SQLOLEDB.1;data 

    source=mySQLServer;database=myDB;uid=SA;pwd=myPword"
    objBL.ErrorLogFile = "c:\error.log"
    objBL.Execute "c:\xml\schema.xml", "c:\xml\addresses.xml"
    Set objBL = Nothing

I'm attempting to load a rather large XML file into the SQL table, however I'm receiving the error "Cannot insert the value NULL into column 'LocID', table 'myDB.dbo.myTable'; column does not allow nulls. INSERT fails."

"LocID" is my primary key and Identity field. Is there a way to prevent it from inserting NULL into this column, since I'm just loading data, not moving it and I don't have ID fields in the XML file?

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

Add this before objBL.Execute

objBL.KeepIdentity = False

From the MSDN Article SQL Server XML Bulk Load Object Model (SQLXML 4.0) (emphasis mine)

KeepIdentity

Specifies how to deal with the values for an Identity type column in the source file. This is a Boolean property. When the property is set to TRUE, XML Bulk Load assigns the values that are specified in the source file to the identity column. When the property is set to FALSE, the bulk-load operation ignores the identity-column values that are specified in the source. In this case, SQL Server assigns a value to the identity column.

If the Bulk Load involves a column that is a foreign key referring to an identity column in which SQL Server-generated values are stored, Bulk Load appropriately propagates these identity values to the foreign key column.

The value of this property applies to all columns involved in the bulk load. The default value is TRUE.

Worked perfectly! Thanks. I actually found this yesterday while looking through the MSDN documentation on SQLXMLBulkLoad object, but I couldn't add the answer for several hours. Glad someone did! — Dexter, Apr 22 '11 at 20:29

score 1 · Answer 2 · answered Apr 21 '11 at 22:59

If your doing a BulkInsert into a Database, you should do a Bulk Insert into Temporary Tables where each Column Can be NULL and i normally set my Size to VARCHAR(800).

Once it is in those Tables, i would then ETL and load it into my master tables.

Hope this helps you out.

SQLXMLBulkLoad via VBScript

2 Answers2