CODEWITHSUNDEEP
sqlsql-serverssis
user3751161
user3751161

Reputation: 3

SSIS Package Execution from SSMS of SQL Server

How can we execute SSIS package from a stored procedure?

I tried by deploying ssis package. Is there any way to run ssis package from a stored procedure without scheduling and not through a job?

Upvotes: 0

Views: 329

Answers (2)

Nick H.
Nick H.

Reputation: 1616

If you have SQL Server 2012 and you're using the project deployment model, it's very easy to start SSIS packages through a stored procedure.

Here's one of mine (simplified) to show you how it's done: 

-- SET THESE VARIABLES YOURSELF (OPTIONAL IF YOU USE ENVIRONMENTS)
    DECLARE @environment    AS NVARCHAR(100);


    -- DON'T SET THESE
    DECLARE @execution_id   AS BIGINT;
    DECLARE @packageName    AS NVARCHAR(128);
    DECLARE @projectName    AS NVARCHAR(128);
    DECLARE @folderName     AS NVARCHAR(128);
    DECLARE @refID          AS BIGINT


    SELECT 
          @packageName  = pck.name
        , @projectName  = p.name
        , @folderName   = f.name
        , @refID        = r.reference_id
    FROM [SSISDB].[catalog].[packages] pck
        INNER JOIN [SSISDB].[catalog].[projects] p  
            ON pck.project_id = p.project_id
        INNER JOIN [SSISDB].[catalog].[folders] f   
            ON p.folder_id = f.folder_id
        INNER JOIN [SSISDB].[catalog].[environment_references] r    
            ON p.project_id = r. project_id
    WHERE 
        pck.[name]              = 'ENTER THE NAME OF YOUR PACKAGE HERE'
        -- BELOW IS OPTIONAL IF YOU USE ENVIRONMENTS
        AND r.environment_name  = @environment;


    -- CREATE YOUR EXECUTION (DOESN'T START IT, THINK OF IT LIKE A PLACEHOLDER)
        EXEC [SSISDB].[catalog].[create_execution] 
        @package_name       = @packageName, 
        @execution_id       = @execution_id OUTPUT, 
        @folder_name        = @folderName, 
        @project_name       = @projectName, 
        @use32bitruntime    = False, 
        @reference_id       = @refID;

    -- WHAT DO YOU WANT YOUR LOGGING LEVEL TO BE?
        DECLARE @var0 SMALLINT = 1;  --Basic


    -- NEED TO SET ALL YOUR PARAMETERS HERE (INCLUDING LOGGING LEVEL)
    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50
        , @parameter_name = N'LOGGING_LEVEL', @parameter_value = @var0; -- BASIC

    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=30
        , @parameter_name = N'YOUR CUSTOM PACKAGE PARAM', @parameter_value = 'YOUR CUSTOM VALUE';


    -- YOU GOOD TO GO? START IT
    EXEC [SSISDB].[catalog].[start_execution] @execution_id = @execution_id;

Upvotes: 1

rvphx
rvphx

Reputation: 2402

Use the below. I dont know what version you are using, but you should be able to figure that out yourself. 

exec master.dbo.xp_cmdshell '""C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" /F "location of your SSIS package"

Upvotes: 0

Related Questions