none
Getting an error while using ODBC to execute a DB2 database stored procedure from Script Task in SSIS 2012 RRS feed

  • Question

  • I'm trying to execute a script task that runs a stored procedure with parameters on a DB2 machine.

    It connects just fine but I get an error when executing saying that it "Failed to convert parameter value from a DateTime to a Byte[]."

    I have to input parameters of type OdbcType.Timestamp that seem to be the culprits.

                OdbcConnection Connection;
                OdbcCommand command;
                string strConnection;
    
                Connection = new OdbcConnection(strConnection);
                command = new OdbcCommand("StoredProcName", Connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("CSE_ENT_TMS", OdbcType.Timestamp);
                command.Parameters["CSE_ENT_TMS"].Value = Dts.Variables["User::EnteredTime"].Value;
                command.Parameters.Add("CSE_LST_MNT_TMS", OdbcType.Timestamp);
                command.Parameters["CSE_LST_MNT_TMS"].Value = Dts.Variables["User::LastMaintTMS"].Value;
    

    I've searched the internet to find a way that works to make the C# DateTime variables compatible with the ODBC timestamp bu to no avail. Changing to OdbcType.DateTime generates a different error:

    ERROR [42601] [IBM][CLI Driver][DB2] SQL0104N  An unexpected token "FRDINST3150TRANS" was found following "".  Expected tokens may include:  "<ERR_STMT> <WNG_STMT> GET SQL SAVEPOINT HOLD FREE AS".  SQLSTATE=42601

    Any ideas?

    Friday, December 11, 2015 12:35 AM

Answers

  • Thanks for the replies. What I ended up doing it switching to OLEDB and using the DBTimestamp type. It works fine now!
    Monday, December 14, 2015 8:57 PM

All replies

  • Hi Mike,

    Quite clearly, CSE_ENT_TMS is incompatible with time.

    Find out what data type is used on the DB2 end so we can try to chose the proper mapping.


    Arthur

    MyBlog


    Twitter

    Friday, December 11, 2015 2:25 AM
    Moderator
  • My source says

    CSE_ENT_TMS  TIMESTAMP(6) and

    CSE_LST_MNT_TMS    TIMESTAMP(6)

    Friday, December 11, 2015 6:02 PM
  • Instead ODBC you need to map to the .net datatypes I think so as per http://www-01.ibm.com/support/knowledgecenter/SSEPGG_8.2.0/com.ibm.db2.udb.doc/ad/r0011385.htm it needs to be just datetime.

    Arthur

    MyBlog


    Twitter

    Saturday, December 12, 2015 2:53 PM
    Moderator
  • Thanks for the replies. What I ended up doing it switching to OLEDB and using the DBTimestamp type. It works fine now!
    Monday, December 14, 2015 8:57 PM
  • Hi Mike,

    Thanks for your post and Glad to hear that the issue is resolved. If you would like to, you can mark the relevant post as answer. That way, other community members could benefit from your thread.

    Regards,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Tuesday, December 15, 2015 2:21 AM
    Moderator