SSIS - How To Build SQL Query By Using Expressions in SSIS Package

Scenario:

A friend of mine who is working as ETL Developer. He wrote Merge Statement for Upsert. When he was deploying his package to QA,UAT and Production, He noticed that the Source and Destination Database names are different in each of environment.
He does not want to maintain different copies of SSIS Package. What other option he has?

Solution:

One way to get this done is create variables for source and destination databases and pass the values of these variable by using SSIS Configuration according to the environments. 

Let's create three variables for this as shown below.

Fig 1 : Variables Required To Generate SQL Query Dynamically in SSIS Package

We can write expression in SSIS 2005 and all latest versions. The same expression can be build by using Expression Task in SSIS 2012 and SSIS 2014. I have example on using Expression Task if you like to use for this example.

Here is my Merge Statement

MERGE [Test1].[dim].[MyTable_SCD] AS DST
      USING [Test1].[dbo].[MyTable] AS SRC
      ON ( SRC.ID = DST.DimID )
      WHEN NOT MATCHED THEN
        INSERT (DimID,
                DimName)
        VALUES (SRC.ID,
                SRC.Name)
      WHEN MATCHED AND (SRC.Name <> DST.DimName) THEN
        UPDATE SET DST.DimName = SRC.Name;

If you see that I am reading from the same database but that is not going to be true. So I am going to use the variables to change the database name.

Click on VarSQL Variable and then hit F4 to go to properties. Click on Expressions as shown below. I used below script in expressions. You can change the query according to your requirement.

"MERGE "+ @[User::VarDestinationDB]  +" .[dim].[MyTable_SCD] AS DST
      USING "+ @[User::VarSourceDB] +".[dbo].[MyTable] AS SRC
      ON ( SRC.ID = DST.DimID )
      WHEN NOT MATCHED THEN
        INSERT (DimID,
                DimName)
        VALUES (SRC.ID,
                SRC.Name)
      WHEN MATCHED AND (SRC.Name <> DST.DimName) THEN
        UPDATE SET DST.DimName = SRC.Name;"

Fig 1: Build SQL Query by Using Variable Values In SSIS Package

The next step is to run this SQL Statement that is saved in our VarSQL variable. We can use the Execute SQL Task to do that.

Bring the Execute SQL Task to Control Flow Pane and configure as shown below.

Fig 3: Execute Query from Variable in Execute SQL Task in SSIS Package

Now you are good to go and create configuration. You can create any type of configuration such as XML, SQL Server etc. whatever you are using in your company.  Include VarSourceDB and VarDestinationDB in configuration and change the database names according to the environment. Your Merge query is going to be build by using Source and Destination Database name from variables and then run by Execute SQL Task.