September 21, 2010 at 8:53 am
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
September 21, 2010 at 9:24 am
I would use a variable that I update using a Script Task.
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]
September 21, 2010 at 9:37 am
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 21, 2010 at 9:41 am
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.
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]
September 21, 2010 at 2:50 pm
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 !
September 22, 2010 at 12:54 am
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 ...
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy