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.
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.
good
ReplyDeletepentaho online job support from india,
ReplyDeletepentaho project support from india,SAP SD online job support from india,SAP SD project job support from india,ReactJS online job support from india,ReactJS project support from india
online job support
ReplyDeleteproject job support
java online job support from india,
ReplyDeletejava project support from india,
mainframe online job support from india,
mainframe project job support from india,
workday online job support from india,
workday project support from india
Thank you so much for providing information about SQL and SSIS and other similar aspects needed to be looked ahead while programming.
ReplyDeleteSSIS Postgresql Write
Hi, I read your whole blog. This is very nice. . We are also providing various Sql Training & Certification Courses, anyone interested can making their career in this field.
ReplyDeleteRenovations themselves assist with adding worth and appeal to your home which can undoubtedly assist with the future, particularly on the off chance that the house will be sold not too far off.howmuch
ReplyDelete"A well-insulated attic can lower heating and cooling costs." Roofing Contractor
ReplyDelete