none
SSIS - Excel to SQL, updating records...

    Soru

  • Wonder if you guys can help. I have an SSIS package which populates a SQL Server Table with data from an Excel Spreadsheet. The Package Sorts the Excel Spreadsheet to remove Duplicates and also does a Lookup Transformation to check for duplicate entries in the SQL table. This all works great, it will insert new records and filter out duplicates to a flat file. What I am wanting to do however is allow updates to Rows/Cells in the Excel spreadsheet which will then populate across to SQL, whilst maintaining the Insert aspect of the package if the record doesn't exist.

    I am in the right ball park with Lookup Transformations and Conditional Splits, I am just falling short with how I can apply this to the SSIS package so that I keep the functionality of both. I don't need to update the Excel Spreadsheet with any changes to the SQL table, it's just any changes to lines in the Excel Spreadsheet that I want to carry over to the SQL table.

    Please can someone guide me in the right direction?

    Cheers.

    15 Mart 2012 Perşembe 14:15

Yanıtlar

  • You can use the SCD component if you like - although it's not as flexible or fast as I'd like it to be, especially for this purpose.

    You need to take the "matched" output of your current lookup and run it into another Lookup (or return more columns from your existing lookup).  Return all of the columns that you want to compare against what's in the Excel sheet.  Then use Derived Columns and/or Conditional Splits to detect differences.

    This is where it gets tricky.  If there's a difference... what do you do?  Do you know that the Excel spreadsheet has "newer" or "better" changes than the database?  If you know without a doubt that the Excel version needs to get pushed to SQL, then route that Conditional Split output to an OLE DB Command component with an UPDATE statement.

    (This will work, but will be fairly slow.  You will want to play with having two lookups - your first one almost definitly cached, your second one maybe not.  You won't want to use the OLE DB Command if you can avoid it either - it works, but you should try to replace it with an insert into a temp table, then issue a batch UPDATE command from an Execute SQL Task.)


    Todd McDermid's Blog Talk to me now on

    • Yanıt Olarak Öneren Eileen Zhao 19 Mart 2012 Pazartesi 08:09
    • Yanıt Olarak İşaretleyen Eileen Zhao 21 Mart 2012 Çarşamba 01:31
    15 Mart 2012 Perşembe 23:31
    Moderatör

Tüm Yanıtlar

  • Hello GhostfaceKill4h,

    Are you trying to do "upserts" against the Excel AKA data synchronization into the database table?

    I would upload the data to a staging table and then diff using this technique: http://www.sqlis.com/sqlis/post/Get-all-from-Table-A-that-isnt-in-Table-B.aspx


    Arthur My Blog

    • Yanıt Olarak Öneren plditallo 15 Mart 2012 Perşembe 17:05
    • Yanıt Önerisini Geri Alan plditallo 15 Mart 2012 Perşembe 17:05
    15 Mart 2012 Perşembe 14:52
    Moderatör
  • If I understand you well, then you want to add new rows in the Table when the business key read in excel row is not in the table, or update a row in the table when it has a corresponding business key in excel? Try using the SCD (slowly changing dimension). It is easy to setup, it works, but beware if you add or modify columns, the SCd has to be redefined.

    Jan D'Hondt - Database and .NET development

    15 Mart 2012 Perşembe 15:35
  • Thank you for the replies.

    Basically at the moment the SSIS package will insert into a staging table any entries from the Excel Spreadsheet which don't already exist in the table. What I am wanting to be able to do is keep the Insert new entries if the records don't already exist, but also allow people to change existing rows in the Excel spreadsheet (which have already been inserted into the staging table) and when the package is re-run, rather than adding the altered Excel rows as a new row in the SQL Server staging table, it will just update any changes to the row.

    15 Mart 2012 Perşembe 16:36
  • Then the Slowly changing Dimension will do the job. It is quick and dirty, I use it often, unless you need to load millions of rows daily, but I guess that is not the case with an excel file.

    Jan D'Hondt - Database and .NET development

    15 Mart 2012 Perşembe 16:43
  • Thank you for the replies.

    Basically at the moment the SSIS package will insert into a staging table any entries from the Excel Spreadsheet which don't already exist in the table. What I am wanting to be able to do is keep the Insert new entries if the records don't already exist, but also allow people to change existing rows in the Excel spreadsheet (which have already been inserted into the staging table) and when the package is re-run, rather than adding the altered Excel rows as a new row in the SQL Server staging table, it will just update any changes to the row.


    How the people will know the rows already exist in the table, and most importantly, what would prevent the users from updating the row does not qualify?

    Arthur My Blog

    15 Mart 2012 Perşembe 17:45
    Moderatör
  • You can use the SCD component if you like - although it's not as flexible or fast as I'd like it to be, especially for this purpose.

    You need to take the "matched" output of your current lookup and run it into another Lookup (or return more columns from your existing lookup).  Return all of the columns that you want to compare against what's in the Excel sheet.  Then use Derived Columns and/or Conditional Splits to detect differences.

    This is where it gets tricky.  If there's a difference... what do you do?  Do you know that the Excel spreadsheet has "newer" or "better" changes than the database?  If you know without a doubt that the Excel version needs to get pushed to SQL, then route that Conditional Split output to an OLE DB Command component with an UPDATE statement.

    (This will work, but will be fairly slow.  You will want to play with having two lookups - your first one almost definitly cached, your second one maybe not.  You won't want to use the OLE DB Command if you can avoid it either - it works, but you should try to replace it with an insert into a temp table, then issue a batch UPDATE command from an Execute SQL Task.)


    Todd McDermid's Blog Talk to me now on

    • Yanıt Olarak Öneren Eileen Zhao 19 Mart 2012 Pazartesi 08:09
    • Yanıt Olarak İşaretleyen Eileen Zhao 21 Mart 2012 Çarşamba 01:31
    15 Mart 2012 Perşembe 23:31
    Moderatör
  • Hi,

    "What I am wanting to do however is allow updates to Rows/Cells in the Excel spreadsheet which will then populate across to SQL, whilst maintaining the Insert aspect of the package if the record doesn't exist."

    Would it be feasible to do all your transforms, lookups, output duplicates, etc and then write out the spreadsheet? Assuming no updates can occur to the spreadsheet during the workflow?

    ?

    16 Mart 2012 Cuma 01:47
  • Hi GhostfaceKill4h,

    I wonder if you’d be interested in a Excel to SQL Server tool I built. It lets users update Excel spreadsheets on the intranet and it automatically parse the spreadsheet and store the data a SQL Server table that you select.

    To avoid duplicates you can setup validation logic in the GUI so users will never be able to enter duplicates. You can also easily pre-load the spreadsheet with the fresh data you have in your  SQL Server table. 

    I'am currently looking for feedback so I give away licenses (2-users) to people who give feedback on the product.

    It's called SQL Spreads: http://www.sqlspreads.com  

    Thanks

    Johannes


    22 Mart 2012 Perşembe 13:28
  • 

    Here i Perform the same i hope, Source is Excel product details with price,

    if new product will added the inserts in table

    if update the price old record will update with Expiredate in destination(Table: Product_dest)

    source like these


    Destination will be like these


    Here Edate Expire date and Skey maintained surrogate key.Here i performed SCD Type 2

    02 Şubat 2013 Cumartesi 16:10