locked
Row Count of various destinations within a Foreach Loop Container RRS feed

  • Question

  • I have an SSIS 2005 package with the following components:

    Control Flow task - Foreach Loop Container which contains a  Data Flow Task and a File System Task for archiving the processed files.

    Data Flow Task - A Flat File Source (CSV) followed by a Lookup Transformation to verify a SQL Server Table for PK violation.  If a violation occurs, then the row is written to an Error Text file destination.  Else the data flows to a SQL Server Table destination. 

    The package works fine.  But I want to add the following functionalities:

    a) Get the Row Count of rows written to the Error Text file destination at the end of the Foreach Loop, so that if the Row Count is >0, then I want to add a File System Task to rename the file with a filename like, ErrFileMMDDYY.txt.  If the Row Count is 0, then I wish to delete the file created at the beginning of the package with a File System task.

    b)  Also get a Row Count of the number of rows written to the SQL Server destination at the end of the Foreach Loop.

    On a typical day, we will be loading 8-10 flat files, each of them having 15-20 rows. 

    How to get the Row Count at the end of a Foreach Loop?  Any help in the matter is highly appreciated.

    - psudershan
    Friday, November 13, 2009 12:17 PM

Answers

  • Thanks once again... 
    However, I can't figure out how a Script Task attached after the DFT can give a total row count... 
    Can you elaborate please... 

    - psudershan

    Hi,
    In the ForEach loop you have the DFT and the Script task.
    In the DFt you store the rowcount value for the current execution in the varErrorRowCount variable.
    After the DFt the Script Task takes the value of the varErrorRowCount and adds it to the variable varTotalErrRowCount's existing value.
    So after the Foreach loop you will have the total value in the above variable.
    It is like(pseudo code):

    int x = 0;
    int y = 0;
    ForEach(file in directory)
    {
          x= 10
          y = y+x
    }
    print y;          y will have the value = 10* number of files in the folder.

    I hope this makes sense..
    Hope this helps !!
    Sudeep   |    My Blog
    • Marked as answer by psudershan Wednesday, November 18, 2009 4:50 AM
    Tuesday, November 17, 2009 12:26 PM

All replies

  • Use the Row count transformation of Data flow controls. Capture the row count in a variable of type int.

    Hold the codition split after the row count transformation and check for the value of the variable and split the path in to two and do the respective operations if 0 and if >0.


    Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.

    Friday, November 13, 2009 12:22 PM
  • Use the Row count transformation of Data flow controls. Capture the row count in a variable of type int.

    Hold the codition split after the row count transformation and check for the value of the variable and split the path in to two and do the respective operations if 0 and if >0.


    Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.

     


    This approach will show the count of the last iteration and the records in that csv file.
    To get a cumulative count you need to use 2 variables for success & 2 variables for the failed records.

    For the success records have 2 int variables say: varDFTSuccessCount & varTotalSuccessCount
    Use the varDFTSuccessCount in the DFt before the SQL Server Destination(I would advise to use the OLEDB Destination in stead)
    Set the other variable vlaue by the expression: @[User::varDFTSuccessCount ] + @[User::varTotalSuccessCount]
    now after the For each loop use the variable varTotalSuccessCount for ur futher steps where u see the count and other stuff.

    Do the same for the error records.
    Hope this helps !!
    Sudeep   |    My Blog
    Friday, November 13, 2009 1:08 PM
  • Thanks a lot for the prompt reply...

    I set up the following Row Count variables for the error rows:

    1) varErrRowCount - Int32 - Value 0

    2) varTotalErrRowCount - Int32 - Value 0 - Expression @[User::varErrRowCount]+@[User::varTotalErrRowCount]

    Now, after the Foreach Loop Container, I added two File System Tasks which use the @varTotalErrRowCount expression as follows:

    1) Rename File System Task - Precedence Constraint expression @varTotalErrRowCount>0

    2) Delete File System Task - Precedence Constraint expression @varTotalErrRowCount=0

    But the @varTotalErrRowCount value is holding the value only for the last iteration and it is not totalling up over the iterations.  (I set up a breakpoint and a watch window for this variable, which shows the value only for the last iteration).

    Where am I going wrong??  Help is greatly appreciated....

    - psudershan
    Tuesday, November 17, 2009 8:29 AM
  • Im sorry I did not test the feasability of the solution I provided.
    It seems in Expression we cannot set the value as var= var + var1.
    So as a work around after the DFT add a script task where u put the below code to sum up the values and after the for each loop use the variable "varTotalErrRowCount":

    Dts.Variables(

    "varTotalErrRowCount").Value = CInt(Dts.Variables("varTotalErrRowCount").Value) + CInt(Dts.Variables("varErrRowCount").Value)

    Set the variable with read write access in the script task varTotalErrRowCount
    Set the variable with read access in the script task varErrRowCount

    Hope this helps.


    Hope this helps !!
    Sudeep   |    My Blog
    Tuesday, November 17, 2009 9:19 AM
  • Just another tip... If you are using SQL 2008, take a look on Change Data Capture.
    regards,
    Pedro
    Visit http://www.pedrocgd.blogspot.com & http://www.BIResort.net - If your question is answered, please mark as answered.
    Tuesday, November 17, 2009 11:55 AM
  • Just another tip... If you are using SQL 2008, take a look on Change Data Capture.
    regards,
    Pedro
    Visit http://www.pedrocgd.blogspot.com & http://www.BIResort.net - If your question is answered, please mark as answered.

    No.. I am working on SSIS 2005...

    - psudershan
    Tuesday, November 17, 2009 12:11 PM
  • Im sorry I did not test the feasability of the solution I provided.
    It seems in Expression we cannot set the value as var= var + var1.
    So as a work around after the DFT add a script task where u put the below code to sum up the values and after the for each loop use the variable "varTotalErrRowCount":

    Dts.Variables(

    "varTotalErrRowCount").Value = CInt(Dts.Variables("varTotalErrRowCount").Value) + CInt(Dts.Variables("varErrRowCount").Value)

    Set the variable with read write access in the script task varTotalErrRowCount
    Set the variable with read access in the script task varErrRowCount

    Hope this helps.


    Hope this helps !!
    Sudeep   |    My Blog

    Thanks once again... 
    However, I can't figure out how a Script Task attached after the DFT can give a total row count... 
    Can you elaborate please... 

    - psudershan
    • Proposed as answer by Sudeep Raj Tuesday, November 17, 2009 12:26 PM
    Tuesday, November 17, 2009 12:13 PM

  • Inside the DFT you are storing the rowcount... and after the DFT in a script task you are doing the total count.

    Visit http://www.pedrocgd.blogspot.com & http://www.BIResort.net - If your question is answered, please mark as answered.
    Tuesday, November 17, 2009 12:18 PM
  • Thanks once again... 
    However, I can't figure out how a Script Task attached after the DFT can give a total row count... 
    Can you elaborate please... 

    - psudershan

    Hi,
    In the ForEach loop you have the DFT and the Script task.
    In the DFt you store the rowcount value for the current execution in the varErrorRowCount variable.
    After the DFt the Script Task takes the value of the varErrorRowCount and adds it to the variable varTotalErrRowCount's existing value.
    So after the Foreach loop you will have the total value in the above variable.
    It is like(pseudo code):

    int x = 0;
    int y = 0;
    ForEach(file in directory)
    {
          x= 10
          y = y+x
    }
    print y;          y will have the value = 10* number of files in the folder.

    I hope this makes sense..
    Hope this helps !!
    Sudeep   |    My Blog
    • Marked as answer by psudershan Wednesday, November 18, 2009 4:50 AM
    Tuesday, November 17, 2009 12:26 PM
  • Thanks once again... 
    However, I can't figure out how a Script Task attached after the DFT can give a total row count... 
    Can you elaborate please... 

    - psudershan

    Hi,
    In the ForEach loop you have the DFT and the Script task.
    In the DFt you store the rowcount value for the current execution in the varErrorRowCount variable.
    After the DFt the Script Task takes the value of the varErrorRowCount and adds it to the variable varTotalErrRowCount's existing value.
    So after the Foreach loop you will have the total value in the above variable.
    It is like(pseudo code):

    int x = 0;
    int y = 0;
    ForEach(file in directory)
    {
          x= 10
          y = y+x
    }
    print y;          y will have the value = 10* number of files in the folder.

    I hope this makes sense..
    Hope this helps !!
    Sudeep   |    My Blog

    Pardon me if my question sounds elementary (which I am as far as SSIS 2005 is concerned). 
    I will try the code as suggested by you..
    And thanks a lot for the tremendous service guys like you are rendering to the community...  May your tribe increase....

    - psudershan
    Wednesday, November 18, 2009 4:56 AM
  • Your welcome to the tribe!!
    Come provide ur help to others......... SHARE UR KNOWLEDGE and learn in the process.
    Hope this helps !!
    Sudeep   |    My Blog
    Wednesday, November 18, 2009 8:50 AM
  • Look at this:

    https://stackoverflow.com/questions/55059973/row-count-of-various-files-with-in-a-for-each-loop-container

    Using an Expression Task

    The Expression Task creates and evaluates expressions that set variable values at runtime, using the Expression Builder

    Add 2 variables to your ssis package:

    • @[User::TotalRowCount]: To store the total count
    • @[User::RowCount]: To be used in the Row Count transformation

    Add an Expression Task inside the Foreach Loop container with the following expression:

    @[User::TotalRowCount] = @[User::TotalRowCount] + @[User::RowCount]


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Monday, October 14, 2019 3:26 AM