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!
Want to talk?
Drop us a line. We are here to answer your questions 24*7.