Post reply

Using new GetIdentificationStringByID to get Column Name in SSIS

  • August 7, 2016 at 1:38 pm

    #1894238

    lduvall (8/7/2016)

    Phil, I added the @RowCount and @ErrorRowCount variables and set a derived column to add the two together and mapped it to my error table destination, but it's not working. It's saying 0. I thought maybe it is because my first test file had the error record on line 1, but I flipped it and put it on the final line and it's still not capturing anything. Could it be because it processes the errors first and it hasn't assigned the value to the completed row yet? I'm attaching photos of the data flow and the derived column. I have the default for the variables set to 0 so I don't think it's a matter of trying to add NULL + line number.

    I must apologise. I have misled you (and learned something myself in the process). I just checked BOL regarding the Rowcount transformation, and found the following text:

    The transformation stores the row count value in the variable only after the last row has passed through the transformation. Therefore, the value of the variable is not updated in time to use the updated value in the data flow that contains the Row Count transformation.

    So we need to find a different way of populating the variables. This can be done within script components (one for each path through the data flow), but I don't have time to write the code just now. I will try to post again later today with more detail.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • August 8, 2016 at 5:38 pm

    #1894446

    The following may help if the truncation error is on the load to the final table - http://www.sqlservercentral.com/Forums/Topic1300952-364-1.aspx

    if the error is on the input file itself then changing the input file sizes to be big and then using the above will also work.

    alternatively use a staging table - in that case either the method above or a identity column will give you the row number. This is what I normally do and in this case I then prefer to to the split and validation fully in SQL rather then extract the data

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply