Post reply

How to load data to Destination SQL table using Stored Procedure in SSIS

  • June 27, 2007 at 3:55 am

    #372497

    Hi all,

    I need to develop one SSIS Package which will run every day at 12 AM.

    This package should extract data from Excel Sheet and Load into Sql Server table.

    My Req: I want my package should do if the data already there in the table should update else insert.

    Tell me what are the possible ways to do this.

    Thanks

    PT Senthilkumar

  • June 28, 2007 at 4:14 pm

    #716013

    you could develop a .net assembly that reads the excel file, link it to a table valued function which you would use in you script. i am thinking you can use a cursor to traverse your records

    Everything you can imagine is real.

  • June 29, 2007 at 1:22 am

    #716079

    Hi,

    the way I usually do this is to do a lookup for the PK of the target table saving the value of an ID (autoincrement value). Then I do a conditional split. I the ID is NULL the value does not exist in the target table, yet. The standard output is in this case the existing values which get an own green connector. I insert the new data by an OLEDB target and do an update with an oledb command using parameters. The last part is not very nice, because you have to use ? parameters, but it works and is fast.

    Cursors are usually slow.

    Best regards,

    Stefan

    Kindest Regards,

    SK

  • June 29, 2007 at 10:16 pm

    #716340

    select a sql script tool, create the sql connection and write a sql script to check if  row count> 0 then go for update command by selecting the update db task tool else go for insert command; it will be the script of 5-7 line.

    ---Anil

     

     

  • July 3, 2007 at 7:35 pm

    #717042

    looks like my last post didnt save so am adding again - apologies if it comes up twice!!

    You may need to consider that you would not want to update every row if the data has not changed. This is especially important if you are replicating your data and dont want to replicate a whole stack of deletes & updates everyday when the data has not changed. I have found a checksum very useful for this - but also used a query that returns a row & column level comparison on key fields where there is a difference.

    I have found the folloiwng articles useful:

    http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/

    http://sqljunkies.com/WebLog/ashvinis/archive/2005/06/15/15829.aspx

    You will also find that popping "SSIS Insert & Update" into Google returns a good range of results with more info.

    Hope that helps,

    Catherine

    Kindest Regards,

    Catherine Eibner
    cybner.com.au

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply