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 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
Filed under: ,

Comments

Scott Munro wrote re: How to Insert or Update records in SSIS DataFlow
on 02-02-2007 10:45

Hi Nadine,

Did you consider using a Lookup component? You can use this to split the dataflow with records that have a match in one flow and those that don't in the other. It would also allow you to retrieve fields such as AmountOfHardware from the database.

I would also consider using a Sql Server Destination to handle the inserts.

It is great to see a fellow daeneter with an interest in SSIS!

MUKUND wrote re: How to Insert or Update records in SSIS DataFlow
on 04-11-2008 7:55

Hi

the above example I am not getting what  is this  fallowing function

DirectRowToOutputInsert()

Nadine Storandt wrote re: How to Insert or Update records in SSIS DataFlow
on 04-11-2008 8:34

Hi Mukund,

The function Row.DirectRowToOutputInsert() will direct the data to the defined Output of the Script Component.

Hope it helps otherwise let me know

cheers

nadine

MUKUND wrote re: How to Insert or Update records in SSIS DataFlow
on 04-11-2008 16:46

Hi

But I am not getting that function in the programme.. it show that function is not defined..

can u please help thanks in advance

Nadine Storandt wrote re: How to Insert or Update records in SSIS DataFlow
on 04-11-2008 18:19

Hi Mukund,

so please ensure the following settings:

- Have you defined an Output in the Script component??

- Have you set the synchronousInputID Property of the output?

- The property should be set to the input which you have defined

greets

MUKUND wrote re: How to Insert or Update records in SSIS DataFlow
on 04-14-2008 8:21

Hi Nadine

Thanks Very Much for your help. Its working for me.

Jams wrote re: How to Insert or Update records in SSIS DataFlow
on 01-02-2009 7:59
Pankaj Sharma wrote re: How to Insert or Update records in SSIS DataFlow
on 03-19-2009 18:14

Hi Nadine,

Will there be any changes if the source instead of a db is a flat file?

Thanks,

Pankaj

Nadine Storandt wrote re: How to Insert or Update records in SSIS DataFlow
on 03-20-2009 9:50

Hey Pankaj,

I think the source make no changes to this data flow task.

here are some interesting links for your issue:

decipherinfosys.wordpress.com/.../ssis-importing-data-from-a-text-file-using-a-package

www.experts-exchange.com/.../Q_24192307.html

Pankaj Sharma wrote re: How to Insert or Update records in SSIS DataFlow
on 03-25-2009 23:06

Hi Nadine,

Thanks so much, that cleared up quite a few wrinkles.

Pankaj

Satish wrote re: How to Insert or Update records in SSIS DataFlow
on 02-12-2010 8:27

Hi Nadine,

How to update the data in sql server table in a DFlow?

I am using same table as source and destination,and i want to update the table with  a new column data.

Governor wrote re: How to Insert or Update records in SSIS DataFlow
on 02-24-2010 12:54

Greetings and Salutations,

I am having the trouble stated above which is that the "DirectRowToOutputInsert()" is NOT a member of the component.

Now I have followed your instructions, the exclusion group is 1, the out is synch'd with the input ID, my input columns are identified.

So what else can I be missing?  

PS: the script is a tranform

Many Thanks in advance

arun wrote re: How to Insert or Update records in SSIS DataFlow
on 05-05-2010 17:28

super

s wrote re: How to Insert or Update records in SSIS DataFlow
on 06-07-2010 22:14

I have changed exclusion group to 1

By default Synchronisation id is taken is 'input 162'

But, It's giving error for Row.

Allan Carr wrote re: How to Insert or Update records in SSIS DataFlow
on 06-24-2010 21:15

I am simply amazed that SSIS does not have a component to handle one of the most common tasks in data warehousing

Nishil wrote re: How to Insert or Update records in SSIS DataFlow
on 11-21-2011 10:10

Even I am amazed if there is no Insert/Update component.

I have noticed majority of the developers using Scripts and finishing off the whole logic in it. I am not a big fan of scripts.

Uitvaartverzekering vergelijken wrote re: How to Insert or Update records in SSIS DataFlow
on 12-01-2011 14:54

I am so thankful for the brilliant information you shared in this page.

Hanna wrote re: How to Insert or Update records in SSIS DataFlow
on 01-23-2013 10:39

I have a table and I need to update two empty columns as below:

Column1  Column2

1                 1

1                 2

1                 3

1                 4

1                 5

2                 1

2                 2

2                 3

2                 4

2                 5

3                 1

3                 2

3                 3

[End of table rows]

So mainly.. it adds values to column2 from 1 and maximum of 5 , if it exceeded 5 , then Column 1 is to be incremented by 1 and starts all over again in column 2 from 1 to 5.

I am using a script component as transofrmation with an OLEDB source that reads these two columns (have null values) and then another OLEDB with the same table to be updated with the new transofrmed values.

PLEASE HELP !

Thanks in advance.

In an SSIS package, how do you insert master-detail records? | Yocum Answers wrote In an SSIS package, how do you insert master-detail records? | Yocum Answers
on 12-10-2014 19:16

Pingback from  In an SSIS package, how do you insert master-detail records? | Yocum Answers

Vipin Jha wrote re: How to Insert or Update records in SSIS DataFlow
on 02-06-2015 12:53

Hi ,

Wonderful article.

I am new to ssis , I dont see destination table in above code.

I am thinking in terms of dynamic , i am maintaiong the sql command in filemaster table. i have 100 of sql source query which will be run every time on the basis og input.

developers.de is a .Net Community Blog powered by daenet GmbH.