SQL Server Performance

SSIS Incremental Load....help!

Discussion in 'SQL Server 2005 Integration Services' started by sql_jr, Aug 13, 2008.

  1. sql_jr New Member

    Hi, forum - need help on this: I am building an SSIS package that gets feeds from other db servers, and I want to capture ONLY NEW ROWS to move over from A to B. I looked at this article on incremental loads which was very helpful : http://www.sqlservercentral.com/articles/SSIS/62063/ . This used a lookup to compare rows in source and destination. Seems to work like a charm for a small table, however, the table(s) I'm dealing with has 13+ MILLION Rows. Therefore, this method takes WAY TOO LONG. Any ideas how to grab the NEW rows in less time? (Will have a follow-up Q, but want to answer this first) Many Thanks!
  2. preethi Member

    1. If you have any identity column in the table you can just compare the Max ID from both databases and findout.
    2. Otherwise you need to develop some sort of auditing mecahnism (trigger could be used.) to get the new rows.
    I had a differnt problem where I couldn't touch the database much for logging. As there was an existing publication, I created a new subscription ( where I deleted the update and delete procedures). After the completion of SSIS package I deleted the data.
    Hope this helps
  3. sql_jr New Member

    Ok, thanks for reply. More specifically, then how can I effectively do
    Select * from SourceDB.dbo.table s where s.maxid not in (select d.maxid from DestDB.dbo.table d)?
    How could I reference the destination? - syntax please. Without using linked servers. THX
  4. preethi Member

    In your case, you don't have to do that way; Run
    SELECT MAX(ID) FROM dbo.table
    from to destination server and you can assign that to a variable in the SSIS package.
    Then you can select all greater than the variable from the source database
  5. sql_jr New Member

    Thx again. That sounds like a good idea.
    But I need to add this into the mix. I have three servers with identical schemas, and the ID column is unique to each server, BUT, may exist on ALL servers. However, the ID + SNID is unique accross all Servers. So, could I still get maxID, where NOT EXIST (ID and SNID). Help me out please!
  6. satya Moderator

    Have you treid with using Linked Servers?
  7. sql_jr New Member

    hi, satya:
    I was trying to avoid using Linked Servers for policy/security reasons, and will try preethi's suggestion. If that ends up being too slow, I may have no choice. THX
  8. sql_jr New Member

    Ok, I still need help with this! How can I pass the results of the query to a variable. can you give me some steps? I'm not having any luck. THX!!!
    NEVER MIND THIS LINK HELPED ME OUT! http://www.sqlis.com/58.aspx
  9. Harnath New Member

    In order to pass the results of the query to a varibale, kindly follow the steps.
    1. Create a variable with OBJECT datatype, so that it will hold the results of the query.
    2. Assign the results of the query to the variable, when you are firing the query like as shown.
    @variable = select * from employee;
    3. Now the results got stored in the variable, so you can use this variable accordingly .
    Hope this will help you.

Share This Page