Of late I have been working on creating a lot of SQL Server Integration Services packages and a new trick that I have come up with is to use a Script Task to create a dynamic SQL and/or MDX for me using standard SSIS variables and the power of C# (I am not your regular VB guy).
In this blog post I will show you a very simple package consisting of a Script Task and an Execute SQL Task. Here I have created a few SQL Server Integration Services variables which will be used to build the SQL text; ensure that all the variables you create have been scoped correctly, so that all necessary tasks within the package can see the variable.
In this case, the first three variables are the ones which will drive the show. Populate the variables using the Execute SQL Task to assign appropriate first and last day values to the SQL Server Integration Services variables.
Since the result is just a single row, the task is configured like wise. Remember to assign the result set to variable you just created.
Since the result is just a single row, the task is configured like wise. Remember to assign the result set to variable you just created.
Now place a final Execute SQL Task and select the SQLSourceType as “Variable” and ensure that you provide the correct variable name in the SourceVariable field. Effectively what you have done till now is that you have loaded the query text in the variable which will be used as the source.
Leveraging such design practices (.NET and SSIS) I have found that the packages now are easy to maintain, more readable and more modular and with less of messy SQL and MDX lying here and there.
Regards
Raunak Jhawar
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
