locked
Increment a value RRS feed

  • Question

  • Hi,

     

    This is my first SSIS query. I have just started with ssis and have the following task

     

    I am looping text files in a folder using foreach. I transfer text file data to sql server. I want to count the total rows are that transferred in all the text files after the foreach loop finishes. I user RowCount but it gives count of only current textfile. It gets reset each time the loop iterates. I want something like @count = @count + currentnoofrows in current text file.

     

    lalit.

    Monday, October 1, 2007 10:18 AM

Answers

  • Have two variables, Count for use in the transform and TotalCount to store the sum of all files. Add a Script Task after

    the Data Flow, but still within the loop, and write a one line bit of code e.g.

     

    Code Block

    Dts.Variables("TotalCount").Value = CType(Dts.Variables("TotalCount").Value, Integer) + CType(Dts.Variables("Count").Value, Integer)

     

     

    I added Count to the Read list and TotalCount to the Read/Write list.
    Monday, October 1, 2007 10:31 AM

All replies

  • Have two variables, Count for use in the transform and TotalCount to store the sum of all files. Add a Script Task after

    the Data Flow, but still within the loop, and write a one line bit of code e.g.

     

    Code Block

    Dts.Variables("TotalCount").Value = CType(Dts.Variables("TotalCount").Value, Integer) + CType(Dts.Variables("Count").Value, Integer)

     

     

    I added Count to the Read list and TotalCount to the Read/Write list.
    Monday, October 1, 2007 10:31 AM
  • thanks Darren for the quick reply.

     

    I did as u said. Here's what i did :

     

    In the Foreach, Inside a DataFlow, I added a RowCount between the source and desination and in its Custom proerties, set the variable name to User::Count. Then in my control flow, i added a Scriptask just below the DataFlow and set the code you have mentioned. I also added a messagebox to display the TotalCount and Count.

     

    However each time i loop, the value is 0 and 0.

     

    I must be missing something. BTW, Count is package variable and TotalCount has loop scope.

     

    Lalit.

     

    Monday, October 1, 2007 10:49 AM
  • Hi darren,

     

    It works now. I was referring the wrong variable.

     

    BTW, when i saw your profile, you are the same person who has created SQLis.com. We all are learning SSIS using that site. I consider it a priveledge talking to you.

     

    thanks again!! Smile

     

    Monday, October 1, 2007 11:02 AM
  •  

    The scoping is if anything the wrong way around, as Count is reset for each loop iteration, TotalCount is what you woudl go on to use in another task I expect, outside of the loop.

     

    How did you check the values? Did you use a breakpoint, perhaps post execute on the Script task, and then use the watch window?

     

    Monday, October 1, 2007 2:05 PM