Archive

Archive for the ‘SSIS (Integration Service)’ Category

Using SSIS Package Configuration Values as Parameters to Execute SQL Tasks

May 2, 2011 2 comments

 

Working on the VLWHDB the team came across a very interesting case where customer request was not to limit the [Execute SQL Task] Query to non-static Data source and Database (Dynamic Data source and Dynamic Database) based on SSIS Package Configuration file.

Let’s go over the steps how we can do this. Create a SSIS Project with a Package


Create variables and package configuration file, these variable will be used to pass the value from the Package configuration file to the SQL statement with in [Execute SQL Task].


Drop [Execute SQL Task] on the Package Canvas, Set the Source and Destination Connection. We have populated these connections with some test servers strings just for value purpose.


Configure the Connection with the connection variables as shown in the below image


Now we need to configure the [Execute SQL Task]. To configure the SQL Statement within the [Execute SQL Task] there are 3 different ways

  1. Directly input the query in the SQL Statement.
  2. Build the Query Within the variable
  3. File Connection

Apart from these options there is one more way to build Dynamic Query .Edit the [Execute SQL Task] property navigate to the Expressions tab



Browse the expressionsàProperty Page will Popup.

Select the “SqlStatementSource” Property


Write the Query in the expression Table within Double Quotes (“”) with concatenation (+) to provide the Package Variable Value and Evaluate the Query.
Sample

“INSERT INTO “+ @[User::cfgDestinationDB] + “.dbo.TableName”

 


Once the Query is evaluated without Error the same will be applied to the expression


Save the [Execute SQL Task] Property by clicking “OK” and close the Editior.Save the Package and open the [Execute SQL Task] editor Property.


The Evaluated Expression Query is applied with Package Variable Value to SQL Statement.