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