CALL SSIS Package from a script

Question

Hi

How to run SSIS Package(sql server 2008 Intergration services stored package folder) from a script/stored procedure. Also please let me know what LEVEL of  access I need to run (windows & sql authenitication). Thanks in advance

Regards

Sajith

Answer 1

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

http://www.codeproject.com/Articles/27156/How-to-Call-SSIS-Package-from-the-Stored-Procedure

---

Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

Hi, This is how I call one of my SSIS packages using a stored procedure. Its just to give you an idea, I had to code it per the client requirements. This would give a starting point. The caveat is I am calling xp_cmdshell not all clients would like this idea.

CREATE PROCEDURE [dbo].[USP_ExecuteSSIS]  
    @SSISPath VARCHAR(500),  
    @Server VARCHAR(500),
    @SSISConfig VARCHAR(500),
    @TableName VARCHAR(500),
    @File VARCHAR(500)
AS
SET NOCOUNT ON
DECLARE
@Cmd VARCHAR(4000),
@ReturnCode INT,
@Msg VARCHAR(1000)
SELECT @Cmd = 'C:\"program files (x86)"\"Microsoft SQL Server"\100\DTS\Binn\DTEXEC.exe /FILE "' + @SSISPath + '\SSISPackage.dtsx" /CONFIGFILE "' + @SSISConfig + '" /MAXCONCURRENT 1 /CHECKPOINTING OFF  /REPORTING EWCDI'
    + ' /SET \Package.Variables[User::DBServer].Properties[Value];' + @Server
    + ' /SET \Package.Variables[User::TextFile].Properties[Value];' + @File
    + ' /SET \Package.Variables[User::DBTable].Properties[Value];' + @TableName
EXEC @ReturnCode = xp_cmdshell @Cmd
IF @ReturnCode <> 0
BEGIN
   SELECT @Msg = 'SSIS package execution failed - ' + @SSISPath + 'Instance Name: ' + @Server + '.dbo.DBName'
END
RETURN @ReturnCode
GO

Hope this helps...........

---

Ione

Answer 3

For security reasons I do not recommend any method that executes command by enabling xp_cmdshell.

You can call your Package easily if you create a SQL Server Agent job that executes it without any schedule and then execute sp_start_job from within your procedure.

The user calling the job would have to be the owner of the job and member of the msdb role SQLAgentUserRole

You might also need a Proxy account for running the “run SSIS package step”

Read more here:

SQL Server Agent Jobs for Packages: http://technet.microsoft.com/de-de/library/ms141701.aspx

SQL Server Agent Fixed Database Roles: http://technet.microsoft.com/de-de/library/ms188283.aspx

Create a SQL Server Agent Proxy: http://technet.microsoft.com/de-de/library/ms175834.aspx

Using this method you do not open your server to threats coming via xp_cmdshell, which is disabled by default for a reason, and also get to use the nice job features like alerting or history.

 

 

---

Andreas Wolter | Microsoft Certified Master SQL Server
Blog: www.insidesql.org/blogs/andreaswolter
Web: www.andreas-wolter.com