How To Execute an Integration Services Package from a SQL Server Stored Procedure

By:   |   Comments (17)   |   Related: More > Integration Services Execute Package Options


Problem

My application has a number of requirements for manipulating data and I would like to create SSIS packages to perform these tasks. However, I am unsure of the best way to execute an SSIS package from my application. Can you provide an example of how to do this from a stored procedure?

Solution

Based on your requirements, your application should call a stored procedure that executes an SSIS package. There are two ways to execute an SSIS package from a stored procedure:

  • Use the extended stored procedure xp_cmdshell to execute the DTEXEC command line utility.
  • In SSIS 2012 use the built-in stored procedures; e.g. ssisdb.catalog.create_execution, ssisdb.catalog.start_execution, etc.

In this tip I walk through an example of executing an SSIS package using the built-in stored procedures that come with SQL Server 2012. I will review the following steps:

  • Create a sample SSIS package.
  • Deploy the SSIS project using the default project deployment model in SSIS 2012.
  • Create a stored procedure to execute the SSIS package.
  • Execute the stored procedure in SQL Server Management Studio (SSMS).
  • Create a .NET console application to execute the stored procedure.
  • Run the .NET console application and check the results.

Create a Sample SSIS Package

I will use the following SSIS package:

Create a Sample SSIS Package

The SSIS package performs the following steps:

  • Creates a table named Test if it does not exist.
  • Inserts a row into the Test table.

The package is deliberately simple since the focus of this tip is to execute an SSIS package from a stored procedure.

Deploy the SSIS Project

By default SSIS 2012 uses the new project deployment mode where an entire SSIS project is deployed to the SSISDB database. To deploy the project right click the project in the Solution Explorer, select Deploy from the menu, and fill in the dialog as shown below:

Deploy the SSIS Project

The Path is a combination of the following:

  • /SSISDB is the database name; it is fixed and can not be overwritten at this time.
  • mssqltips is the folder name; this is user-defined.
  • exec-ssis-stored-proc-ssis-sample is the name of my project.

Note that you need to setup the SSISDB catalog; see SSIS Package Deployment Model in SQL Server 2012 for the details.

Create a Stored Procedure

I will use the following stored procedure in my mssqltips database to execute the SSIS package:

create procedure dbo.execute_ssis_package_sample
 @output_execution_id bigint output
as
begin
 declare @execution_id bigint
 exec ssisdb.catalog.create_execution 
  @folder_name = 'mssqltips'
 ,@project_name = 'exec-ssis-stored-proc-ssis-sample'
 ,@package_name = 'Sample.dtsx'
 ,@execution_id = @execution_id output
 exec ssisdb.catalog.start_execution @execution_id
 set @output_execution_id = @execution_id
end

The following are the main points about the above stored procedure:

  • The stored procedure ssisdb.catalog.create_execution (new in SSIS 2012) is called to create an instance of an SSIS package execution.
  • You pass the folder, project and package to the ssisdb.catalog.create_execution stored procedure.
  • ssisdb.catalog.create_execution returns an output parameter named @execution_id; you need this value to start executing the SSIS package as well as to check the status of package execution.
  • The ssisdb.catalog.start_execution stored procedure (new in SSIS 2012) is called to start the actual execution of the package.
  • The SSIS package execution is asynchronous; i.e. when you call the ssisdb.catalog.start_execution stored procedure you are not waiting for the package to complete.

Execute the Stored Procedure

Launch SSMS, open a New Query, and run the following T-SQL script:

declare @output_execution_id bigint
exec dbo.execute_ssis_package_sample @output_execution_id output
print @output_execution_id

You have now executed an SSIS package from a stored procedure!

Create a Console Application

I will use a .NET console application to demonstrate executing an SSIS package from an application. The C# code below is standard ADO.NET which is the part of the .NET Framework that is used to work with relational databases. Essentially the code simply executes the execute_ssis_package_sample stored procedure described in the previous section.

The following is the "Main" section of the code (i.e. this is the code run when the console application is executed):

Create a Console Application

