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
- Directly input the query in the SQL Statement.
- Build the Query Within the variable
- 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.
|“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.