Clever Geek Handbook

SSIS in SQL Server 2005

I created an SSIS package in which I call 2 sp with one parameter each. So how to call this package inside a stored procedure by passing the value as a parameter to this package and stored procedure dynamically.

+2


source to share


2 answers


It would be better to create a Job and then run this from your procedure. Otherwise, you will need to run the SSIS package using xp_cmdshell, which is considered by many to be bad to use (security risks).

EXEC msdb.dbo.sp_start_job N'YourJobNameHere ';

try the SSIS Software Package which covers all of the following methods:



  • Run a Package Programmatically Using the SSIS Object Model
  • Start the DTEXEC.EXE process. DTEXEC is a command line utility for executing SSIS packages.
  • Use SQL Agent. You can configure an agent job to run your package.
  • Use another utility to run DTEXEC for you.
  • Create your own application that will run the package

EDIT based on OP which says they cannot pass value to job

unless you have multiple users trying to run this package in one go, you can insert your values ​​into a special table so that stored procedures (inside your package) can be fetched and used. If you have multiple people trying to run a package at the same time, you can create a queue table where the job pulls the required parameters from that queue table and then runs the package, when it finishes it will pull the next set, etc.

+1


source


You can probably run the package using the DTExec.exe utility in command line mode. Check out Jamie Thomson's blog .

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



Also check this MSDN page

0


source






More articles:


All Articles