How to insert new and update existing records using SSIS

In this article, we will see how to insert new and update existing records using SSIS.

We often need in the incremental load of various tables (from source to destination) that old records must be updated and new records inserted.

Step 1: Finding the columns of the source tables from which we can determine that a particular records is a new record or it is the old one.

For e.g. I am taking a Student (Source) table. It's structure is:

SSIS1.gif

By looking at it's table structure we easily find that we can determine whether a record is old/new by the createdate and modidate.

New record -> Createdate = Modidate
Old Record -> Createdate <> Modidate

Step 2: Create an SSIS Package
  • Open BIDS
  • File – New Project
  • Name - InsertUpdate

    Create SSIS Package
     
  • Click Ok

Step 3: Add Oledb connection for source and destination
  • Right click on connection manager pane and select new OLE DB Connection

    SSIS3.gif
     
  • Enter Server name or IP and then select database name

    SSIS4.gif
     
  • Click on Test Connection
  • Click OK.
     
  • Repeat step (a) to step (d) to create an OLE DB connection for the destination
Step 4: Add a dataflow task into package

SSIS5.gif

Step 5: Rename it to Student_incr

Step 6: Add three variable of the package level scope

Date_From : Int32,
Date_To: Int32
Query_Student: String

SSIS6.gif

Set the value of package variable as shown above.

Here Query_Student variable will contain the SQL incremental query:
  1. SELECT * from Student   
  2. WHERE (CAST(CONVERT(VARCHAR(10), Createdate, 111) AS DATETIME) >= CAST(CONVERT(VARCHAR(10), DATEADD(Day" +(DT_WSTR,50)@[User::Days_From]+", GETDATE()), 111) AS DATETIME)) AND (CAST(CONVERT(VARCHAR(10), Createdate, 111) AS DATETIME) < CAST(CONVERT(VARCHAR(10),DATEADD(Day"+(DT_WSTR,50)@[User::Days_To]+", GETDATE()), 111) AS DATETIME))  
Paste this query in the expression property of the Query_Student variable

SSIS7.gif


Step 7: Double click Student_incr dataflow task
  • Drag and drop OLE DB Source onto the Package
  • Right click on the OLE DB Source and then select edit

    SSIS8.gif
     
  • Set properties as shown following:

    SSIS9.gif
     
  • Click Ok.
     
  • Drag and drop Conditional Split component onto the package

    SSIS10.gif
     
  • Connect it to OLE DB Source and then right click on it and then select edit

    SSIS11.gif
     
  • Make two mutually exclusive condition as shown

    SSIS12.gif
     
  • Click ok.
     
  • Drag and drop OLE DB destination and OLE DB Command components onto the package
  • Connect OLE DB destination and OLE DB Command to Conditinal split.
    Insert -> OLE DB destination
    Update -> OLE DB Command

    SSIS13.gif
     
  • Right click on the OLE DB destination and select Edit and then set properties
    as shown below

    SSIS14.gif
     
  • Map the column on the mapping page and then click ok.
     
  • Create a stored procedure in the destination database named

    "UpdateStudentProc" which has the following definition

    SSIS15.gif

    This procedure will update the existing records and will insert the new ones.
     
  • Right click on OLE DB Command and then select edit
     
  • In the connection manager, select localhost.destination

    SSIS16.gif
     
  • In the Component Properties set as

    SSIS17.gif
     
  • In the Column mappings tab map the output column to parameters of UpdateStudentProc and then click ok.

    SSIS18.gif
You are through.