Use of Dynamic SQL for SSIS Data Sources

Quite a set of adventures in SSIS-land today.  It seems using parameters on SQL Server Integration Services data sources can cause CAST errors.  The only reason I was using parameterized OLE DB SQL Data Sources is to avoid a heavily nested SQL query that was performing poorly.

Here’s the CAST error that I was unable to solve no matter how many CASTs and CONVERTs I applied around the parameter:
[OLE DB Source [219]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80040E21 Description: “Invalid character value for cast specification”.

A good approach is to break down the queries, using a Control Flow Execute SQL Task, to populate a Variable.  Note to populate a variable, one has to set the ResultSet to be a Single row.

Next thing to be aware of is that the Variables window does not seem to show updated values. Ever.  To see updated values, add a Script Task with a MessageBox in a scripttask; that’s the way I roll, pop-ups during development all the way 🙂

MessageBox.Show("My favorite variable " + Dts.Variables["User::TestVar1"].Value.ToString());

Next thing is the Data Source can use a Variable as source for SQL, but the Data Source expects it to be sufficient SQL to compile.

Happy SSISing!