April 20, 2010 at 3:55 am
Is there a function, method, or technique that someone could share with me?
I have a large dataflow that services records inbound to our data warehouse. There are various components in the SSIS package that deal with records in particular tables. On many occasions though there may be no records in particular parts of the dataflow. Is it possible to prevent certain data flow tasks, and other components from running where there are no records in particular tables?
On the same wavelength, is it possible to stop the dataflow at an early stage if there are no records to process beyond a particular point?
I see that you can set expressions on the connections between components. Is this something I could use?
Any pointers will be well received as ever!
Paul
April 20, 2010 at 6:28 am
Paul_Harvey (4/20/2010)
I see that you can set expressions on the connections between components. Is this something I could use?
Et voila, you've solved your own question 🙂
Use the expressions on the connections to control how the dataflow reacts to certain cases. Therefore you probably need some variables, which you will test in the expression.
Place the different parts of your control flow in different sequence containers, and link them together with the precedence constraints. That way, you can exclude some parts of your control flow if necessary.
A nice blog post on precedence constraints:
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 20, 2010 at 6:38 am
Expanding on that a little ... You could use an Execute SQL task to run something like
Select Count(PK) from table where ...
and map the result directly to a variable, which can be used in a subsequent precedence constraint to control logic flow.
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
April 20, 2010 at 6:50 am
Thanks for the advice.
Can I just ask....
If I set this constraint, does it potentially prevent the entire package from running, or will components AFTER the one I wish to exclude run?
In some situations I would like to be able to "Skip" data flow tasks, but not stop the entire package.
April 20, 2010 at 6:54 am
Paul_Harvey (4/20/2010)
Thanks for the advice.Can I just ask....
If I set this constraint, does it potentially prevent the entire package from running, or will components AFTER the one I wish to exclude run?
In some situations I would like to be able to "Skip" data flow tasks, but not stop the entire package.
Good question. It would stop the entire package. So your constraints need to build a logical path through the package - a bit like a flowchart.
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
April 20, 2010 at 6:56 am
It can potentially prevent the entire package from running, if you use only one precedence constraint.
However, more than one precedence constraint can depart from a single task. If you use multiple precedence constraints, be sure to use the correct logical operators in the set-up (the bottom part of the editor screen of a precedence constraint).
Another option is, if you use Execute SQL Tasks, is to embed the logic in the task itself instead of in the precedence constraint. You can use the IF control flow syntax of T-SQL to accomplish this. For example:
IF (? == True)
BEGIN
here comes your SQL Code
END
The ? is a variable placeholder. Map this to the correct variable.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 20, 2010 at 6:59 am
I will give those a try!
Many thanks.
April 21, 2010 at 2:24 am
Thanks very much for this. I have added the expressions and am now able to skip whole chunks of my package based on certain conditions which have been written out to variables as suggested above.
Thanks to both of you for pointing me in the right direction initially, and for basic guidance, which enabled me to start experimenting with the precedence constraint expressions at an interesting point.
I have already tried to read up on these a little, but found articles to be difficult to understand. After a few small pointers not only did I manage to work most it out, but subsequently re-reading those same articles began to make more sense.
I guess this is what makes forums like this, and forum members like these invaluable to someone just starting out.
Many thanks!
Paul
April 21, 2010 at 2:29 am
Paul_Harvey (4/21/2010)
I guess this is what makes forums like this, and forum members like these invaluable to someone just starting out.
Oh, you're making me blush 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 22, 2010 at 2:57 pm
da-zero (4/21/2010)
Paul_Harvey (4/21/2010)
I guess this is what makes forums like this, and forum members like these invaluable to someone just starting out.Oh, you're making me blush 😀
Many people take a suggested hint or answer away and never post back to say 'thanks, it worked' - you took the the time and, for me, that's what makes it all worthwhile.
Phil
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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply