Link to home
Start Free TrialLog in
Avatar of anushahanna
anushahannaFlag for United States of America

asked on

parameter passing to a step in a job

when one of the step in a job is a SSIS package, and you need to pass a different parameter as input to the package, what needs to be done for that purpose?

thanks
Avatar of Jason Yousef
Jason Yousef
Flag of United States of America image

You mean you want to pass variables between parent and child packages?

Avatar of anushahanna

ASKER

actually, if it in a job and job run manually it should ask the value for parameter for package before finishing.

it does not have to be a package; what if a SP in a job step need a parameter value? how can it be and what options to pass it live?
ASKER CERTIFIED SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
OK- but what if the next user running the job wants to pass a different value to the proc, not abc, but xyz, and the next user may want to pass his own etc.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

Hi,

can you give your live example scenario, I thought you needed to schedule a job to run a SP.
you can query the SP directly from SSMS or SSRS report. and change the parameter as you want.

you mentioned PACKAGE in your question, so explain what's your scenario.
for example, the user#1 may want to run the job (and any Sp/SSiS package inside the job) only for 2008 data.

user#2 may want it only for 2006 data etc

so that is why i asked about passing a value in live situation.

can you give a small example how dynamic code could help in this?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I can't see how you would give "users" rights to run SQL Jobs or SSIS packages ( and implicit SP's) with parameters directly against your SQL Server. In this case indeed a SSRS (sql report) with parameters would be the best solution as "users" will run the report from browser, input their desired value then execute the report - implicitely the SP with the parameter value they just keyed in.
Thanks for your helpful input.