The following is the ExecSqlProc method called from Main; this is the code that calls the stored procedure that executes the SSIS package:

the ExecSqlProc method called from Main; this is the code that calls the stored procedure that executes the SSIS package

The ExecSqlProc method calls the GetSqlConnection method shown below to open a connection to the mssqltips database:

The ExecSqlProc method calls the GetSqlConnection method shown below to open a connection to the mssqltips database

The connectionName parameter passed to the GetSqlConnection method must match a connection string in the app.config as shown below:

The connectionName parameter passed to the GetSqlConnection method must match a connection string in the app.config as shown below

Check the Results

After running the .NET console application or running the execute-ssis-package-sample stored procedure, you can check the results of the SSIS package execution with the following query (where N is the output_execution_id returned as an output parameter by the ssisdb.catalog.create_execution stored procedure):

select status 
from SSISDB.catalog.executions
where execution_id = N

A status of 7 means the SSIS package ran successfully. A status of 2 indicates that the SSIS package is still running. There are 9 possible status values; see catalog.executions for the details.

The following method can be used to programmatically check the status (it is called from the Main code above; see the code following the "check the status" comment):

A status of 7 means the SSIS package ran successfully
Next Steps
  • SSIS 2012 has built-in stored procedures in the SSISDB database allowing you to easily execute an SSIS package from a stored procedure.
  • While the above sample code is pretty straight forward, remember that the SSIS package execution happens asynchronously; i.e. your code that calls the stored procedure doesn't wait for the SSIS package to finish. You can query the SSISDB.catalog.executions view to determine the status of the SSIS package execution.
  • Download the sample SSIS project and .NET console application here to experiment on your own.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Thursday, October 5, 2017 - 1:38:40 AM - vivek shimpi Back To Top (66922)

i created package in 2012. it's run successfully.

i used sql server 2014. when i try to execute exec dbo.execute_ssis_package_sample it show bellow error :

 

Msg 27123, Level 16, State 1, Procedure create_execution, Line 38

The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.

Msg 27123, Level 16, State 1, Procedure start_execution, Line 32

The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.

 

 

My Code :

 

CREATE PROCEDURE dbo.execute_ssis_package_sample

 @output_execution_id BIGINT OUTPUT

AS

BEGIN

 DECLARE @execution_id BIGINT

 EXEC ssisdb.CATALOG.create_execution 

  @folder_name = 'GST_Hosting'

 ,@project_name = 'GST_PWCProject'

 ,@package_name = 'GST_PWCProject.dtsx'

 ,@execution_id = @execution_id OUTPUT

 EXEC ssisdb.CATALOG.start_execution @execution_id

 SET @output_execution_id = @execution_id

END

Wednesday, July 19, 2017 - 12:25:58 PM - Scott Coleman Back To Top (59687)

If you simply need to run the package, you can create a job that runs it and use 'EXEC msdb.dbo.sp_start_job' to start the job.  There are a number of permission issues to solve depending on whether you want non-sysadmins to run the package.

If you want ANYONE to run the job/package, and don't want to deal with permission issues, a sneaky approach is to define an alert that is activated by some obscure error number and whose response is to run the job.  Any user can then run the job/package with the RAISERROR command.

As far as initializing variables, if you create a SQL configuration for those variables they will be initialized from a configuration table every time the job runs.  You are free to update them before you run the package.

Thursday, December 17, 2015 - 11:39:29 AM - Armando Prato Back To Top (40268)

I was looking for a tip on this topic for something I'm working on and, lo and behold, Ray comes through!

Thanks... this was a big help to me!

Thursday, July 10, 2014 - 8:02:22 AM - Ray Barley Back To Top (32628)

Script out your SQL Server Agent job and you will see the @command parameter I'm referring to.

Are you using a domain account for the SQL Agent service?  Generally that is required when your job references another server.

 

 

Thursday, July 10, 2014 - 6:06:55 AM - Abhi Back To Top (32625)

Hi Ray,

Thank you for your response.

