CODEWITHSUNDEEP
sql-serversql-server-2008stored-proceduresssis
JasonR
JasonR

Reputation: 409

How do you call and SSIS Package via and SSMS Stored Procedure?

I feel like this should be easy but I have scoured the internet and I can't find a good point of reference on where to start.

I have an SSIS Package that I use to copy a bunch of tables from our AS400 into my SQL Server Data Warehouse. Then I run some stored procedure to generate data for a number of reports. I currently trigger this manually. I want to be able to trigger this SSIS Script via a Stored Procedure. The package I want is stored in Integration Services, not on a local machine. It is the same server that SSMS lives on.

I feel like this should be easy, and fairly common but I can't seem to figure it out. Any input would be greatly appreciated.

Image: My SSIS IDE enter image description here

Upvotes: 0

Views: 156

Answers (1)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131180

Almost all actions in SSMS can generate a script by clicking the Script button that appears in almost all dialog boxes. To generate the execution script, right click on the package you want to execute and click on the Script button on the top left.

This generates a script like this:

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'MyPackageName.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'MyFolder', @project_name=N'MyProject', @use32bitruntime=False, @reference_id=1
Select @execution_id
DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0
EXEC [SSISDB].[catalog].[start_execution] @execution_id

GO

Upvotes: 1

Related Questions