Basic SSIS Workflow Management – Step 6 of the Stairway to Integration Services

Question

Basic SSIS Workflow Management – Step 6 of the Stairway to Integration Services

Andy Leonard

SSChampion

Points: 10144
More actions
July 18, 2012 at 12:05 am

#264313

Comments posted to this topic are about the item Basic SSIS Workflow Management – Step 6 of the Stairway to Integration Services
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

Viewing 12 posts - 1 through 11 (of 11 total)

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

sgeiger 71434 SSC Veteran Points: 238 More actions · Answer 1

I think that using the example of a MsgBox in a script task sets a bad precedent. SSIS was never intended to be an event driven process. I agree the script tool is powerful but not for interrupting data flow with events that require user intervention.

Larry Kruse SSCrazy Points: 2617 More actions · Answer 2

Many thanks for the great, concise introduction to SSIS Andy!

I've inhereted a very complex operation with tons of existing SSIS packages. While I have had a little bit of experience using them in the forms you illustrated in Step 1 of your series on SSIS - backup-maintenance packages, and in saving a few import operations to packages, these were created by wizards and were simply repeatable processes. They didn't require much understanding of SSIS to produce or to use.

Your intro has allowed me to dive into the more complex existing packages we have and understand more about what they are doing and why. I am anxious to get to read the succeeding Steps you might have in store for us!

Many thanks for your efforts to produce this instructional series for us noobs! :w00t:

Andy Leonard SSChampion Points: 10144 More actions · Answer 3

Thanks Larry,

It wasn't long ago that I was learning SSIS. We all traverse the learning curve. I continue to learn and grow, and encourage others to do the same.

Thank you for your kind words. Steve Jones deserves the credit for this series - it was his idea; I'm honored he asked me to write it.

:{>

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

Andy Leonard SSChampion Points: 10144 More actions · Answer 4

sgeiger 71434 (7/18/2012)
I think that using the example of a MsgBox in a script task sets a bad precedent. SSIS was never intended to be an event driven process. I agree the script tool is powerful but not for interrupting data flow with events that require user intervention.

We disagree. And that is ok.

One important note: this is the control flow, not the data flow. The control flow is a workflow engine and each task generates events, some of which trigger additional operations. The control flow was designed to facilitate such interruptions.

Andy

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

sgeiger 71434 SSC Veteran Points: 238 More actions · Answer 5

We disagree. And that is ok.

One important note: this is the control flow, not the data flow. The control flow is a workflow engine and each task generates events, some of which trigger additional operations. The control flow was designed to facilitate such interruptions.

Andy[/quote]

I don't disagree with your statement that events get triggered, we write SSIS packages that handle and perform tasks based on events. The issue that I and others have with doing things like popping msg boxes is that you totally interrupt and stop the control flow until there is some intervention to continue it. I could not even imagine deploying a package with a message box to a production environment. On this we do disagree.

Andy Leonard SSChampion Points: 10144 More actions · Answer 6

sgeiger 71434 (7/19/2012)
I don't disagree with your statement that events get triggered, we write SSIS packages that handle and perform tasks based on events. The issue that I and others have with doing things like popping msg boxes is that you totally interrupt and stop the control flow until there is some intervention to continue it. I could not even imagine deploying a package with a message box to a production environment. On this we do disagree.

I understand your argument, I appreciate the logic behind it, and I am glad you have expressed and defended it.

The scenario you describe cannot easily occur in Production - at least not in SSIS 2008, 2008 R2, or 2012. This was an issue in SSIS 2005 RTM, but I believe (not certain) that it was addressed in a service pack. Calls to messagebox functions in a Production environment - executed from SQL Server Agent or any scheduler running as a service configured according to most versions of SQL Server and SQL Server Agent run-time best practices - should raise an error in the SSIS run-time (Code: 0x00000001 Source: <Script Task Name> Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidOperationException: Showing a modal dialog box or form when the application is not running in UserInteractive mode is not a valid operation. Specify the ServiceNotification or DefaultDesktopOnly style to display a notification from a service application.). Interestingly, you can raise and display a message box from inside BIDS or SSDT and by executing using dtexec from the command line. The aforementioned error is raised when the SSIS package is executed from teh security context of a service account (configured with default roles and permissions for service accounts).

As a best practice, unqualified calls to message boxes should be avoided. I address this in an upcoming article in this series - supplying one implementation of the Debug Pattern. But in a demonstration written for those new to SSIS, I have no issue using message boxes.

Message boxes are a tool, and all tools have their place in software development. Best practices exist, in my opinion, to warn developers of potential hazards and abuse when utilizing certain practices that lend themselves to misunderstanding, misapplication, and misuse. Your warning about message boxes is warranted (and again, appreciated), but it is also incomplete from an SSIS architecture perspective.

Thank you for the good discussion - it is appropriate for this thread. I would be happy to continue this conversation with you via email: andy.leonard@gmail.com.

Andy

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

Mad Hacker Ten Centuries Points: 1394 More actions · Answer 7

Another nice job, Andy. You continue to be my "go to" guy on all things SSIS. I had the opportunity to attend your SSIS training session at the SQL Saturday Preconference in Pensacola in June of 2011 and thoroughly enjoyed the opportunity to meet you and and obtain some solid instructtion on this powerful tool. I know that I was a little intimidated during my migration from DTS to SSIS, but now that I've made it past the learning curve I don't know how I ever survived without this valuable tool.

Also, I noticed that you provided some very professional responses to some constructive advice on this topic.

Finally, I recently read on your blog that you've re-aligned your personal, professional, and spiritual priorities with the emphasis being in the spiritual realm. Congrats, I have no doubt that you will make this world a better place as a result of your spirtual commitments. I wish you much success.

Richard Parsons-65 SSChasing Mays Points: 614 More actions · Answer 8

Hi Andy - a brilliant set of articles which I've been through step-wise and really enjoy. On the precedence scripting, I found that the message boxes didn't stack up; I had to click ok for the second box to appear. This was prior to taking the step where I connected the green (precedence) arrow. Any thoughts?

Cheers, Rchard

Andy Leonard SSChampion Points: 10144 More actions · Answer 9

Hi Richard,

Sometimes SSIS message boxes "hide". They will appear in the task bar but, although modal in every other sense, will not appear on top of the other forms. Another possibility is you are executing the package with the package MaximumConcurrentExecutables property set to 1. The default is -1 and translates to "Run a number of tasks at the same time that match my CPU count + 2." If you have a single-core machine, SSIS will execute 3 tasks concurrently when MaximumConcurrentExecutables is set to -1; if you have 8 CPUs, it will execute 10.

Hope this helps,

Andy

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 10

Hello Andy,

Well I have made it to the 6th article in your series. Am I correct, that you could set six objects AND change the MaxConcurrentExecutables to 6. Then have each of the 6 objects run their processing on a differnet core? So in effect it is like six sql server 'workers' executing each of the objects?

If I am right, this would be just like having 6 stored proc's that are set to run at the same time (in my case when SAP extracts out all of our files for loading into other databases). So I could create one SSIS package to load all the files into our staging database... and get rid of the stored Proc's... and it would do it simo.. up to the limit of cores + 2, correct?? (Of course the note you have about having the number of objects above the number of cores +2 gives you a random order in execution. So I would have to make sure that nothing was a FK into one of the other tables.)

Andy Leonard SSChampion Points: 10144 More actions · Answer 11

Hi dwilliscp,

I am unsure about how the SSIS execution engine decides which cores - or how many cores - to utilize. From a purely SSIS perspective, the answer is "yes."

Thank you for hanging in there for the first six articles!

:{>

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics