How to insert or update records in SSIs dataflow

If you import data from a specified source periodically in an integration service project and you need to update existing data in the SQL destination the best workaround for this is as following:

First define a source in the data flow e.g. ole db source, make sure that your columns does not contain null values, this you can do with the derived column shape then add a script component to the data flow-Now your data flow shocould look like the following picture:

In the script component you have to define the Input Source and the output source. right click on the script component and click 'edit', then go to 'uts and outputs' you can rename the input and output name. in the output you shoshould set the exclusion group to 1 and the synchronousinputid to the ID of the input in this case the ID is '000000' per default the first output has the ID. in the following picture you see the configuration.

 

The next step:

Go to the option script and click the button design script, now Visual Studio will be open where you can define your source code, the default language is VB:

Here is a little code snippet for a insert update procedure:

Dim reader as sqlclient. sqldatareader

Sqlconn = new sqlclient. sqlconnection ("Data Source = servername; initial catalog = Databse; Integrated Security = true ")

Sqlconn. open ()

Sqlcmd = new sqlclient. sqlcommand ("select * From tabelle where (ID = @ ID)", sqlconn)

Sqlparam = new sqlclient. sqlparameter ("@ ID", sqldbtype. nvarchar, 50)

Sqlcmd. Parameters. Add (sqlparam)

Sqlcmd. parameters ("@ ID"). value = row. bestellnummer

Reader = sqlcmd. executereader ()

If reader. Read () then

'Do all field comparisons here to determine if

'The record changed since the last ETL.

If (Reader ("amountofhardware"). tostring () <> row. summe) then

Reader. Close ()

Sqlcmd = new sqlclient. sqlcommand ("Update tbestellmassnhme set amountofhardware = @ sum where id = @ ID", sqlconn)

Sqlparam = new sqlclient. sqlparameter ("@ sum", sqldbtype. nvarchar, 50)

Sqlcmd. Parameters. Add (sqlparam)

Sqlcmd. parameters ("@ sum"). value = row. summe

Sqlparam = new sqlclient. sqlparameter ("@ ID", sqldbtype. nvarchar, 50)

Sqlcmd. Parameters. Add (sqlparam)

Sqlcmd. parameters ("@ ID"). value = row. bestellnummer

Sqlcmd. executenonquery ()

Else

End if

Else

'If the reader contains no data the row will be redirect to 'the output source which cocould be the insert statement

Row. directrowtooutputinsert ()

End if

Reader. Close ()

Sqlconn. Close ()

After you have Insert the script you should add an ole db command shape to the output of the script component. In this command shape you coshould define the insert statement as you need.
Ref: http://developers.de/blogs/nadine_storandt/archive/2006/12/07/How-to-Insert-or-Update-records-in-SSIS-DataFlow.aspx