This is the first of a planned series of posts that explore the little bits and pieces of the tools I use that are often overlooked (or never looked at in the first place).
Let’s face it, everyone loves the Execute SQL task. It’s easily the most common task I see in packages (next to the data flow task). 99 out of a hundred times I see this task set up the same way, using a direct input. Let’s think about what probably happens in these cases. You open SQL Management Studio, write and test a query (you do test it right???), copy the query into the execute SQL task direct input text box and bang … Bob’s yer uncle.
I’ve done it myself a gazillion times. Did you know there is another way, I might even argue a better way?
This time, after you’ve created your SQL command (or often a batch of commands), save your work from SQL Management Studio into a new .SQL file on a shared resource that your SSIS Package will still have access to once you’ve deployed it. Now in your SSIS package instead of using direct input hit the dropdown menu and choose ‘File Input’.
You’ll be prompted for a file location. Browse to where you saved your .SQL file. Now choose a connection that this Execute SQL Command will execute on and you’re done.
So … what have we done here? We’ve seperated the SQL batch from our SSIS package. There are a couple things that we might want to think through now.
- We can now save all the SQL behind our execute SQL statements in one place making it easier to review/edit.
- We no longer have to copy our SQL out of SQL management Studio where we have a rich syntax editor into what is basically just a plain text editor that will have no mercy on us if we fat finger an extra character or two into the code after we paste it (ask me how I know …)
- We might be able to reuse the same .SQL file in several SSIS packages.
- The place we save our .SQL file has to be a shared resource that our SSIS package has access to from wherever it is deployed.
Here are a couple things to keep in mind if you want to try this:
- Each command in your batch must be seperated with a GO or a ;
- If you use multiple parameterized statements, all parameters must match in data type and order.
- only one statement can return a result and it must be the first statement in your batch.
- If you return a result from your batch, it must map to the columns you configure in the Execute SQL task.
Did I miss anything? I look forward to your thoughts on how this alternate way to configure the Execute SQL task works for you.