SSIS in sql server 2005

Question

I've created a SSIS package, in which I'm calling 2 sp's with a single parameter each. So how to call this package inside a stored procedure by passing a value to as parameter to that package and stored procedure dynamically.

Answer 1

It would be better to create a Job, and then start that from within your procedure. Otherwise you'll need to start your SSIS package using xp_cmdshell, which many consider bad to use (security risks).

EXEC msdb.dbo.sp_start_job N'YourJobNameHere';

try looking at Running SSIS package programmatically which covers all of the following methods:

• Run package programmatically using SSIS Object Model
• Start DTEXEC.EXE process. DTEXEC is command line utility for executing SSIS packages
• Use SQL Agent. You can configure an Agent job to run your package
• Use some other utility to start DTEXEC for you
• Create a custom application that will run the package

EDIT based on OP saying they can't pass a value into a job

if you won't have multiple people trying to run this package at one time, you can insert your values into a special table that the stored procedures (within your package) can select out and use. If you have multiple people trying to run the package at the same time, you can create a queue table, where the job pulls the needed parameters from this queue table and then runs the package, when it is finished it pulls the next set, etc.

Answer 2

I cant do that in a stored procedure because i cant pass a variable to a job , so i need to run or execute the package itself where in i can pass a parameter to the package programatically, so pls help me regarding this...

Answer 3

May be you can run the package using the DTExec.exe utility in command line mode. Check Jamie Thomson's blog.

Ex:- /SET \package\DataFlowTask.Variables[User::MyVariable].Value;newValue

Also check this MSDN page