Increment a value

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.

Answer 1

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.

Answer 2

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.

 

Answer 3

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!!

 

Answer 4

 

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?