Welcome Guest! Log in
×

Notice

The forum is in read only mode.
Due to some maintenance operations, stambia.org will be switched to read-only mode during the 13th November. It will be possible to read and download, but impossible to post on the forums or create new accounts. For any question please contact the support team.

Topic-icon Question Bulk issue : difference between DAT file and database

More
06 Apr 2016 11:32 #1 by letancel
letancel created the topic: Bulk issue : difference between DAT file and database
Hi,

I have a mapping with DB2/400 tables in source and SQL Server table in target.
I have an weird issue when I'm using "Action process LOAD Rdbms to Mssql (with BULK)" :
When I run the mapping, the result is that I have 88105 rows in source, 88105 rows in the DAT file aaaand 88104 rows in the target table..
It appears that the last line in the DAT file is not loaded in the target table...

What is happening?

Thanks
More
06 Apr 2016 16:59 - 06 Apr 2016 16:59 #2 by Thomas BLETON
Thomas BLETON replied the topic: Bulk issue : difference between DAT file and database
It appears that there are issues with BULK INSERT when the last field of the last row is empty.
For example :
-----------------
val1.1~val1.2
val2.1~val2.2
val3.1~<EOF>
-----------------

=> in this case, BULK INSERT ignores the last line.
Is this your case, does your last line of data end with an empty field ?
If yes, then we can help you modify the Template to force a line break after the last row.
Last Edit: 06 Apr 2016 16:59 by Thomas BLETON.
More
06 Apr 2016 17:14 #3 by letancel
letancel replied the topic: Bulk issue : difference between DAT file and database
Hi,

Yes it's the case here, the last value of the line is empty..

Is it a known issue?
More
06 Apr 2016 17:28 - 06 Apr 2016 17:31 #4 by Thomas BLETON
Thomas BLETON replied the topic: Bulk issue : difference between DAT file and database
I found some similar cases on internet:
www.sqlservercentral.com/Forums/Topic1228778-357-2.aspx
www.pcreview.co.uk/threads/bulk-insert-p...row-is-null.2227230/

... but no real conclusion on being a BULK INSERT issue or a misconfiguration.

However I just tested a workaround in the template, which works for me :
- Open your "Load Rdbms to Mssql (with BULK)" template
- Double click the "Load" step
- Add a parameter, Name: "rowSepOnLastRow", Type: "Boolean", Value: "True".
- The ".dat" file will be generated with a last "end-of-line" character, which avoids the issue.
- Save the template and test the mapping.

Does it work for you ? If yes I will submit the modification to our dev team so that it's included in the official template version...
Last Edit: 06 Apr 2016 17:31 by Thomas BLETON.
More
06 Apr 2016 17:55 #5 by letancel
letancel replied the topic: Bulk issue : difference between DAT file and database
Alright, I've never heard about it! SQL Server...

Anyway, I did what you told me and it worked perfectly!

Thanks