July 23, 2008 at 2:28 pm
I've written a fairly nice package that uses input boxes and message boxes in script components to allow for some interactive manipulation while the package is running. However I'm now trying to take it a step farther.
I have an XML file containing my server names broken down into various categories. I want to display a list of categorys so that the package can be run either just on 1 server or on all of the servers within the category etc.
To do this I was thinking I could write (or have written) a VB form that reads in the XML and prompts the user then passes the information back into the package.
Has anyone else done something like this? Or have any oppinions one way or the other? Or have a better idea for that matter.
Thanks
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
July 24, 2008 at 12:04 pm
This is really outside what is going to be reliable in SSIS.
It is an ETL product, not a workflow management product. Creating a package that waits for user interaction from within a script component is not a good idea. Packages should be able to run end-to-end without user interaction. If you need to collect information, it should be collected before the package runs and either passed in whent he package is executed, or the package should look up the information in the table or file you keep it in.
Also, with your structure, the user needs to actually be executing the package interactively on an SSIS Server. So are you installing SSIS on their workstations or are they logging into your server?
If you have a process that has to wait for a person to make a decision before continuing, it really should either be two packages or you should be using a workflow application like BizTalk.
July 24, 2008 at 12:13 pm
Its used by DBAs only who run it interactivly. It is a set of 2 packages although the interactive part is all in 1 package. It's mostly a set of error checking. IE if there is an error it prompts to see what the user wants to do. There is also a prompt for part of a seed for a random number.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
July 24, 2008 at 12:19 pm
For the other piece, I again would not try to do this prompting within the package. Since your plan is to run the package with multiple data sources, I would create an application that prompts the user for the servers they want to run the package on and then execute the packages passing in the connection information.
This would also mean you could run them all in parallel rather than a loop.
July 24, 2008 at 12:32 pm
That's basically the path I'm taking at the moment. I'm buliding in a "BatchMode" that turns off the interactive part so that we can pass in the variables.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply