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 CSV Bulk insert issue

More
28 Sep 2016 07:52 #1 by Prashant.Sangle
CSV Bulk insert issue was created by Prashant.Sangle
Hi,

I have CSV file which I want to insert into db

If I try with Action Process LOAD File to Mssql (with BULK) template it gives me below error
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot bulk load. Invalid number of columns in the format file "e:\devl\IMS/SSIM07L.fmt".

If i try with Action Process LOAD File to Rdbms template then It work fine.

I am attaching screen shot of error and also sample file.
Please help me to resolve error.

Let me know if any other details required.

Regards,
Prashant
Attachments:
More
28 Sep 2016 14:48 #2 by Nikolay
Replied by Nikolay on topic CSV Bulk insert issue
Template Action Process LOAD File to Rdbms does not use Bulk Load. It uses Select from the source and insert to the destination
Probably you have an errors at the metadata. Check if you used whitespaces in the names of the fields.
Moreover you can compare fmt file that Stambia generates with fmt file of bcp out.
More
28 Sep 2016 16:04 #3 by Prashant.Sangle
Replied by Prashant.Sangle on topic CSV Bulk insert issue
Hi,

To test my mapping is correct or not, I am using Template Action Process LOAD File to Rdbms.
For bulk load i am using Template Action Process LOAD File to Mssql (with BULK) template which is giving me error.

also If there is errors at the metadata then how could my mapping executed successfully when i executed my mapping with Template Action Process LOAD File to Rdbms. and I have check there is no whitespace in the names of the fields.

Also your suggestion for compare fmt file that Stambia generates with fmt file of bcp out, I will check and get back to you.

Thanks for help.

Regards,
Prashant
More
29 Sep 2016 16:06 - 29 Sep 2016 16:06 #4 by Thomas BLETON
Replied by Thomas BLETON on topic CSV Bulk insert issue

Prashant.Sangle wrote: [...]If there is errors at the metadata then how could my mapping executed successfully when i executed my mapping with Template Action Process LOAD File to Rdbms. [...]


Because these two templates (File To Rdbms & File to Mssql (with BULK)) use different technologies to load the data (Jdbc vs. Mssql's BULK tool). That's why there can be different behaviors ;)

Prashant.Sangle wrote: [...]Also your suggestion for compare fmt file that Stambia generates with fmt file of bcp out, I will check and get back to you.[...]

This is a good practice when having issues with BULK-like tools:
- try loading the file out of Stambia, using directly the tools
- compare the working FMT file with Stambia-generated to see the differences
- adjust your Stambia metadata & template options to obtain the desired FMT file.
Last edit: 29 Sep 2016 16:06 by Thomas BLETON.