locked
Lookup Transformation error output RRS feed

  • Question

  • What is the difference between Redirect rows to no match output and redirect rows to error output?

    I need to output the non-matching rows to a flat file. Which option should I use?


    Anonymous

    Tuesday, August 26, 2014 3:01 PM

Answers

  • Redirect rows to Error output option will cause non matched records to also get redirected to Error output which may include records that got failed due to any other errors too like truncation etc. Before SQL 2008 this was the only output available in addition to the Match output for Lookup Task. But this had the issue of not able to distinguish between records that got redirected due to non match against rows that got redirected due to any other error unless you add some subsequent tasks to check status etc of various columns.

    SQL 2008 introduced one more output called NoMatch output which will only have the records that failed lookup match. Thus it makes it easier to identify non matched records separately. So in your case you can use option Redirect rows to no match output itself which will make sure you get only the non matched records and then you can link to flat file destination to get records onto a file.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Haja Moinudeen Tuesday, August 26, 2014 3:15 PM
    • Marked as answer by DevCoder Tuesday, August 26, 2014 4:33 PM
    Tuesday, August 26, 2014 3:11 PM

All replies

  • Redirect rows to Error output option will cause non matched records to also get redirected to Error output which may include records that got failed due to any other errors too like truncation etc. Before SQL 2008 this was the only output available in addition to the Match output for Lookup Task. But this had the issue of not able to distinguish between records that got redirected due to non match against rows that got redirected due to any other error unless you add some subsequent tasks to check status etc of various columns.

    SQL 2008 introduced one more output called NoMatch output which will only have the records that failed lookup match. Thus it makes it easier to identify non matched records separately. So in your case you can use option Redirect rows to no match output itself which will make sure you get only the non matched records and then you can link to flat file destination to get records onto a file.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Haja Moinudeen Tuesday, August 26, 2014 3:15 PM
    • Marked as answer by DevCoder Tuesday, August 26, 2014 4:33 PM
    Tuesday, August 26, 2014 3:11 PM
  • Another question, Do I need to have a Flat File Config Manager for every flat file destination? Or is there a way to consolidate these?

    Anonymous

    Tuesday, August 26, 2014 4:34 PM
  • Another question, Do I need to have a Flat File Config Manager for every flat file destination? Or is there a way to consolidate these?

    Anonymous

    It depends. If the metadata (ie number of and datatype of columns) is different then you need seperate connection manager. Otherways you can use single connection manager with an expression being set for the connectionstring property to point to multiple files at runtime

    See an example here

    http://www.sqlis.com/sqlis/post/Looping-over-files-with-the-Foreach-Loop.aspx


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, August 26, 2014 5:02 PM
  • How do you use a single connection manager but with one file per package?

    Anonymous

    Tuesday, August 26, 2014 5:10 PM
  • How do you use a single connection manager but with one file per package?

    Anonymous

    one file per package?

    Sorry you cant share connection managers between multiple packages unless you're on SSIS 2012 and uses project deployment model


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, August 26, 2014 5:25 PM
  • iam on 2012

    Anonymous

    Tuesday, August 26, 2014 5:54 PM