3

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 ?

Ujjwal Vaish
  • 373
  • 1
  • 7
  • 21
  • What version of sql server you are using. Have you tried anything so far. It is pretty straightforward. – Anup Agrawal Jul 10 '14 at 15:54
  • sql server 2008 R2, umm the thing isI have 10 SSIS packages having different names and paths, I want to accept aparameter in the job and the run it, without hard-coding anything. – Ujjwal Vaish Jul 10 '14 at 16:00
  • You should start by googling the requirement, and clicking around the Execute SSIS job step. You'll find that passing parameters is not difficult. Organising parameters across multiple packages can be difficult depending on your design. Can you clarify the name of the SQL Agent job step you are using – Nick.Mc Dec 03 '19 at 01:19

2 Answers2

1

  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

Anup Agrawal
  • 6,551
  • 1
  • 26
  • 32
  • How can I pass the parameters to this job , lets say i have a number coming another stored procedure to this job , how can I accept it here is I am asking. – Ujjwal Vaish Jul 11 '14 at 06:26
  • 1
    You would need to go to `Configuration` tab to configure your parameters. You can also set the connections in connection manager. – Anup Agrawal Jul 11 '14 at 14:31
  • Check out this article on Configurations http://msdn.microsoft.com/en-us/library/cc895212.aspx – Anup Agrawal Jul 11 '14 at 14:36
0

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 :-)

SinisterPenguin
  • 1,610
  • 15
  • 17