I have given the right server name in sql server agent where ssisb is located.I dont understand why i am getting this problem.Mine is sql server 2012 instance.I have tried to do the same with the package with no spaces in it and I still face the issue. Also I executed the above package by creating a job in the server which has ssis db and it worked fine.So the issue is not with the spaces in the name. And how do i check this @command parameter?

 

Wednesday, July 9, 2014 - 2:45:17 PM - Ray Barley Back To Top (32613)

I am assuming that you want a SQL Agent job running on one server and you want to execute an SSIS package from the Integration Services Catalog on another server.  When you created the job step did you specify SSIS Catalog for the Package source, enter the server name where the Integration Services Catalog exists, then select the package you want for the Package parameter?

Based on the rror message that you reported OPtion "/ISSERVER" is not valid ... I'm wondering if there's something wrong with your @command parameter for the job step.  For instance I created a job step that executes an SSIS package from the catalog in a SQL Agent job and here's what my command parameter looks like:

@command=N'/ISSERVER "\"\SSISDB\mssqltips\exec-ssis-stored-proc-ssis-sample\Sample.dtsx\"" /SERVER localhost /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E'

 

I notice your SSIS package name has spaces in it.  Is that causing a problem?  Is the /Server parameter correct?  Is it a SQL Server 2012 instance? 

You should be able to take everything inside the single quotes of your @command parameter and run DTEXEC from the command prompt.

 

 

Tuesday, July 8, 2014 - 7:40:35 PM - Abhi Back To Top (32595)

Hi,

I have a requirement of job being in one server and package which is to be executed in the job in "SSIS DB" of Development server.When I am trying to execute this job,I am getting error:"OPtion "/ISSERVER" is not valid.

 

sqldev01-server

pkg path:\SSISDB\LoadConsignmentsfromEDI\LoadConsignmentsfromMySQL\testing indexcreation sp for consignment with ssisuser.dtsx

 

I have tested with the job and package(in SSISDB) in the same server and it works fine

Could you please throw some light on this?

 

Thank you

 

Regards,

Abhi

Wednesday, February 5, 2014 - 8:49:14 AM - Raymond Barley Back To Top (29338)

Take a look at this comment above: Wednesday, August 14, 2013 - 7:55:19 AM - Nuren Geodakov

Tuesday, February 4, 2014 - 3:02:27 AM - Mario Back To Top (29324)

How I can get Execution status inside the Stored Procedure? I want to know this because depending if was success or not I want to update a SQL status table.

Friday, September 6, 2013 - 1:20:52 PM - Glenn Back To Top (26651)

This will get your errors back also :)

 

SELECT 

e.execution_id,

e.folder_name,

e.project_name,

e.package_name,

e.start_time,

e.end_time,

e.[Status],

m.[Message]

FROM[SSISDB].[catalog].[executions]e

LEFTOUTERJOIN[SSISDB].[catalog].[operations]o

ONe.process_id=o.process_id

LEFTOUTERJOINSSISDB.[catalog].[operation_messages]m 

ONo.[operation_id]=m.[operation_id]

WHERE 

e.execution_id= [YOURID]

Wednesday, August 14, 2013 - 7:55:19 AM - Nuren Geodakov Back To Top (26297)

Thank you, Ray. You page is very helpful.

For those who need to pass parameters to SSIS packages in the calling stored procedure it can be done this way, for example:

DECLARE @var0 NVARCHAR(500) = @FilePath
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
 @execution_id,
 @object_type=30,
 @parameter_name=N'FilePath', @parameter_value=@var0

Here @FilePath is my calling stored procedure parameter: @FilePAth VARCHAR(500)

See: http://technet.microsoft.com/en-us/library/ff877990.aspx

Now, you can get execution result messages this way:

SELECT om.[message]
FROM internal.operation_messages om WITH (NOLOCK)
JOIN internal.operations op WITH (NOLOCK) ON op.operation_id = om.operation_id
JOIN [catalog].executions ex WITH (NOLOCK) ON ex.process_id = op.process_id
WHERE ex.execution_id = @execution_Id
ORDER BY om.operation_message_id
DESC

Here @execution_Id is the output parameter of the [SSISDB].[catalog].[create_execution] stored procedure.

One more thing. If you need to execute your calling stored procedure synchronously you can use
 WAITFOR DELAY '00:00:00.100'
Something like this:

DECLARE @status INT = 1
WHILE @status NOT IN (3,4,6,7,9)
BEGIN
 WAITFOR DELAY '00:00:00.100'
 SELECT @status = [status] FROM [SSISDB].[catalog].[executions] WITH (NOLOCK)
 WHERE execution_id = @execution_Id
END

Cheers
Nuren

Thursday, July 4, 2013 - 1:54:21 PM - Ray Barley Back To Top (25715)

Response to question from Armondo C on July 4 - I think the starting point with permissions is that you assign permissions to securable objects; e.g. folders, projects, environments, and operations.  Take a look at SSISDB.Catalog.grant_permission at this link:

http://msdn.microsoft.com/en-us/library/ff878150(SQL.110).aspx

In addition take a look at SSISDB.Catalog.effective_object_permissions

 

http://msdn.microsoft.com/en-us/library/ff878149.aspx  this will tell you the effective permissions for a principal

Thursday, July 4, 2013 - 1:14:21 PM - Ray Barley Back To Top (25714)

Response to question from Rajeev on July 4 - SSIS 2012 has a new feature called environment which allows you to specify values for package variables.  Take a look at this blog post for the details: http://blogs.msdn.com/b/mattm/archive/2011/07/25/all-about-server-environments.aspx

 

Thursday, July 4, 2013 - 3:17:49 AM - Armando C Back To Top (25709)

hello:


I need run dtsx packages mounted in SSIS catalog with parameters, throught an stored procedure, because one aplication fired dtsx using stored procedure with parameter values


I have created the stored procedure using SSISDB.Catalog.Create_Execution, Set_Execution_Parameter_Value, and Start_Execution. This works, but for run the user need have db_owner privilegies on principal_db and ssis_admin on SSISD this will only run if the executing user is a windows account, no problem with this but I need the windows account have minimal rigths on principal_db, I know I can't do it using Sql user because is necesary an windows account but when I create my procedure give execution permition for execute stored proc to an win user in the principal database, create the win user on SSISDB and give permision on catalog procedures SSISDB.Catalog.Create_Execution, Set_Execution_Parameter_Value, and Start_Execution and add to my stored procedure the clause "execute as owner",show me next error:

I tried using Execute As and specifying a windows account but I get the error "The current security context cannot be reverted. Please switch to the original database where 'Execute As' was called and try it again."

I tried using "execute as self" and when I try run the procedure with another win user (that give permision) show me is necesary windows account.

My user have permision on db_owner, sysadmin,and ssis_admin, I think i need come into my permisions to an user for execute this, considering that my procedure working with 2 databases principal_db (where record my procedure) and SSISDB (where live the catalog procedures for create execution and put parameters)

can you help me?

Thursday, July 4, 2013 - 12:51:20 AM - Rajeev Back To Top (25707)
Hi, Thanks. That was one of the methods to run a package using a SP. Another would be to expose the package object. My question: Besides sending parameters to the stored procedure, can one set values to the variables used in the package? Rgds,

Wednesday, July 3, 2013 - 8:39:53 AM - Raymond Barley Back To Top (25696)

With an SSIS 2012 project, when you build, rebuild or deploy the project in SQL Server Data Tools, a .ISPAC file is created in the bin/Development folder under your project directory.  The .ISPAC file is then used to deploy the entire project to the SSISDB catalog.

I think configuration is handled as a separate step; for instance after you deploy you can setup parameter and connection managers using SQL Server Management Studio (you navigate to your project in the SSISDB catalog, right click and select Configure.  In addition you can create environments where you specify different sets of variable values.

This is all still new to me but I think this is essentially how it works.

Tuesday, July 2, 2013 - 9:26:03 AM - Avinash reddy Back To Top (25675)

here you deployed package using new feature but u didnt create manifest and config file for package deployment how it is possible sir can i deploy package without manifest and config file if yes please tell me the solution

 














get free sql tips
agree to terms