Running SSIS from an SQL job by passing parameters

ssis sql-job
Ujjwal Vaish picture Ujjwal Vaish · Jul 10, 2014 · Viewed 7.1k times · Source

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 ?

Answer

Anup Agrawal picture Anup Agrawal · Jul 10, 2014

  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

Related questions

How to get the value from step1 to step2 in sql Job

I need to create a SQL JOB. Step1: Insert a Row into TaskToProcess Table and return ProcessID(PK and Identity) Step2: Retrive the ProcessID which is generated in step1 and pass the value to SSIS package and execute the SSIS …

Read More
How to pass variable as a parameter in Execute SQL Task SSIS?

I have ssis package in that I'm taking values from flat file and insert it into table. I have taken one Execute SQL Task in that creating one temptable CREATE TABLE [tempdb].dbo.##temptable ( date datetime, companyname nvarchar(50), price decimal(10,0), …

Read More
SSIS Convert Between Unicode and Non-Unicode Error

I have an ssis package where I am using an OLEDB source linking to SQL Server 2005 table. All columns except a date column are NVARCHAR(255). I am using an Excel destination and using a SQL statement to create the sheet …

Read More