How Call SSIS package from Stored procedure and pass a parameter to SSIS package ?

Question

Hello Every One,

I have a SSIS package in which i have a variable ClientID.

I want to call SSIS Package from Stored Procedure and pass ClientID value to it.

Can any one suggest me how to call it  ?

 

Answer 1

Dear Ahmed,

  Create a procedure with one parameter.

-->within the procedure update the ssis_configurations table with CientID value(Parameter).

it will update the package configurations in ssis.then

-->execute the job using exec msdb.dbo.sp_start_job 'jobname'

Regards,

Anil

Answer 2

Thanks Anil.

 

Is there any way that i can pass the parameter directly to the SSIS Package ?

By the way which configuration you want me to use in this case ?

SQL SERVER configuration ?

Answer 3

please check the similar threads

http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/9a014e88-8917-4317-a230-05728986e8ec/

 

---

Let us TRY this |

---

http://quest4gen.blogspot.com/

Answer 4

By the way which configuration you want me to use in this case ?

SQL SERVER configuration ?

NO this is a SSIS package configuration .

http://msdn.microsoft.com/en-us/library/cc895212.aspx

sorry i clicked on the "Proposed As Answer"  by mistake.

 

---

Sincerely SH --MCTS 2005 & 2008 MCITP 2008 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s)

Answer 5

Is there any way that i can pass the parameter directly to the SSIS Package ?

yes check the code section in http://www.mssqltips.com/tip.asp?tip=1395

you can use a SP (stroed procedure) to call a package and you dont need to have a SQL-JOB or SSIS configuration , you have to know the variables in SSIS and the scope of them.

---

Sincerely SH --MCTS 2005 & 2008 MCITP 2008 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s)

Answer 6

This might work:

CREATE PROCEDURE dbo.Exec_SSIS_Pkg @clientID varchar(30), @returnVal Varchar(8000) OUTPUT
WITH EXECUTE AS 'mynetwork\me', ENCRYPTION
AS
SET NOCOUNT ON;

DECLARE @cmd varchar(1000)

SELECT @cmd = 'dtexec /f "C:\SSIS\Pkg.dtsx" /SET \Package.Variables[User::clientID].Properties[Value];"' + @clientID + '" /REPORTING E'

EXEC @returnVal = xp_cmdshell @cmd

RETURN 

GO

 

---

Chaos, Disorder and Panic ... my work is done here!

Answer 7

Thanks Shahriar,

 

But in my case i am going to deploy my package and then call that package with parameter.

In your suggestion it's seems like the package is on hard drive and you are passing hard drive path .

 

 

Answer 8

In your case, change the parameter of dtexec, instead of /f (file), you can use either  /dts or /sql

---

Chaos, Disorder and Panic ... my work is done here!

Answer 9

Thanks Shahriar,

 But in my case i am going to deploy my package and then call that package with parameter.

In your suggestion it's seems like the package is on hard drive and you are passing hard drive path . 

you mentioned that youare goingto deploy it , the question is is it a SSIS thats deployed as a FILE or in  SQL ? i am assuming that its SQL

in my suggestion insted of using the /F and the file name path you can use /SQL and the SQL server name and /PKG

check the parameters in http://msdn.microsoft.com/en-us/library/ms162810.aspx

i am assumin g that you need something like

dtexec /sq pkgOne /ser productionServer ........

please check the parameters for dtEXEC

---

Sincerely SH --MCTS 2005 & 2008 MCITP 2008 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s)

Answer 10

den for that u got to execute that procedure...

take a execute sql task and in the sqlstatement of execute sql task write the exec querry to execute that stored procedure.........

for eg... exec sp1 'priya',?output...

these sp takes takes one input parameter  and returns one op paramtere... here 'priya' is the input parameter that we are supplying...

and now go to the parameter mapping tab... there jus take one user defined variable that u have created,where u want to store the result coming from sp...

keep the direction as output.. as its an output parameter from the sp... and specify the datatype...........

and even u can give the input parameter dynamically.. in dat case...

exec sp1 ?,?output...

in parameter mapping, map both of the variables... keep the direction of the first variable as input and where as of other as output...

---

Priyanka.Sutrave