- Posted by António Lourenço
- On September 21, 2018
- 0 Comments
- sql server, SSIS, T-SQL
Se preferirem ver este post em português, podem visitar o blog do autor onde foi colocado originalmente.
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 a 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 parameter’s 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 use Event Handlers a lot for their flexibility and it’s usual to have a log message with more than 20 parameters that, sooner or later, someone is going to ask if you could make “just a little change”, like the order or insert 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.
I hope that this will help you just as it helps me when I have a 23 parameter query…