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

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?

Answer 1

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

Answer 2

My source says

CSE_ENT_TMS  TIMESTAMP(6) and

CSE_LST_MNT_TMS    TIMESTAMP(6)

Answer 3

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

Answer 4

Thanks for the replies. What I ended up doing it switching to OLEDB and using the DBTimestamp type. It works fine now!

Answer 5

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