Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Loding Data from excel to table in ssis and write already existing records to a diffrent table

Answered Loding Data from excel to table in ssis and write already existing records to a diffrent table

  • Saturday, February 16, 2013 9:57 PM
     
     
    Sign In to Vote
    0

    Hi,

    I'm new to SSIS. We are using SSIS 2005.  I need to load the data from a excel to sql server table.  If the table already have those data we have to redirect it to reject table having same structure for tracking purpose.  I'm using lookup transformation. But existing records are not rejected properly.   I'm having primary key on my table. 

    ex:

    Main Table : tblemp

    empid  firstname  lastname
    1 Scott  tiger
    2 Smith Nike
    3 David Norman

    Table :tblrejct

    Could you please help me on this.

    Regards,

    VG.

     

All Replies

  • Saturday, February 16, 2013 11:10 PM
     
     Proposed
    Sign In to Vote
    0

    Hi VJ - Could you please elaborate as to how the existing records are not being rejected?  Is it some of the records, or all records that have a match in the table?  I'm wondering if it could be related to differing collations.  

    As an alternative, have you considered using a slowly changing dimension?  Write new records to the destination table, and have existing records written to a rejection table.  You may want to look at using Todd McDermid's SCD component (http://dimensionmergescd.codeplex.com/)  as an alternative to the OTB slowly changing dimension component.


    • Proposed As Answer by DeviantLogic Sunday, February 17, 2013 5:51 PM
    •  
     
  • Saturday, February 16, 2013 11:48 PM
     
     
    Sign In to Vote
    0

    Hi,

    Thanks for the immediate reply.

    We load the data in to the table everyday. Some times we need to reload the data for the previous days when ever upstream system got updated with new records for the previous business date.  Then the upstream system will resend the whole data for the older business day. Then we need to reload the data into our system. So, we have to check if the data already existed then we will write it error_log_table(rejected table) only new records need to load into the current table.  Can I achieve it by using Lookup.

    I will try to use the SCD.

    Once again thanks a lot.

    Regards,

    VG.

     
  • Sunday, February 17, 2013 12:00 AM
     
     
    Sign In to Vote
    0

    Happy to help.  I think the SCD would be a perfect fit for this scenario.  You should be able to achieve by using lookups, however they would be less efficient than using the aforementioned component.  

    If using a lookup, you would want to discard matching results and process non-matching results.  By default the lookup component will route non-matching records as errors, so you have to make sure you route the non-matched records to a separate output.  If the non-matched output isn't what you are expecting, I'd say you may have a collation issue, or the keys being matched need to be reviewed.

    Sorry if that was a little confusing, but bottom line, I'd recommend looking at using the SCD component.

     
  • Sunday, February 17, 2013 7:58 PM
     
     
    Sign In to Vote
    0
    This link may be helpful for you. http://sqlserverrider.wordpress.com/2011/06/28/lookup-transformation/

    Thanks Ayyappan Thangaraj, http://SQLServerRider.wordpress.com

     
  • Sunday, February 17, 2013 10:32 PM
     
     
    Sign In to Vote
    0

    Hi,

    I'm using SSIS 2005. It doesn't have an option redirect rows to no match output. I'm trying to implement the above by using SCD. I'm having hard time is there any good example available.

    Regards,

    VG.

    VG


    • Edited by gvramse Monday, February 18, 2013 12:36 AM
    •  
     
  • Monday, February 18, 2013 1:28 AM
     
     
    Sign In to Vote
    0
    Try this link http://sqlserverrider.wordpress.com/2011/05/11/slowly-changing-dimension-scd-transformation/

    Thanks Ayyappan Thangaraj, http://SQLServerRider.wordpress.com

     
  • Monday, February 18, 2013 3:28 AM
     
     
    Sign In to Vote
    0

    Hi,

    Can you please see this link?

    Look up 2005

    Thanks, hsbal

     
  • Monday, February 18, 2013 8:48 AM
     
     Answered
    Sign In to Vote
    0

    Regarding the lookup no-match output, please see Harry's post above.  

    If you are working with the Dimension Merge SCD recommended earlier, there are several examples out there.  The below video on YouTube is referenced directly on the sites main page for an overview of using the component to populate a table.  

    http://www.youtube.com/watch?v=KppAPY-dRHc

    If you are using the built-in SCD component, you may want to start at http://blogs.msdn.com/b/karang/archive/2010/09/29/slowly-changing-dimension-using-ssis.aspx  and http://www.bimonkey.com/2009/07/the-slowly-changing-dimension-transformation-part-1/ . I honestly haven't used the MS shipped component in a while due to my preference for the Dimension Merge SCD component (formerly known as the Kimball Method SCD component).