SQL Server Bulk Load Error

[johndeere]

I am getting an un specified error when I use the bulk load soure. How can I determine what the actual error is.

[shawn_ramsey]

I am getting an un specified error when I use the bulk load soure. How can I determine what the actual error is.

The best way to track it down is to run the Microsoft SQL Profiler and filter it to trap the actual error. I have put a ticket into Ascential on this and the OLEDB stage not returning the actual error message a while ago but there still has not been a fix. I was told at one time the Microsoft API did not return the actual error message.

[davidnemirovsky]

Have a 'Reject' Sequential File stage out of the transformer stage directly before the SQL Server Bulk Loader stage. In your transformer contraints make sure 'Reject Row' is ticked for the 'Rejects' link. Define 5 columns in the rejects file and capture the Link Variables (Output) DBMSCODE, LASTERR, REJECTED, REJECTEDCODE and SQLSTATE. Ie. Derivation for the columns as follows:

Code: Select all

[RejectLinkName].DBMSCODE

View the file for the bulk load error.

[johndeere]

Thanks for the tip but the values returned do not seem to be of much help.

LASTERR = 2004-12-18 00:07:40\CILAN|LANCEH\2\AptifyPerson_Seq_file_BulkLoad..Transformer_47: At row 10, link "LinkBulkLoadPerson"?Failed to insert the row.
REJECTED = 0
REJECTEDCODE = -100
DBMSCODE blank
SQLSTATE blank

I was not able to see any activity in profiler. What events should I watch.

[ray.wurlod]

Well you've got a bit more. Row 10 is the culprit. Take a look at the data in row 10 of the load file, and see whether you can figure out why it can not be inserted. Perhaps a duplicate key value? Perhaps an invalidly formed date?