locked
how to redirect user variable to the error table after derived column RRS feed

  • Question

  • hi folks:

      I have a simple package that loops flatfile data for each year and dump into a table. I've created a user variable @user:: CounterLoop to control the loop.  My goal is to redirect errors whenever an error occurs in the derived column .

    However, when I double click "OLE DB Destination 4 " and go to the Mappings part, I am unable to find this user variable in the available Input Columns. 

    My question is how to direct this user variable to the error output for trouble shooting purpose? 

    Thanks

    


    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    Thursday, September 15, 2016 11:35 PM

Answers

  • Hi cat_ca,

    You want to redirect the variable values in the table,  but in the “derived column” component, you can't "redirect variable values".  You can only redirect the rows which come from the source to the error output.

    So, in my test ,firstly, add the new column in source , it is a null values. secondly in the “derived column” component, the new column is replaced the variable value,  and  you  will see the new column in the error output.

    Like this one:

    If you have any question, please let’ me know.

    Regards, Seif

    • Proposed as answer by Seif Wang Thursday, September 29, 2016 7:09 AM
    • Marked as answer by Eric__Zhang Thursday, September 29, 2016 7:52 AM
    Monday, September 26, 2016 11:42 AM

All replies

  • You need to add a new column in the "Derived Column" and then map it in your destination
    Friday, September 16, 2016 12:03 AM
  • Thanks Ryan. I already did but still can't find the column in the mapping. I want to redirect the highlighted column to the error output table

     

    However, in the mapping, I can not find the CounterLoop column that I've created , therefore, can't redirect. Any idea?


    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    Friday, September 16, 2016 4:35 PM
  • You are looking at "OLE DB Destination 4", aren't you? I note none of those derived columns show which suggests, and does make sense, the component rejects the input columns. You could have a derived column just before this one that adds in your loop column. That way, it become part of the input
    Sunday, September 18, 2016 8:25 PM
  • Hi cat_ca,

    In the Flat file connection manager editor , add  a new column in the advanced, e.g.

    The counterloop  column be assigned to the user variable. e.g.

    Double click the error output destination ,see  mappings option, the new column  be assigned in it.

    Please take a reference.


    Regards, Seif


    • Edited by Seif Wang Monday, September 19, 2016 1:37 AM
    • Proposed as answer by Seif Wang Monday, September 26, 2016 11:54 AM
    • Unproposed as answer by Seif Wang Monday, September 26, 2016 11:54 AM
    Monday, September 19, 2016 1:37 AM
  • hi Seif, I do not understand your approach. You are literally adding a new column to the flatfile schema and this new column does not exist in the flatfile source. I tried your solution and SSIS stopped at  flat file source which totally makes sense.


    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    Tuesday, September 20, 2016 6:53 AM
  • Hi cat_ca,

    You want to redirect the variable values in the table,  but in the “derived column” component, you can't "redirect variable values".  You can only redirect the rows which come from the source to the error output.

    So, in my test ,firstly, add the new column in source , it is a null values. secondly in the “derived column” component, the new column is replaced the variable value,  and  you  will see the new column in the error output.

    Like this one:

    If you have any question, please let’ me know.

    Regards, Seif

    • Proposed as answer by Seif Wang Thursday, September 29, 2016 7:09 AM
    • Marked as answer by Eric__Zhang Thursday, September 29, 2016 7:52 AM
    Monday, September 26, 2016 11:42 AM
  • thanks. I figure it out by adding an extra derived column after the source. 

    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    Thursday, October 6, 2016 6:27 PM