S_Nathan


I have created a master controller package which runs as follows

deletes all the log files -> deletes few flat files on different drives -> preprocess task(execute package task) -> c# executable (execute process tasks) -> postprocess tasks (execute package tasks)

i need a create a task just before the preprocess task with an user input asking whether he wants to run a particular batch file before proceeding to preprocess. if the user says yes it should run a batch file followed by preprocess tasks, c# and post process or else it should directly goto preprocess, c# and post process (neglecting batch file task)

can anyone help me how to do it.




Re: Regarding running batch files and different packages

Phil Brammer


We've been down this road before... Will the user running the package be a licensed SQL Server user SSIS packages aren't meant to be given to end-users.

With that said, you could probably write a batch file to prompt the user before execution and then take that value and pass it into DTEXEC where it will configure the package appropriately.






Re: Regarding running batch files and different packages

S_Nathan

i meant the user to be my team leader not the end users...the scope of tis task is well within my team...sorry for the confusion...




Re: Regarding running batch files and different packages

Phil Brammer

Still, the answer still applies. SSIS isn't meant to jump out of execution and prompt for values. But you can write a wrapper batch script that does so and passes it into the SSIS package.





Re: Regarding running batch files and different packages

S_Nathan

Do you have any sample of writing a wrapper batch script for this kind of task...



Re: Regarding running batch files and different packages

Phil Brammer

http://www.computing.net/programming/wwwboard/forum/12508.html

Then, take the result and pass it into DTEXEC via the /SET option.





Re: Regarding running batch files and different packages

Phil Brammer

Something like this:

@echo off
echo "Enter Variable: "
set /p myVariable=

DTEXEC.EXE /FILE "package.dtsx" /SET \package.variables[myVariable].Value;%myVariable%


Then in your package, you'd either use expression constraints in the control flow or a conditional split transformation in the dataflow to check the contents of myVariable to decide what to do.





Re: Regarding running batch files and different packages

jwelch

If the user already has the SQL Server client tools installed, you might just use DTEXECUI.exe. That gives an interface for setting the variables. The downside is that you have to know the path to the variable to be set.

The batch file is good, too. Simpler for non-SSIS developers.






Re: Regarding running batch files and different packages

S_Nathan

I tried using VBScript InputBox function and it worked...thanx a lot guys for ur timely help...