ForEach Loop File Count

Question

Post reply

ForEach Loop File Count

DoubleEx

SSCommitted

Points: 1523
More actions
September 21, 2010 at 8:53 am

#145196

I have a ForEach container that controls the process of going through a directory importing 1 to many text files to a temp table in SQL. After the container I run a stored procedure that processes the data that was loaded into the temp table.

I would like to conditionally run final steps of this SSIS package based on the fact that I imported at least 1 file. Are there any built-in properties of the ForEach Loop object that would hold the count of files that it processed or do I need to work with a variable?

Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

I would use a variable that I update using a Script Task.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Phil Parkin SSC Guru Points: 245813 More actions · Answer 2

I would use a variable that is assigned from the result of an EXECUTE SQL task along the lines of:

select count(*) from ImportTable

and then use precedence constraints (= 0 or > 0). Easier than scripting it.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

Phil Parkin (9/21/2010)

I would use a variable that is assigned from the result of an EXECUTE SQL task along the lines of:

select count(*) from ImportTable

and then use precedence constraints (= 0 or > 0). Easier than scripting it.

Good point. The select count should be more foolproof than just using the completion status of the previous task.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

DoubleEx SSCommitted Points: 1523 More actions · Answer 4

Thanks all - good ideas. I started with scripting and using a global var that is looked at by the Contstraints for >0 or ==0. The Select Count against the temp table would work too but later on in the process I will email the results of the process which I am including the file count so I needed it anyway.

I'm a long time vb.net programmer and T-SQL guy but I'm struggling a bit with figuring out how SSIS wants me to do things. I'm getting better !

Phil Parkin SSC Guru Points: 245813 More actions · Answer 5

DoubleEx (9/21/2010)

Thanks all - good ideas. I started with scripting and using a global var that is looked at by the Contstraints for >0 or ==0. The Select Count against the temp table would work too but later on in the process I will email the results of the process which I am including the file count so I needed it anyway.

I'm a long time vb.net programmer and T-SQL guy but I'm struggling a bit with figuring out how SSIS wants me to do things. I'm getting better !

If you put the file name in the temp table (easily accessible from your FEL via a derived column) you can easily query that for the file count too ...

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.