Scenario :
Sometime we write queries in our Execute SQL to update some column values. Let's say we start with updating one or two columns but later we needed to add third column in Update query. To handle this situation we have to open our SSIS Package every time and update update query. We want to achieve this without opening our SSIS Package.Solution:
Step 1:
Create definition table for your Update Queries, this table will have source table name and column names those needs to be updated.
Have SourceTableName, DestnationTable name,Column Names and Active(Y,N) in our definition table so we can write our dynamic query by using these column values.
CREATE TABLE dbo.UpdateDef ( SourceTableName VARCHAR(100) ,DestinationTableName VARCHAR(100) ,ColumnName VARCHAR(100) ,JoiningKey VARCHAR(100) ,Active CHAR(1) ) GO INSERT INTO dbo.UpdateDef VALUES ( 'dbo.Src' ,'dbo.Dest' ,'NAME' ,' dbo.Dest INNER JOIN dbo.Src ON D.ID=S.ID AND D.NAME=S.NAME' ,'Y' ) GO INSERT INTO dbo.UpdateDef VALUES ( 'dbo.Src' ,'dbo.Dest' ,'ADDRESS' ,' dbo.Dest INNER JOIN dbo.Src ON D.ID=S.ID AND D.NAME=S.NAME' ,'N' ) GO INSERT INTO dbo.UpdateDef VALUES ( 'dbo.Src' ,'dbo.Dest' ,'AGE' ,' dbo.Dest INNER JOIN dbo.Src ON D.ID=S.ID AND D.NAME=S.NAME' ,'N' ) GO
Step 2:
Run this query in Execute SQL Task as shown below
DECLARE @VARSQL varchar(max) SET @VARSQL = (SELECT DISTINCT REPLACE(COL, '=SET', ',SET') AS QUERY FROM (SELECT 'Update D ' + (SELECT STUFF((SELECT N'=SET D. ' + ColumnName + N'=S. ' + ColumnName FROM dbo.UpdateDef I WHERE I.Active = 'Y' FOR xml PATH (''), TYPE) .value('text()[1]', 'nvarchar(max)'), 1, 1, N'')) + ' FROM ' + JoiningKey AS COL FROM dbo.UpdateDef O WHERE DestinationTableName = 'Dbo.Dest' AND Active = 'Y') D) PRINT @VARSQL EXEC (@VARSQL)
How to add Update Column Dynamically in SQL Server in SSIS Package without Opening SSIS Package |