January 25, 2007 at 10:10 am
Maybe I'm confused. I thought that if you run the DTS Package manually, from Enterprise Manager, it should prompt you, but if you run it from dtsrun, and pass variables in the /A switches, it should not prompt you, and it should just take the values passed in those switches to the package, in answer to the prompts that WOULD have come up had you NOT passed values in in the first place.
It is prompting me, even when I put NO switches in there other than the /F c:\mailing.dts one, when I run it just from a cmd prompt.
Even if you are not supposed to be prompted, I don't think your example worked, entirely, in that there was nothing IN the text file it created, but it DID create the text file, and when I ran the xp-cmdshell it did not prompt me (but it also did not prompt me when I ran it from EM)
January 25, 2007 at 11:18 am
I don't consider myself a DTS guru, but I have worked with it a great deal and have created some pretty complicated packages. I have never seen or heard of a DTS package prompt for user input. My example was designed to show how a package can accept a global variable value through the DTSRUN call. It works perfectly here as I posted it. I would recommend looking at the differences in your package and my basic package. I would also recommed running mine as-is, meaning that you run it from your SQL Server and not from a structured storage file. If you are OK with it, send me your table DDL and your package at jrowan@cox.net. I will look at it more tonight.
January 25, 2007 at 12:54 pm
If the prompting doesn't happen at the point that someone runs the DTS Package manually, then what is the point of setting up global variables at all? The user would never be able to change them at run-time anyway, unless they ran it some other way than in EM, like a stored proc that promted them. What I am ultimately trying to do here, is set up a VB app that they can run that does the prompting, and feeds the answers to those prompts into a(n) SP, that in turn runs the DTS package. Let me look into my DTS package a bit more, and see if I can figure out anything else. will let you know what I find.
January 25, 2007 at 1:32 pm
You cannot prompt for user input in stored procedures either. The only way I see to prompt the user for input is to do as you plan and create a front end app that collects user input. You can then use that user input to create the DTSRUN command and execute the stored procedure, or pass the input into a stored procedure that does the same. Global variables exist for this purpose, but you will not be prompted to input them. If you set up global variables in a DTS package and you don't initialize them somehow either by passing them in the DTSRUN command or by creating an ActiveX task that initializes all global variables, the package will run and use NULL for the global variable values. SQL Server is a backend database platform and not a user interface. If you want the ability to run a package manually that normally gets called from a VB app that is smart enough to pass it global variables, you will have to come up with a way to call it and set the variables at the time of calling. If you do create a VB app, you should not have any reason to run it manually, just use the VB app as that is how you've designed the package.
January 25, 2007 at 1:50 pm
This is very good information, thanks. I actually didn't think one could prompt for input in a(n) SP. I will try to figure out how to change the DTS (but I think it might be useful at some point, to know HOW to prompt, since obviously I must have figured out how. Maybe it was never INTENDED that one could do this, but it seems that maybe it's an 'undocumented feature'.
Thanks for all your help.
Kerstin
January 25, 2007 at 3:03 pm
OK, Kerstin. Check in your package for an ActiveX script containting the inputBox function. This will prompt for input from the user. You could change this prompt to only prompt when the global variable value are empty, meaning that they have not been passed in the DTSRun call. This would allow you to run it through your VB app and pass in the values, or to run it manually and be prompted for the values. One thing you will need to do is to add a task at the end of your package to set all of your global variables to an empty string. This has been a long thread and it has been a bit difficult to get you answers without knowing more about your existing package and your desired end result. Hopefully this helps.
January 25, 2007 at 5:15 pm
I was already far down the road of doing this without prompts, so I figured out how to do it, finally. My final approach was to put the variables (as the come out of my stored proc) into a txt file which I use as input to my DTS package. Now, when I put /A in my dtsrun statement, it does not prompt me for more input, spacing doesn't matter, and puts the values in my txt file, that I then use as input for my DTS package. Works like a charm. Thanks again, for your patience and help on this.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply