IT Process Automation

  • Private Community

  • 1.  SQL Server Bulk Insert Operator Query

    Posted Jan 29, 2012 06:30 AM
    Hi,

    I need to open a file, get that data into a dataset and then write the values of the multi-row data set in to SQL Server 2008.

    I have a small flow that works up to the point where I try to call a Bulk Insert operator, which fails telling me that I have this error:
    "The interpreted datatype of the value in JDBCInsertMapArray does not match the destination datatype."

    The SQL connection is good - I have been able to verify this with a simple SQL Insert using literal values - a row appears in my database.
    I have the dataset loaded and all values appear.
    The dataset is type Named, with all fields set to string. Same datatypes are on the database.
    I have made sure all fields in the dataset are named exactly the same as the columns on the target table (Don't know if this is even required).

    I think the actual error message is misleading because I can deliberately change the dataset name or the (Data Source???) table name to nonsense and I get the same error.

    Can anyone help with general tips on Bulk Insert, even if they can't see my problem?

    Bob.


  • 2.  RE: SQL Server Bulk Insert Operator Query

    Posted Jan 30, 2012 03:06 AM
    Hi,
    i would try to log the generated sql commands from the bulk operator with the SQL server profiler.

    Regards
    Stefan


  • 3.  RE: SQL Server Bulk Insert Operator Query
    Best Answer

    Posted Jan 30, 2012 09:37 AM
    All fixed. This was down to two things:

    1) Me not fully unserstanding that the indexing must be at the top valuemap level and not at the field level underneath. Makes sense.

    2) The Bulk Insert operator needing to be fed with apostrophe delimited field values, despite everything being String type in the valuemap definition.

    As always, a little time and lots of noodling got me there.

    I don't like that forced data type thing on Bulk Insert however. On a normal SQL Insert operator you can correct this by naming the columns that you insert into. That seems to force the operator to do datatyping properly, but you get a similar problem with an error "String or binary data would be truncated" if you don't name the columns being inserted into.

    Thanks for any/all responses.

    Bob.