Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Basic SSIS Workflow Management – Step 6 of the Stairway to Integration Services Expand / Collapse
Author
Message
Posted Wednesday, July 18, 2012 12:05 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:15 PM
Points: 388, Visits: 1,044
Comments posted to this topic are about the item Basic SSIS Workflow Management – Step 6 of the Stairway to Integration Services

Andy Leonard
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #1331228
Posted Wednesday, July 18, 2012 7:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 18, 2013 12:19 PM
Points: 2, Visits: 68
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.
Post #1331448
Posted Wednesday, July 18, 2012 1:36 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 94, Visits: 677
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!
Post #1331807
Posted Wednesday, July 18, 2012 3:36 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:15 PM
Points: 388, Visits: 1,044
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
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #1331856
Posted Wednesday, July 18, 2012 3:43 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:15 PM
Points: 388, Visits: 1,044
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
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #1331858
Posted Thursday, July 19, 2012 7:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 18, 2013 12:19 PM
Points: 2, Visits: 68
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.

Post #1332157
Posted Thursday, July 19, 2012 8:59 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:15 PM
Points: 388, Visits: 1,044
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
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #1332263
Posted Monday, July 23, 2012 8:10 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, October 16, 2014 7:48 AM
Points: 709, Visits: 397
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.




Post #1333811
Posted Wednesday, December 19, 2012 4:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 8, 2013 4:49 AM
Points: 14, Visits: 73
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
Post #1398305
Posted Wednesday, December 19, 2012 4:29 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:15 PM
Points: 388, Visits: 1,044
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
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #1398705
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse