Improving the use of Variables and Parameters on SSIS– A tip from the field

As part of my actual job, I frequently use SQL Server Integration Services for developing new packages as well as to change existing ones.

Inside SSIS I use and abuse of variables and parameters, and often end up having some kind of “beautiful” code like this one inside an Execute SQL Task.

insert into dbo.sometable
  (col1, col2, col3, col4, col5, col6, col7)
values 
  (?,?,?,?,?,?,?)

At first glance, no problem will arise from this. If some parameter order is changed, it is simple to change the order of the columns in the insert. We seldom need to change the parameters order on the mappings.

The real problem is that this is a really simple example. Double-digit number of parameters is normal. Sometimes a cast() or convert() is needed. Havoc.

I frequently use Event Handlers for their flexibility and it’s usual to have a log message with more than 20 parameters where, sooner or later, someone will ask if you could make “just a little change”, like changing the order or inserting another parameter in the middle. We are entering a hole that can just get deeper.

And after a while the errors start to arise.

[…]
SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED
[…]
Conversion failed when converting date and/or time from character string.
[…]
Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.
[…]
“No value given for one or more required parameters.”
[…]
“Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
[…]
“Value does not fall within the expected range”

To mitigate these situations I declare variables and then I can use them with added control, with some bonuses like the possibility of reusing them on the query, a rare but potential event.

declare @MyVariableName0 as varchar(50) = ?
declare @MyVariableName1 as varchar(10) = ?
declare @MyVariableName2 as varchar(20) = ?
declare @MyVariableName3 as varchar(50) = ?
declare @MyVariableName4 as varchar(50) = ?

insert into dbo.sometable (somemessage)
  values
  (
    ' MyVar0 = '+ @MyVariableName0 +
    ' MyVar3 = '+ @MyVariableName3 +
    ' MyVar1 = '+ @MyVariableName1 +
    ' MyVar2 = '+ @MyVariableName2 +
    ' MyVar4 = '+ @MyVariableName4 +
    ' And again MyVar0 = ' + @MyVariableName0
 )

Switch the order of a parameter? Nice and clean.

Add a new parameter in the middle? No problem.