SSIS auto generate update scripts

Specify the TableName in @TableName variable to create the Update script to be used for SSIS.

DECLARE @counter int;

DECLARE @QueryString nvarchar(2400) = ''

DECLARE @ColumnName nvarchar(120);

DECLARE @TableName nvarchar(120) = 'MyTable'

CREATE TABLE #ColumnList

(

ID int IDENTITY(1,1)

,ColumnName varchar(1200)

)

INSERT #ColumnList

select sys.columns.name from sys.columns

inner join sys.tables on sys.tables.object_id = sys.columns.object_id

where sys.tables.name = @TableName

and sys.columns.name <> 'ID'

SET @counter = 1

SET @QueryString = 'UPDATE dbo.' + @TableName + '

SET '

SET @ColumnName = (SELECT ColumnName FROM #ColumnList WHERE ID=@counter)

SET @QueryString = @QueryString + '

' + @ColumnName+ ' = ? '

SET @counter = @counter + 1;

WHILE @counter <= (SELECT COUNT(*) FROM #ColumnList)

BEGIN

SET @ColumnName = (SELECT ColumnName FROM #ColumnList WHERE ID=@counter)

SET @QueryString = @QueryString + '

,' + @ColumnName+ ' = ? '

SET @counter = @counter + 1;

END

SET @QueryString = @QueryString + '

WHERE ID = ?;'

PRINT @QueryString

DROP TABLE #ColumnList

SSIS How to UPDATE instead of INSERT (UPSERT)

I'm working on several SSIS Transfer Packages right now and I just figured out how to do an "UPSERT": How to insert rows that don't exist in the destination table and how to update rows that exist in both source and destination tables. This will work across servers (which is the environment I'm working on).

This is how one table/view would look like in an SSIS package:

Yes, that means (as far as I know) that you have to create four objects for every table you want to transfer data from/to.

The Source object is, of course, connected to the Source database's table/view. The other objects beneath it (the Lookup, Destination and Update objects) are all connected to the Destination database's table/view.

The Update object is the only tricky element in this package. I specify the update script manually, which looks like:

UPDATE dbo.MyTable

SET Column1 = ?

,Column2 = ?

,Column3 = ?

WHERE ID= ?

(see: Generate SSIS Update script which I use to generate the update script for each table that I have in my SSIS package)

Each "?" will be automatically mapped by the system based on the order you defined it. All you have to do next is to specify the Column Mappings. Make sure that you do it in the right order like the image below:

Note how the ID is specified last -- that's because its corresponding "?" was specified last in the Update statement.