none
using lookup transformation in SSIS 2008 r2

    Question

  • I am importing data from the staging table into the destination table using ssis 2008 r2.

    what i am implementig is oledb souce then Deried column then data conversion and then Lookup transform. Actually i implemented the package first without the lookup transform and i got the error that tells that the PK does not allow duplicates i checked that there are already 20 PK values which were already there at the destination so i thouht to use Lookup transform.

    But again i am gettin error i think iam not configuring the lookup properly. error is

    Error: 0xC0202009 at Data Flow Task, OLE DB Destination [28]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.

    An OLE DB record is available.  Source: "Microsoft OLE DB Provider for SQL Server"  Hresult: 0x80040E21  Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

    Error: 0xC020901C at Data Flow Task, OLE DB Destination [28]: There was an error with input column "FAMILY_ID" (159) on input "OLE DB Destination Input" (41). The column status returned was: "The value violated the integrity constraints for the column.".

    Error: 0xC0209029 at Data Flow Task, OLE DB Destination [28]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "OLE DB Destination Input" (41)" failed because error code 0xC020907D occurred, and the error row disposition on "input "OLE DB Destination Input" (41)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

    Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "OLE DB Destination" (28) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (41). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

    Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is beginning.

    Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "component "OLE DB Destination" (28)" wrote 0 rows.

    Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning.

    Task failed: Data Flow Task

    Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

    SSIS package "Package.dtsx" finished: Failure.

    The program '[952] Package.dtsx: DTS' has exited with code 0 (0x0).

    PLEASE help me in confifuring this

     

     


    Fighttillend_DBA/DEV
    Sunday, June 05, 2011 7:42 PM

Answers

  • Are you using the Lookup to determine if the FamilyID aready exists in the table? If so, then do this:

    On the Lookup, match your Data Flow column of FamilyID to the FamilyID of the lookup table,but there is no need to include any columns from the lookup table.
    On the main page of the Lookup Transform, click the Error Configuration. Select "Send unmatched rows to No-Match Ouput". Now when you connect the Lookup to the next component by dragging and dropping the green arrow, it will stop and ask you which output you want. Select the No-match Output. This No-Match output is the one that has all the rows where there is no matching row already in the table.


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    • Marked as answer by SQL_BOSS Monday, June 06, 2011 7:00 PM
    Sunday, June 05, 2011 8:20 PM
  • The other option is to set the Table Access method on the Destination Adapter to "Table or View" (ie: NOT Fast Load) and then branch off the red output to a Row Count or Flat File or both. That way it allows SQL to insert any row it can, and those that it cannot, get rejected down the red path.

    How many rows are we talking about here? One or tow? Is it a Data Quality issue on the Source System? or is 1/2 your batch containing duplicates? The answer may dictate your course of action!


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    • Marked as answer by SQL_BOSS Monday, June 06, 2011 7:00 PM
    Monday, June 06, 2011 6:04 PM

All replies

  • Are you using the Lookup to determine if the FamilyID aready exists in the table? If so, then do this:

    On the Lookup, match your Data Flow column of FamilyID to the FamilyID of the lookup table,but there is no need to include any columns from the lookup table.
    On the main page of the Lookup Transform, click the Error Configuration. Select "Send unmatched rows to No-Match Ouput". Now when you connect the Lookup to the next component by dragging and dropping the green arrow, it will stop and ask you which output you want. Select the No-match Output. This No-Match output is the one that has all the rows where there is no matching row already in the table.


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    • Marked as answer by SQL_BOSS Monday, June 06, 2011 7:00 PM
    Sunday, June 05, 2011 8:20 PM
  • package is running successfully but i am not getting any row into the destination.

    Actually yes iam performing lookups on FAMILY_ID but I am using derived column which is using SUBSTRING(FAMILY_ID,1,8). after that every column goes to data conversion transform and then LOOKUPS. Copy of Derived column 1 is the one who i want to map to the FAMILY_ID at the destination. 

    So do you think we are performing wrong lookups


    Fighttillend_DBA/DEV
    Sunday, June 05, 2011 8:40 PM
  • Now i implemented again i found some mistake and now strange thing is happening its picking 204 rows every time from the source but at the destination it shows 170 rows for lookup no match output and ultimately there is red at the destination and when i see at the destination everytime some rows goes into the destination table and the rows at te destination for failure decreases everytime so whta do you think is the reason. everytime 9, 10 or 20 rows goes to destination but the package is failing.

    Thanks

     


    Fighttillend_DBA/DEV
    Sunday, June 05, 2011 9:12 PM
  • I believe the source data has duplicate values.

    Maintaining a Lookup and using the no-match would definitely work. If it is not working, then that means the source has duplicate rows.

    If that is the case, then you have to select the DISTINCT rows from staging.

     

    Monday, June 06, 2011 6:01 PM
  • The other option is to set the Table Access method on the Destination Adapter to "Table or View" (ie: NOT Fast Load) and then branch off the red output to a Row Count or Flat File or both. That way it allows SQL to insert any row it can, and those that it cannot, get rejected down the red path.

    How many rows are we talking about here? One or tow? Is it a Data Quality issue on the Source System? or is 1/2 your batch containing duplicates? The answer may dictate your course of action!


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    • Marked as answer by SQL_BOSS Monday, June 06, 2011 7:00 PM
    Monday, June 06, 2011 6:04 PM
  • Thanks!   This helped me.
    Wednesday, November 16, 2011 11:52 PM
  • Hi,

    Using Lookup transformation to lmport data from source to destination with some scenarios, we need to consider some practical approach. Look the below article on to get some basic idea

    http://beyondrelational.com/blogs/community/archive/2011/08/15/using-ssis-lookup-transformation-editor-with-millions-of-rows.aspx


    Thanks Karthikeyan Anbarasan http://f5debug.net/
    Thursday, November 17, 2011 6:40 AM