August 11, 2014 at 11:10 am
Being new to SSIS, I'll humbly admit that my perspective MAY change on this in the future, so open to feedback on some of my premises here.
But right now I feel that almost everything I'm setting up in a package, I'd really like it to be sequential. Coming from a coding background that did not include all of this drag-and-drop stuff, I'd really like to be in complete control over what happens when. I do not find the perceived and minor increase in efficiency gained by "parallel execution" to be alluring at least in most of my SSIS tasks which are short and quick anyway. For example, checking the recordcount of a stored procedure, which SP itself will no longer return that same recordcount after I run the other stored procedure, obviously calls for ordered tasks.
At first I thought Sequence Containers were the way to go, but after doing more extensive research, it seems like the "sequence" benefit pretty much stops after their [deceptive] name. They are more for grouping and setting properties and variables at a limited scope rather than having much to do with sequence.
And rather, that one should look to task-level variables with precendence constraints to basically do a step-by-step ordering. I recently reviewed this link as one of many readings I completed:
(see the very last bit of text by William Salzman).
Any thoughts? Generally do you think this is the right path.
August 11, 2014 at 11:25 am
Sorry, but bear with me a second. This may be a newbie needs training question or I'm completely lost as to the issue.
When you drag the green arrows from one component to the next, you're telling it to finish the first one (successfully) before starting the second one. What more do you need for sequentialism? Just chain all your objects into a single path.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 11, 2014 at 11:31 am
Unless you plan on getting tremendously fancy with your SSIS projects, just use the precedence constraints. If you have steps X and Y and you drag the "green arrow" from step X to Step Y, Step X willl run first and IF it completes successfully, then Y will fire. If you do NOT put any constraints, they will both fire at the same time or in whichever order SSIS wants to fire them.
As long as you set up an appropriate sequencing in each data flow and within each control flow (and yes that means within your seuqnce containers as well), you should not have issues with not being able to control the sequence of execution.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 11, 2014 at 11:33 am
Totally agree that it should be that way and/or looks that way?, but that's not actually what I've found, and numerous sources (forum threads and articles on the web) agree with me.
SSIS does not guarantee you that those arrows really mean they are all completing in order and the next one not completing until the previous one has completed.
SSIS runs as much as it can in parallel - second guessing the cosmetic/visual order of the tasks and green arrows. I do not want that.
August 11, 2014 at 11:34 am
OK sounds good - so it's not just about the order of the arrows and boxes, it's about precedence constraints. That makes sense to me.
I'm not sure I won't have more questions when it comes to a tree that I want branches going in 2 different directions, but for now - I think I get it. THanks!
August 11, 2014 at 11:37 am
ipisors (8/11/2014)
SSIS does not guarantee you that those arrows really mean they are all completing in order and the next one not completing until the previous one has completed.SSIS runs as much as it can in parallel - second guessing the cosmetic/visual order of the tasks and green arrows. I do not want that.
Can you link some of these articles you've found that explain this? I'd really like to investigate that research/information.
There are two components, the Control Flow and the Data Flow. The Control Flow is DEFINATELY sequentially controlled via the precedence constraints (aka, green arrows). The Data Flow is... more like a stream. Each ROW definitely finishes its work in the component prior, but that row can move to the next piece while other rows are being handled by prior components. Sorts, Aggregations, and a few other controls hose that up and force everything to 'bunch up' before it can start streaming again, which kill optimization.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 11, 2014 at 11:43 am
Just the fact that I have precedence constraints for "success AND [variable]", yet if the 2nd box has errors, those errors end up getting raised.
Why, if they weren't supposed to ever execute?
August 11, 2014 at 11:45 am
ipisors (8/11/2014)
Just the fact that I have precedence constraints for "success AND [variable]", yet if the 2nd box has errors, those errors end up getting raised.Why, if they weren't supposed to ever execute?
OH! That's the validation phase. There's a two step process to SSIS. First it validates everying in it SHOULD run (metadata lines up, servers are existing, etc) then it starts going through its steps to do actual work.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 11, 2014 at 11:46 am
http://stackoverflow.com/questions/3255454/can-ssis-execute-tasks-in-specific-order
and here's one about FTP task failing due to bad connection, even though the connection would have been correct AFTER the first task had executed, despite precedence constraints.
Lastly, people in my office have mentioned that sometimes SSIS creates its own execution plan for the package, if you don't have precedence constraints. I guess that's what I was mainly thinking of as well.
August 11, 2014 at 11:46 am
Oh - just saw your post about Validation Phase. OK, that is relieving. Thanks!
August 11, 2014 at 11:50 am
ipisors (8/11/2014)
Oh - just saw your post about Validation Phase. OK, that is relieving. Thanks!
By the way - you fix that by setting the "delay validation" property of that particular step. I haven't done it in a while - but it will allow you to more or less set some steps to do JIT validation.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 11, 2014 at 11:51 am
ipisors (8/11/2014)
http://stackoverflow.com/questions/3255454/can-ssis-execute-tasks-in-specific-order
That thread is about someone who misplaced a control between sequence containers. It visually looked like it was in a different container than it actually was. That's rare, but it can happen. If things like that start showing up usually there's a bug somewhere. I rebuild the package shell and start copy/pasting objects over.
and here's one about FTP task failing due to bad connection, even though the connection would have been correct AFTER the first task had executed, despite precedence constraints.
In Properties (usually on the right side of the screen) in the MISC section, there's a property called BypassPrepare. Turn that to TRUE so that it doesn't try to evaluate prior to getting all its variables set for its expressions and the like.
Lastly, people in my office have mentioned that sometimes SSIS creates its own execution plan for the package, if you don't have precedence constraints. I guess that's what I was mainly thinking of as well.
A plan is only for a single SQL statement. While yes, the SSIS package may create a different plan due to its parameter delivery methods compared to the parameterized SQL, if you use a procedure you shouldn't see any differences for what you optimized in SSMS originally.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 11, 2014 at 12:06 pm
Delay Validation and BypassPrepare sound like the same concept, implemented on different objects basically -
August 11, 2014 at 12:18 pm
Ahh - OK, thank you.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply