http://www.databasejournal.com/features/mssql/executing-a-ssis-package-from-stored-procedure-in-sql-server.html

Executing a SSIS Package from Stored Procedure in SQL Server

By Arshad Ali
Here are two different ways a SSIS package can be executed from a stored procedure. In the first approach, we will create a job, making SSIS package call a job step and executing it by calling the sp_start_job system stored procedure from the user defined function. In the second approach, we will enable xp_cmdshell to execute the DTEXEC command line utility from the user defined stored procedure.

Problem Statement

Recently we had a requirement to execute a SSIS package from one of our user stored procedures, but there is no direct way available in SQL Server for executing a SSIS package from a stored procedure. SQL Server has some undocumented system stored procedures for SSIS package management, but none is available that can be used to execute a SSIS package from the stored procedure. Now the question is, how can we execute a SSIS package from the user stored procedure? If the direct method is not available, is there any alternative for this?

Executing a SSIS Package from Stored Procedure

There are basically two ways you can execute your SSIS package from the user stored procedure. First, you can create a SQL Server Agent job that would have a step to execute the SSIS package, and wherever you want to execute it, you can use sp_start_job system stored procedure to kick off the job, which in turn will execute the package. You might not have any schedule attached to the job if you just want to execute it from the user stored procedure, when you don't want your job to be kicked off (SSIS package to be executed) on a defined schedule time/interval. The disadvantage of using this approach is you don't have an easy way if you have to pass some values (assign values to SSIS package variable) at run time. You might need to have a metadata table from where your SSIS package will consume/retrieve the runtime values.
Second, you can enable xp_cmdshell extended stored procedure, and using it you can execute DTEXEC utility to execute your SSIS package. The disadvantage of using this approach is that enablement of xp_cmdshell poses security threats (operating system level access) and hence by default it's disabled. However using this approach provides finer level control of passing SSIS package variables' runtime values easily. In this article, I am assuming the first approach is simple and straightforward and thus I will jump directly to the second approach.
Please make sure you have enabled xp_cmdshell component or extended stored procedure, or else you will get an exception like this:
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

Enabling xp_cmdshell

To enable xp_cmdshell you need to use sp_configure system stored procedure with advance options like this:
Enabling xp_cmdshell component

USE master 
GO 
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
-- WITH OVERRIDE disables the configuration value checking if the value is valid
RECONFIGURE WITH OVERRIDE
GO
-- To enable the xp_cmdshell component.
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE 
GO
-- Revert back the advance option
EXEC sp_configure 'show advanced options', 0
GO 
RECONFIGURE WITH OVERRIDE 
GO

Executing a SSIS package stored in SQL Server from the user stored procedure

Once xp_cmdshell extended stored procedure is enabled, you can execute any operating system command as a command string. In our case, we will be using DTEXEC utility of SSIS to execute the package. Since the SSIS package is stored in SQL Server, we need to use /SQL switch with DTEXEC command and /SET switch to set the values of SSIS variables as shown below:
DTEXEC calls SSIS package from SQL Server

DECLARE @SQLQuery AS VARCHAR(2000)

DECLARE @ServerName VARCHAR(200) = 'ARSHAD-LAPPY' 

SET @SQLQuery = 'DTExec /SQL ^"\DataTransfer^" '
SET @SQLQuery = @SQLQuery + ' /SET \Package.Variables[ServerName].Value;^"'+ @ServerName + '^"'

EXEC master..xp_cmdshell @SQLQuery
GO

Figure 1 - DTEXEC calls SSIS package from SQL Server

Executing a SSIS package stored in file system from the user stored procedure

As our SSIS package is now stored in the file system, we need to use /FILE or /F switch with DTEXEC command and /SET switch to set the values of SSIS variables as shown below. You can see that executing the xp_cmdshell extended stored procedure it outputs, if there is any, results in rows of texts. If you don't want this output to be returned, you can use second parameter (NO_OUTPUT) of this stored procedure:
DTEXEC calls SSIS package from File System

DECLARE @SQLQuery AS VARCHAR(2000)

DECLARE @ServerName VARCHAR(200) = 'ARSHAD-LAPPY' 

SET @SQLQuery = 'DTExec /FILE ^"E:\DataTransfer.dtsx^" '
SET @SQLQuery = @SQLQuery + ' /SET \Package.Variables[ServerName].Value;^"'+ @ServerName + '^"'

EXEC master..xp_cmdshell @SQLQuery
GO

Figure 2 - DTEXEC calls SSIS package from File System
Notes

xp_cmdshell is an extended stored procedure that lets you execute operating system commands and returns any output as rows of texts. This extended stored procedure runs in synchronous mode; that means control will not return until the command completes. By default only sysadmin can execute this extended stored procedure but permission can be granted to other users with a proxy account; for information, click here.
DTEXEC is a command line utility for SSIS configuration and SSIS package execution from SQL Server, SSIS service and file system sources. After execution, this utility returns different exit codes that indicate what happened during execution; for more information click here.

Conclusion

In this article I talked about executing a SSIS package from user defined stored procedure. In the first approach, we created a job, making SSIS package call as a job step and executing it by calling sp_start_job system stored procedure from the user defined function. In the second approach, we enabled xp_cmdshell to execute DTEXEC command line utility from the user defined stored procedure.

Example

USE [HBFR]

/****** Object: StoredProcedure [dbo].[CallDtsx] Script Date: 03/21/2013 19:33:30 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[CallDtsx](@Month as varchar(2)=NULL, @Year as varchar(4)=NULL)

BEGIN

SET NOCOUNT ON;

DECLARE @ssis_cmd VARCHAR(4000)

DECLARE @Packagepath VARCHAR(500)

DECLARE @FileName VARCHAR(500)

Declare @database as varchar(500)

Declare @filepath as varchar(5000)

Declare @sqlquery as varchar(max)

Declare @oem as varchar(50)

--Exec CallDtsx '01','2012'

select @filename=ExtractPath,@oem=code from oem_master

select @database=db_name(dbid) from master..sysprocesses where spid=@@SPID

SET @Packagepath = 'D:\Project Documents\iCIS Enhancements\Damco\Other Phases\Overnight Job Changes\ImportParticipationCSV\Integration Services Project3\bin\Package.dtsx'

set @FileName='\\' + @FileName + '\' + @Year+@month+ '\Participation'

--dtexec /FILE "D:\Project Documents\iCIS Enhancements\Damco\Other Phases\Overnight Job Changes\ImportParticipationCSV\Integration Services Project3\bin\Package.dtsx" /CHECKPOINTING OFF /REPORTING EW /SET "\Package.Variables[User::mFilePath].Properties[Value]";"\\D:\Share\HBFR\Extracts\201201\Participation"

SELECT @ssis_cmd = 'dtexec /FILE "' + @Packagepath + '"'

SELECT @ssis_cmd = @ssis_cmd + ' /CHECKPOINTING OFF /REPORTING EW /SET "\Package.Variables[User::mFilePath].Properties[Value]";"' + @FileName + '"'

print @ssis_cmd

EXEC master..xp_cmdshell @ssis_cmd

END

http://code.msdn.microsoft.com/Calling-a-SSIS-Package-a35afefb

Calling a SSIS Package by creating Job from a Stored Procedure

Problem
Calling a SQL Server Integration Services (SSIS) package from s stored procedure should be an easy task however Microsoft has provided no direct method to do so.

A quick search on Google found this CodePlex example which uses the xp_cmdshell extended stored procedure to execute dtexec via the Windows Command Shell and this blog post (Rick Weyenberg) which describes how to use a stored procedure to create a SQL Server Job which executes a SSIS package via a Job Step.

Although the above examples work quite well, I wasn’t too keen on using xp_cmdshell and I wanted a solution that provided the ability to:

· Notify of execution failure

· Use dynamic package declaration

· Use dynamic variable assignment

Solution
This solution is based on the SQL Server Job example provided by Rick Weyenberg however it uses XML to implement my additional requirements.

It has been been tested on SQL Server 2005 – 2008(R2) and is configured to execute SSIS packages that reside on the same server as the executing stored procedure.

XML Structure
The structure of the XML used in this solution is as follows:

Copy Code

XML

<package name="PackageName" notifyMethod="Method" notifyOperator="Operator"

owner="Owner" runAs="RunAs">

<variable name="Variable1" value="Value"/>

<variable name="Variable2" value="Value"/>

</package>

Package Element

· name: Name of the SSIS package to execute. You need to specify the full package name and path i.e. if a package called SSISTest resides in a folder called Test within MSDB, the package name would be \Test\SSISTest

· notifyMethod: The method used to notify of job failure. The value for this attribute corresponds with SQL Server Agent notification types (Except for “Automatically Delete Job” which is always set to “When the job completes” and “Write to the Windows Application Event Log” which is always set to “When the job fails”) . Leave blank for no notification.

· notifyOperator: The name of the SQL Server Operator to notify when notifyMethod is provided.

· owner: An account in SQL Server that has sufficient permissions to execute jobs

· runAs: An proxy account that has sufficient permissions to execute any tasks within the SSIS package. Leave blank to run under the SQL Server Agent Service Account.

I have only added the owner and runAs attributes to demonstrate that this can be done. I recommend that you do not allow the caller of the stored procedure to nominate which accounts to use.
Variable Element

· name: Name of the SSIS variable you want to assign a value to

· value: Value you want to assign to the variable

Stored Procedure
The stored procedure used to implement this solution is quite lengthy so I’ll break it down into chunks and provide explanations where required.

1) Create the stored procedure shell which accepts a parameter of type XML and implements some basic error handling.

Copy Code

SQL

CREATE PROCEDURE [dbo].[usp_ExecuteSSISPackage]

(

@xVar XML

)

BEGIN

-- Step 2 code goes here

BEGIN TRY

-- Step 3 code goes here

-- Step 4 code goes here

-- Step 5 code goes here

-- Step 6 code goes here

-- Step 7 code goes here

-- Return successfull job creation

RETURN 1

END TRY

BEGIN CATCH

-- Retrieve error details

SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage;

-- Return failed job creation

RETURN 0

END CATCH

END

The error handling implemented here only relates to job creation. Any errors that occur during job execution will be logged in the Windows Application Event Log.
2) Create some local variables.

@VariableTable is used to hold the shredded XML variable data (@xVar parameter)

3) Shred the XML contained in the @xVar parameter and store the resulting data in local variables

Copy Code

SQL

-- Get package attributes

SELECT @PackageName = nref.value('@name[1]','varchar(255)'),

@NotifyMethod = nref.value('@notifyMethod[1]', 'varchar(10)'),

@NotifyOperator = nref.value('@notifyOperator[1]', 'varchar(255)'),

@Owner = nref.value('@owner[1]','varchar(100)'),

@RunAsAccount = nref.value('@runAs[1]', 'varchar(100)')

FROM @xVar.nodes('/package') AS R(nref)

-- Store SSIS variable details in local table variable

INSERT INTO @VariableTable

SELECT nref.value('@name[1]', 'varchar(50)'),

nref.value('@value[1]', 'varchar(50)')

FROM @xVar.nodes('/package/variable') AS R(nref)

4) Create the job.

The job name (@JobName) is made up of the combination of the package name and a GUID. This enables multiple copies of the same package to be executed at the same time.

@delete_level = 3 causes the job to be deleted regardless of whether job execution succeeded

Copy Code

SQL

-- Get the current server name

SET @Server = CONVERT(sysname, SERVERPROPERTY(N'servername'))

-- Create a unique job name

SET @JobName = @PackageName + '_' + CAST(NEWID() AS CHAR(36))

-- Create the job

IF @NotifyMethod = 'E-mail'

BEGIN

EXEC msdb.dbo.sp_add_job @job_name = @JobName, @enabled = 1,

@notify_level_email = 2, -- When the job fails

@notify_email_operator_name = @NotifyOperator,

@notify_level_eventlog = 2, -- When the job fails

@Owner_login_name = @Owner,

@delete_level = 3, -- Delete job regardless of execution state

@job_id = @JobId OUTPUT

END

ELSE IF @NotifyMethod = 'Page'

BEGIN

EXEC msdb.dbo.sp_add_job @job_name = @JobName, @enabled = 1,

@notify_level_page = 2, -- When the job fails

@notify_page_operator_name = @NotifyOperator,

@notify_level_eventlog = 2, -- When the job fails

@Owner_login_name = @Owner,

@delete_level = 3, -- Delete job regardless of executionstate,

@job_id = @JobId OUTPUT

END

ELSE IF @NotifyMethod = 'Net send'

BEGIN

EXEC msdb.dbo.sp_add_job @job_name = @JobName, @enabled = 1,

@notify_level_netsend = 2, -- When the job fails

@notify_netsend_operator_name = @NotifyOperator,

@notify_level_eventlog = 2, -- When the job fails

@Owner_login_name = @Owner,

@delete_level = 3, -- Delete job regardless of execution state,

@job_id = @JobId OUTPUT

END

ELSE

BEGIN

EXEC msdb.dbo.sp_add_job @job_name = @JobName, @enabled = 1,

@notify_level_eventlog = 2, -- When the job fails

@Owner_login_name = @Owner,

@delete_level = 3, -- Delete job regardless of execution state,

@job_id = @JobId OUTPUT

END

-- Add the job to the server

EXEC msdb.dbo.sp_add_jobserver @job_name = @JobName, @server_name = @Server

5) Get the package variables and their associated values.

This code portion loops through the rows in the @VariableTable and builds a string which is used to add the variables and their associated values to the job step. The syntax used to set package variables is:

Copy Code

SQL

/SET \Package.Variables[User::VariableName].Value;VariableValue

e.g.

Copy Code

SQL

/SET \Package.Variables[User::ProjectID].Value;666

Actual stored procedure code.

Copy Code

SQL

-- Build the SSIS variable assignment list

SELECT @MaxID = MAX(ID) FROM @VariableTable

SET @VariableList = ''

SET @CurrentID = 0

WHILE (@CurrentID <= @MaxID)

BEGIN

SELECT @VariableList = @VariableList

+ ' /SET \Package.Variables[User::' + Name + '].Value;' + Value

FROM @VariableTable

WHERE ID = @CurrentID

SET @CurrentID = @CurrentID + 1

END

6) Create the job step

Copy Code

SQL

-- Create the job step

SET @StepCmd = '/SQL "' + @PackageName + '" /SERVER '

+ @Server + ' /CHECKPOINTING OFF ' + @VariableList + ' /REPORTING E'

EXEC msdb.dbo.sp_add_jobstep @job_name = @JobName,

@step_name = N'SSISExecuteStep', @step_id = 1,

@cmdexec_success_code = 0, @on_success_action = 1,

@on_fail_action = 2, @retry_attempts = 0,

@retry_interval = 0, @os_run_priority = 0,

@subsystem = N'SSIS', @command = @StepCmd,

@database_name = N'master', @flags=0, @proxy_name=@RunAsAccount

7) Start the job

Copy Code

SQL

-- Start job

EXEC msdb.dbo.sp_start_job @job_id = @JobId

That’s it. To execute the stored procedure in SSMS use:

Copy Code

SQL

DECLARE @XML XML

SET @XML =

'<package name="\SSISTest\SSISTest" notifyMethod="E-mail" notifyOperator="ITSupport"

owner="Machine\Administrator" runAs="">

</package>'

exec [dbo].[usp_ExecuteSSISPackage] @XML

To call the stored procedure from .Net is very straight forward.

I’m using the Microsoft Enterprise Library 5.0 as it handles the majority of the database code required to connect to SQL Server and execute the stored procedure.

Copy Code

using Microsoft.Practices.EnterpriseLibrary.Data;

using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

using System.Data.Common;

using System;

using System.Data;

class SSIS

{

public int ExecuteSSISPackage()

{

SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();

// This is the stored procedure we created earlier

DbCommand dbCommand =

db.GetStoredProcCommand("[dbo].[usp_ExecuteSSISPackage]");

// Add a paramter of type XML and assign the

String xmlData = "<Your XML Data Goes Here>";

db.AddInParameter(dbCommand, "@xVar", DbType.Xml, xmlData);

// Add a ReturnValue paramter of type INT for error checking

db.AddParameter(dbCommand, "@RETURN_VALUE", DbType.Int16, 0,

ParameterDirection.ReturnValue, false, 0, 0, "",

DataRowVersion.Current, 0);

// Execute the stored procedure

DataSet ds = db.ExecuteQuery(dbCommand);

//Get error indicator

int erroCode =

Convert.ToInt32(db.GetParameterValue(dbCommand, "@RETURN_VALUE"));

return erroCode;

}

Conclusion
In this post I have demonstrated how to call a SSIS package from a SQL Server stored procedure.

http://msdn.microsoft.com/en-IN/library/jj820152.aspx

Deploy and Execute SSIS Packages using Stored Procedures

SQL Server 2012

This topic has not yet been rated - Rate this topic

When you configure an Integration Services project to use the project deployment model, you can use stored procedures in the SSIS catalog to deploy the project and execute the packages. For information about the project deployment model, see Deployment of Projects and Packages.
You can also use SQL Server Management Studio or SQL Server Data Tools (SSDT) to deploy the project and execute the packages. For more information, see the topics in the See Also section.

Tip

You can easily generate the Transact-SQL statements for the stored procedures listed in the procedure below, with the exception of catalog.deploy_project, by doing the following:

1. In SQL Server Management Studio, expand the Integration Services Catalogs node in Object Explorer and navigate to the package you want to execute.

2. Right-click the package, and then click Execute.

3. As needed, set parameters values, connection manager properties, and options in the Advanced tab such as the logging level.

For more information about logging levels, see Enable Logging for Package Execution on the SSIS Server.

4. Before clicking OK to execute the package, click Script. The Transact-SQL appears in a Query Editor window in SQL Server Management Studio.

To deploy and execute a package using stored procedures

1. Call catalog.deploy_project (SSISDB Database) to deploy the Integration Services project that contains the package to the Integration Services server.

To retrieve the binary contents of the Integration Services project deployment file, for the @project_stream parameter, use a SELECT statement with the OPENROWSET function and the BULK rowset provider. The BULK rowset provider enables you to read data from a file. The SINGLE_BLOB argument for the BULK rowset provider returns the contents of the data file as a single-row, single-column rowset of type varbinary(max). For more information, see OPENROWSET (Transact-SQL).

In the following example, the SSISPackages_ProjectDeployment project is deployed to the SSIS Packages folder on the Integration Services server. The binary data is read from the project file (SSISPackage_ProjectDeployment.ispac) and is stored in the @ProjectBinary parameter of type varbinary(max). The @ProjectBinary parameter value is assigned to the @project_stream parameter.

Copy

DECLARE @ProjectBinary as varbinary(max)

DECLARE @operation_id as bigint

Set @ProjectBinary = (SELECT * FROM OPENROWSET(BULK 'C:\MyProjects\ SSISPackage_ProjectDeployment.ispac', SINGLE_BLOB) as BinaryData)

Exec catalog.deploy_project @folder_name = 'SSIS Packages', @project_name = 'DeployViaStoredProc_SSIS', @Project_Stream = @ProjectBinary, @operation_id = @operation_id out

2. Call catalog.create_execution (SSISDB Database) to create an instance of the package execution, and optionally call catalog.set_execution_parameter_value (SSISDB Database) to set runtime parameter values.

In the following example, catalog.create_execution creates an instance of execution for package.dtsx that is contained in the SSISPackage_ProjectDeployment project. The project is located in the SSIS Packages folder. The execution_id returned by the stored procedure is used in the call to catalog.set_execution_parameter_value. This second stored procedure sets the LOGGING_LEVEL parameter to 3 (verbose logging) and sets a package parameter named Parameter1 to a value of 1.

For parameters such as LOGGING_LEVEL the object_type value is 50. For package parameters the object_type value is 30.

Copy

Declare @execution_id bigint

EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'SSIS Packages', @project_name=N'SSISPackage_ProjectDeployment', @use32bitruntime=False, @reference_id=1

Select @execution_id

DECLARE @var0 smallint = 3

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0

DECLARE @var1 int = 1

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=30, @parameter_name=N'Parameter1', @parameter_value=@var1

GO

3. Call catalog.start_execution (SSISDB Database) to execute the package.

In the following example, a call to catalog.start_execution is added to the Transact-SQL to start the package execution. The execution_id returned by the catalog.create_execution stored procedure is used.

Copy

Declare @execution_id bigint

EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'SSIS Packages', @project_name=N'SSISPackage_ProjectDeployment', @use32bitruntime=False, @reference_id=1

Select @execution_id

DECLARE @var0 smallint = 3

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0

DECLARE @var1 int = 1

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=30, @parameter_name=N'Parameter1', @parameter_value=@var1

EXEC [SSISDB].[catalog].[start_execution] @execution_id

GO

To deploy a project from server to server using stored procedures

You can deploy a project from server to server by using the catalog.get_project (SSISDB Database) and catalog.deploy_project (SSISDB Database) stored procedures.
You need to do the following before running the stored procedures.

· Create a linked server object. For more information, see Create Linked Servers (SQL Server Database Engine).

On the Server Options page of the Linked Server Properties dialog box, set RPC and RPC Out to True. Also, set Enable Promotion of Distributed Transactions for RPC to False.

· Enable dynamic parameters for the provider you selected for the linked server, by expanding the Providers node under Linked Servers in Object Explorer, right-clicking the provider, and then clicking Properties. Select Enable next to Dynamic parameter.

· Confirm that the Distributed Transaction Coordinator (DTC) is started on both servers.

Call catalog.get_project to return the binary for the project, and then call catalog.deploy_project. The value returned by catalog.get_project is inserted into a table variable of type varbinary(max). The linked server can’t return results that are varbinary(max).
In the following example, catalog.get_project returns a binary for the SSISPackages project on the linked server. The catalog.deploy_project deploys the project to the local server, to the folder named DestFolder.

Copy

declare @resultsTableVar table (

project_binary varbinary(max)

INSERT @resultsTableVar (project_binary)

EXECUTE [MyLinkedServer].[SSISDB].[catalog].[get_project] 'Packages', 'SSISPackages'

declare @project_binary varbinary(max)

select @project_binary = project_binary from @resultsTableVar

exec [SSISDB].[CATALOG].[deploy_project] 'DestFolder', 'SSISPackages', @project_binary

SQLSolutions and SSIS

Wednesday, July 24, 2013

Query to Return all jobs that ran in last 24 hours.

Tuesday, July 9, 2013

Calling SSIS from SP

Executing a SSIS Package from Stored Procedure in SQL Server

Problem Statement

Executing a SSIS Package from Stored Procedure

Enabling xp_cmdshell

Executing a SSIS package stored in SQL Server from the user stored procedure

Executing a SSIS package stored in file system from the user stored procedure

Conclusion

Deploy and Execute SSIS Packages using Stored Procedures

Deploy and Execute SSIS Packages using Stored Procedures