Wednesday, 3 June 2009

SQL 2005: Scalar Resultsets and SSIS Variables

Found an interesting SSIS tidbit today which I've amazingly never encountered before.  Not quite sure how I've never managed to hit this problem before - it seems surprising to me but there you go, I guess it happens from time to time.

In an ExecuteSQLTask, I was returning a scalar value to a variable.  The value returned was a bigint.  My SSIS variable was a bigint.   The step failed with an invalid type error.  What the?  Changed the type to an object.  Success this time but not much help to me down the control flow because I can't read it without a ForEach - which I really don't wanna do for a scalar value.  I mean that would just be silly.

According to Technet "With an OLE DB or Excel connection manager, the connection manager explicitly converts values of the following types, DBTYPE_NUMERIC, DBTYPE_GUID, and DBTYPE_BYTES, to strings".  Seems crazy to me!  I changed the SSIS variable to a string and what do you know, success.


No comments: