parameter passing to a step in a job

anushahanna
anushahanna used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jason YousefSr. BI  Developer

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

Author

Commented:
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?
Database Analyst
Commented:
There are differencies between executing a DTSX or a SP in a job - I assume SQL Job.
SP you just add a SQL script type step end exec the SP passing the parameter like

execute my_procedure(@name='abc', @id=1) or
execute my_procedure('abc', 1)  

While a DTSX must be designed to take input parameters then in the Job step SSIS Package type you can pass under Configuration tab a file or Set values tab parameters.
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Jason YousefSr. BI  Developer
Commented:
Easy, just create a new SQL AGENT JOB, create a new STEP, use T-SQL type, and enter the the T-sql to run the SP

Exec SPName AnyParamter
SP-param.jpg

Author

Commented:
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.
lcohanDatabase Analyst
Commented:
If that's the case for that you may need to write dynamic SQL instead of hardcoding the values but what do oy mean by the next user running it wants to pass different parameters? You mean like a UI or something where you put a value or values or a file that they will update? I can't see any Job that needs to take diferent parameters every time is executed and if that's the case as I said you could wrap that code in dynamic sql and get the parameter values from a table(view) for instance.
Jason YousefSr. BI  Developer

Commented:

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.

Author

Commented:
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?
lcohanDatabase Analyst

Commented:
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.

Author

Commented:
Thanks for your helpful input.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial