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 should 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 'Inputs and Outputs' you can rename the Input and Output Name. In the Output you should set the Exclusion Group to 1 and the SynchronousInputID to the ID of the Input in this case the ID is '243' 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 TBestellmassnahme 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 could 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 could define the Insert Statement as you need.
Posted
Dec 07 2006, 11:53 AM
by
Nadine Storandt