Home > SSIS (Integration Service) > Using SSIS Package Configuration Values as Parameters to Execute SQL Tasks

Using SSIS Package Configuration Values as Parameters to Execute SQL Tasks

 

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.


  1. Jing
    February 13, 2015 at 1:51 am

    What is wrong with my expression in execute SQL task SSIS? Can somebody help?

    INSERT INTO LOC_Summary_Table (LOC_A,LOC_B,LOC_C,ASSESS)

    VALUES (” + @[User::RowCountA] +”,”+ @[User::RowCountB] +”,”+ @[User::RowCountC] + “,”+ @[User::RowCountASS] +”)

  2. BEN
    June 12, 2015 at 9:50 am

    double quotation marks should be replace with single one.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: