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?
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
WHEN MATCHED AND (SRC.Name <> DST.DimName) THEN
UPDATE SET DST.DimName = SRC.Name;"
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.