CODEWITHSUNDEEP
ssissql-job
Ujjwal Vaish
Ujjwal Vaish

Reputation: 375

Running SSIS from an SQL job by passing parameters

Is it possible to send parameters like @inputfile, @packagepath etc. to my sql job which will run my SSIS?

Currently I am configuring my path, package name in the job properties itself to run it.

I know one way is to define a command

@command = exec my_stored_procedure @parameter1 , @parameters2

and include this in the job.

But can't I do it without calling the stored procedure , directly from the job ?

Upvotes: 3

Views: 8143

Answers (2)

SinisterPenguin
SinisterPenguin

Reputation: 1618

You could have the Master package above read parameter values from a table & load them into SSIS variables with the "execute SQL" task.

You could then use a SPROC to populate the table before you run the Package if that is the way you need it to run?

Bit of a hack really :-)

Upvotes: 0

Anup Agrawal
Anup Agrawal

Reputation: 6669

  1. Create a master package which calls all the 10 SSIS packages you want to execute. If all the packages are in same project use Project Reference. If the packages are not in same project (or as you said different paths) use external reference type to call them.

    enter image description here

  2. If you want to call all the packages dynamically irrespective of name. Create a for each loop enumerator and call the execute package task. In the execute package dynamically pass all the packages from SSISDB.

  3. Call the master package in your SQL Server agent Job and you are done.

    enter image description here

Upvotes: 1

Related Questions