SSIS - Add Update Columns dynamically [Execute SQL Task in SSIS]

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

4 comments:

  1. Hi, with this solution only the row with the Active='Y' will be executed. If I have 3 columns that should be updated at same table? the dynamic query expects only one query statement, how should i modify it?
    Thank You

    ReplyDelete
  2. Meghalaya Board of School of Education (MBOSE) Going to Conducted Secondary School Leaving Certificate(SSLC) Examination Month March, Every Year about More than Thousands of Students enroll, MBOSE SSLC Conduct the Same Examination in the Month of June for the MBOSE 10th Class Syllabus Benefit of the Students who Main Examination. Nearly thousands Students take the Supplementary Examination. Meghalaya Board 10th Syllabus 2022 is Available our Web portal, Students you can also Download your Exam Topics in Pdf format, MBOSE SSLC Syllabus 2022 All Subjects etc. Students Start Preparation of Exam with new Syllabus 2022. You can get Meghalaya Board 10th Exam Syllabus in the form of Pdf through Online Mode from here. If you want to get it Directly From Official Portal.

    ReplyDelete