January 17, 2012 at 9:11 am
First, I am fundamentally against trying to make SSIS packages interactive, this is not what the technology is for and requires installation of software on individual machines that may incur additional licensing. Short answer, DON'T!
Now onto what I am commenting on..
arun7787 (1/17/2012)
What kind of script we have to write to achieve this and where to write it??
There are several ways to make the determination, EXEC SQL Task, Script Task are two, what it comes down to is something looking at the results and deciding A or B. The you can use precedence constraints with expressions to execute that decision. The methodology to determine A or B is entirely tied to your process but once the decision has been make using that decision is pretty easy in SSIS.
CEWII
January 17, 2012 at 9:57 am
I was thinking to have a dedicated machine for this, with users connecting remotely.
If I cannot find another solution, I will look into getting help from a .net programmer, although I would rather avoid it and keep the things in my yard 🙂
January 17, 2012 at 9:58 am
Thanks, Tim! I will give it a try.
January 17, 2012 at 9:33 pm
The function MSGBox allows you to show a message and capture which button was pressed (i.e. Yes, No, Cancel). This is a VB6/VBScript function.
There would be a parallel in VB.Net (the scripting that SSIS uses).
You can then set a SSIS variable based on the result, then used conditional branching based off that variable to decide which step to run next.
As already mentioned, SSIS is not really the tool for this, but I think the best thing to do is try yourself and note/learn the outcomes. Just give it a lot of thought before deploying it to users.
A more effective solution might be a VBScript that runs various stored procedures based on input.
January 18, 2012 at 9:01 am
You could 2-step it.
Create package 1 to do everything that leads up to requiring user selection. Maybe pkg sends email to users that their action is required?
Create user selection app outside of ssis. Isolated from pkgs.
Create package 2 to pick up after user selection.
many problems with this, like, how does pkg 2 know the user did their part? You'd need pkg 2 to evaluate SOMETHING for the package to decide whether or not to proceed else risk going forward without user decision.
January 18, 2012 at 8:01 pm
If you were to create a user app outside of SSIS, then you may as well remove SSIS from the equation altogether.
But this has become a design discussion and I'm yet to ever find two people who agree on the same design.
January 19, 2012 at 2:27 am
Just reading quickly the post and I would agree with Nick, put a MsgBox in a script and get the response back from the user when you display the value too them. quick and dirty and not something I would put in, but still a quick and dirty.
January 19, 2012 at 8:00 am
Tiz the problem with our work - No One Way to do anything.
Interesting, tho, if you can capture input at run time. Could be a major PITA if inputbox goes like msgbox at run-time: they may have to enter the same damned thing over and over for every record in the data flow.
Can it all be wrapped in an app that has the ability to call the packages? I'm not a Dot Netter, so really curious! But, that goes back to it having to run at server level.
January 24, 2012 at 5:59 pm
nick.mcdermaid (1/17/2012)
The function MSGBox allows you to show a message and capture which button was pressed (i.e. Yes, No, Cancel). This is a VB6/VBScript function.There would be a parallel in VB.Net (the scripting that SSIS uses).
You can then set a SSIS variable based on the result, then used conditional branching based off that variable to decide which step to run next.
As already mentioned, SSIS is not really the tool for this, but I think the best thing to do is try yourself and note/learn the outcomes. Just give it a lot of thought before deploying it to users.
A more effective solution might be a VBScript that runs various stored procedures based on input.
Thanks everybody for the suggestions. For the moment the "interactive task" discovered by Tim does everything I need, including branching based on a SSIS variable - I didn't even think about this prior to looking in their documentation, although in hindsight it looks just natural. I prefer this to writing scripts or .net code, since I can keep everything in a single flow and under *my* control :-). It's also extremely easy to use and there was almost nothing to learn. It still doesn't solve the user permissions issues, but at least it's OK for what I need today.
I still have a question, though. I don't want to start a holy war, but almost everybody seems to discourage having an interactive package. Is there a deep reason for this, that I cannot see?
January 24, 2012 at 7:56 pm
I remain open minded on the tools usage here are some thoughts:
1. Scope creep
You build an interactive package and before you know it, people want it extended to have drop down boxes, radio buttons etc. The tool is not really suited to this (though of course you can do it with much effort)
2. Its a server tool
An SSIS package is generally something that is built in a special studio then deployed to a server to be executed. Interaction implies users, and every user needs BIDS installed to run your package. Again, scope creep means all of a sudden you need to deploy BIDS (and whatever drivers you require) to all those users.
Also be aware that those data movement operations are sucking data over to the client, operating on it, then writing it back to the server. This is fine for a user close to the databases using small datasets, but doesn't scale well when a user is more remote from the databases and starts using bigger datasets.
3. Its an ETL tool
Its optimised for data movement etc. I would guess that the data operations you are performing are pretty simply and could be handled quite easily by basic T-SQL... in which case your ideal solution would be developing an application in .Net or VBA or something. In SSIS you've got a lot of startup overhead when it allocates local memory buffers etc. before it even starts moving data.
But I remain open minded and I think it's important to remember that we are here to solve business problems. If this implementation solves a business problem in a required timeframe and budget, thats good. However it's important to be aware of how this will all work in 12 months - are you going to have a dozen users who all need BIDS installed? Are you going to have to employ a SSIS person just to keep this maintained when you might have been better off employing a .Net person? Are you going to have a million more records to work with? Will it be a maintenance nightmare?
I guess it helps to have that nice interface that shows you where you're up to etc.
January 24, 2012 at 8:58 pm
public2 61705 (1/24/2012)
I still have a question, though. I don't want to start a holy war, but almost everybody seems to discourage having an interactive package. Is there a deep reason for this, that I cannot see?
Lessons learned the hard way. As described above, it's a server 'under the hood' tool for developer usage. The intracies of it allow the user either A) way too much power (write permissions to a database, for example), or B) too many ways to screw up if you don't know what you're doing. Imagine, for a moment, a user turning on the 'transactions' property while holding a connection open and then going to lunch while the dialog box to make a decision was up.
In cases where you need a user to be allowed to perform interactive solutions you usually want to give them a separate interface, where their actions are discreet events against your data so there's little to no concurrency issues and you can make them 10, 20, even 40 different discreet choices to make afterwards.
Otherwise, it's just pain. Lots. and lots. of pain.
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
January 26, 2012 at 3:25 pm
For me, user interaction requires forms with directions and field validations. Which I would build using a tool suited to that purpose. I never trust the users. It isn't personal, it's jsut that the safety of the data is my primary concern. So if I need input from the users, they get their pretty little forms with a zillion safeguards to save them from themselves and protect my data from accidents they may make.
SSIS is supposed to be a package builder. non-interactive packages. It's specifically designed to optimize mass data movements and transforms and run unattended on teh server.
Really, it's about what the tool is designed to do, and do well. VB has long been popular for apps that users interact with because it's designed to do that, with all those grids and input boxes and drop downs, etc. Components that users interact with.
SSIS doesn't even know that users exist.
January 26, 2012 at 11:12 pm
herladygeekedness (1/26/2012)
I never trust the users.
DITTO!
June 8, 2012 at 6:08 am
Elliott Whitlow (1/16/2012)
SSIS is not meant to be interactive. There is no forms builder. It is meant to be run on a server with no interaction from a user. You can in most cases pass parameters to change running behavior ....CEWII
I have similarly need but in my case I just want to build and maintain one package and use a parameter as a variable in the package. Can someone please tell me how to pass parameters to a package?
June 14, 2012 at 3:59 pm
It depends on how you are calling it.
Please explain how you call the package and we'll see what we can do..
CEWII
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply