Welcome Guest! Log in
×

Notice

The forum is in read only mode.
Stambia versions 2.x, 3.x, S17, S18, S19 and S20 are reaching End of Support January, 15th, 2024. Please consider upgrading to the supported Semarchy xDI versions. See Global Policy Support and the Semarchy Documentation.

The Stambia User Community is moving to Semarchy! All the applicable resources have already been moved or are currently being moved to their new location. Read more…

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

More
06 Apr 2016 11:32 #1 by letancel
Bulk issue : difference between DAT file and database was created 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
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.
More
06 Apr 2016 17:14 #3 by letancel
Replied by letancel on 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
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.
More
06 Apr 2016 17:55 #5 by letancel
Replied by letancel on 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