Welcome Guest! Log in

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

More
06 Apr 2016 11:32 #1 by letancel
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

Please Log in or Create an account to join the conversation.

More
06 Apr 2016 16:59 - 06 Apr 2016 16:59 #2 by Thomas BLETON
Replied by Thomas BLETON on 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.

Please Log in or Create an account to join the conversation.

More
06 Apr 2016 17:14 #3 by letancel
Hi,

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

Is it a known issue?

Please Log in or Create an account to join the conversation.

More
06 Apr 2016 17:28 - 06 Apr 2016 17:31 #4 by Thomas BLETON
Replied by Thomas BLETON on 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.

Please Log in or Create an account to join the conversation.

More
06 Apr 2016 17:55 #5 by letancel
Alright, I've never heard about it! SQL Server...

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

Thanks

Please Log in or Create an account to join the conversation.