June 8, 2009 at 1:27 pm
Is there a way to do this? I'm not seeing any examples that use NOT, much less any that use multiple possibilities in the
I have 1 data flow task inside the ForEach container, so I considered using a Conditional Split inside the data flow based on the @[User::FileName] variable, but you can't make that object the first in a Data Flow task.
What am I missing here? Is there a better way to skin this cat?
In the "file:" field of my ForEach, I'd like to use something like this:
*.txt; !CBS%.txt
THANKS SSC!
June 8, 2009 at 1:36 pm
June 8, 2009 at 1:46 pm
I'm in the field, but I'm not sure how to express 2 separate clauses that both must be met. Further, my VB script skills are worthless. Can someone help a guy out with syntax for that field?
"*.txt" && NOT ("CBS%"???
June 8, 2009 at 2:59 pm
After looking at this a bit closer, it appears that there is a limitation to what you can define the FileSpec property to be as the expresssion is not evaluated for each item in the collection, rather once for the container.
You may have to set your filespec property to be the *.txt and then use precedence constraints with expressions to eliminate files from making it through to the meat of your tasks within your for each container. Does this make sense?
June 8, 2009 at 3:16 pm
I'm with you right up until "precedence constraints with expressions to eliminate files...".
What would the expression be? Run through all the files until a C%.txt file fails the data flow, then handle it after the fact?
Is there some way to use the @[User::FileName] variable while the ForEach is still looping through them all? Would the Data Flow task need to be outside the FE container in that case?
I'm just not able to put it together with precedence constraints John. Thanks for sticking with me.
June 8, 2009 at 3:40 pm
I'm with you. So here's how I see this comming together in terms of tasks/steps.
1. Set up For Each container.
-- use Foreach File Enumerator
-- set Files Property to *.txt
-- set up your variable mappings so you can capture the filename and extension
2. The first task inside your For Each container should be a 'dummy' task so that you can get a precedence constraint into your Control Flow.
-- This could be an Execute SQL Task that you set up with SELECT 1 as the SQL Statement just so it will always execute w/o error.
3. Drag the Green Arrow from the 'dummy' control flow task in #2 to what would have been the first task in your For Each loop.
4. Once connected, double click on the green connector arrow. This opens up the Precedence Constraint Editor.
-- Set the Evaluation operation to 'Expression'
-- Set the Expression to (SUBSTRING(@[User::FileName] , 1, 3) == "CBS" ? "NO" : "YES") == "YES"
-- I didn't fully test the expression, but the key is to make the expression evaluate to a TRUE for the condition that you want to send SSIS to the next task and FALSE for the condition where you want to exclude the file. If the expression I've posted does not work, I can mock up an example so I can test it and get you a tested expression.
Does this make sense?
June 8, 2009 at 4:00 pm
June 8, 2009 at 7:11 pm
Nice solution. What happens if the expression evaluates to FALSE? I'm assuming that control flow doesn't just stop because there is no onward path?
Yeah, I know I should know the answer to this 🙂
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
June 9, 2009 at 7:49 am
Is this what you had in mind? I think it's going to work. Thanks.
To answer Phil's question (and to hear myself think), on Fail, we'll just go right back and loop again without executing the Data Flow... Right? If I want to handle those files excluded (CBS), I can specify them in a separate for loop. (They'll need different handling anyway.)
Thanks again. This is genius.
June 9, 2009 at 3:22 pm
Yes, if the file name does not meet the criteria for continued execution through the control flow, it will just proceed with the next iteration of the for each file loop. Only those files that meet the criteria will make it into the 'guts' of the loop.
This same effect could have been achieved by nesting for each file loops, but I'm not a fan of that. As far as routing the exclusions to a different flow, I think you can accomplish that by adding the reverse expression on another precedence constraint comming from that 'dummy' step.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply