Execute SSIS via Stored Procedure (SSIS 2012)

13 Aug
August 13, 2012

The SSIS 2012 Catalog integration with SQL Server comes with the advantage of being able to execute SSIS packages indigenously from within T-SQL, along with a host of other functionality including improved monitoring and logging, integrated security and obviously the new deployment model.

Executing SSIS packages by calling a stored procedure can be handy in many situations, but also comes with some (albeit minor) stipulations and quirks, this post tries to go through some of the basics of executing SSIS packages through stored procedures, while also touching lightly on some of the issues I encountered while having a go at this new SSIS execution method.

SSIS packages are executed asynchronously through a collection of procedures, these procedures perform functions such as initialize the execution context of a package, control the package/project/environment parameters, and kick off a particular execution context.

So lets begin with an example, we start by kicking-off an SSIS package execution context using the stored procedure: [SSISDB].[catalog].[create_execution]:

    DECLARE @exec_id BIGINT

    EXEC [SSISDB].[catalog].[create_execution]
        @package_name=N'Test.dtsx',     --SSIS package name TABLE:(SELECT * FROM [SSISDB].internal.packages)
        @folder_name=N'Warehouse ETLs', --Folder were the package lives TABLE:(SELECT * FROM [SSISDB].internal.folders)
        @project_name=N'ImportPackages',--Project name were SSIS package lives TABLE:(SELECT * FROM [SSISDB].internal.projects)
        @use32bitruntime=FALSE,
        @reference_id=NULL,             --Environment reference, if null then no environment configuration is applied.
        @execution_id=@exec_id OUTPUT   --The paramter is outputed and contains the execution_id of your SSIS execution context.

    SELECT @exec_id

This does not actually run the SSIS package, but instead it starts an execution context, with a specific execution_id value which is returned to the user as a result of executing the stored procedure, this execution_id will be used for various functions such as adding parameters to the execution context, starting the execution of the package and checking on the status of the package.

Once a package is created, it will have an Execution Status of Created (1). You can query the package execution status (in numerical format) using the following stored procedure:

  SELECT [STATUS]
  FROM [SSISDB].[internal].[operations]
  WHERE operation_id = @exec_id

There is a handy tip by Jamie Thomson about SSIS Execution Statuses.

Just one more thing to note here, if you get any of the project/folder/package names wrong you’ll get an error that looks like this:
Cannot access the package or the package does not exist. Verify that the package exists and that the user has permissions to it.

Just make sure you get the names correct, you can use the SELECT procedures above to verify the names you could use for each parameters.

Now in order to add parameters to an SSIS package execution context, you can call the following stored procedure:

    DECLARE @IntParam sql_variant = 2 --Some random parameter value, needs to be in sql_variant format

    EXEC [SSISDB].[catalog].[set_execution_parameter_value]
        @exec_id,  -- The execution_id value we received by calling [create_execution]
        @object_type=30,  --30 is Package Parameters, you can also use 20 for Project parameters or 50 for Environment
        @parameter_name=N'ParamDateOffset',  --Parameter name
        @parameter_value=@IntParam

The code above is pretty much self explanatory, again make sure you get the object_type parameters correct here (i.e. whether they are package level parameters of project level parameters etc.), otherwise you’ll get a nasty error about parameter not existing or not having permission to set them.

Finally when you are ready to execute an SSIS package, you can use the following simple stored procedure:

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

This will change the package Execution Status from Created (1) to Running (2).

It is important to note that if a package is left un-executed (and in the Created (1) Execution Status), then the next time SSIS maintenance job runs it will change the Execution Status to Ended Unexpectedly (6).

Here you could monitor the SSIS package through the relevant log tables in the SSIS Catalog DB, or using the more friendly SSRS reports to follow the progress of your SSIS package. If everything goes well, the SSIS package Execution Status will change to Succeeded (8).

SSIS Windows Authentication Account Requirement

In order to execute SSIS packages, you will need to execute the above stored procedures using a Windows Authentication Account rather than SQL Server Authentication Account, otherwise you will be at the receiving end of the following error:

The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.
A .NET Framework error occurred during execution of user-defined routine or aggregate “start_execution_internal”:
System.Data.SqlClient.SqlException: The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.

Although annoying, this requirement makes sense considering SSIS executes outside the context of SQL Server and as part of an OS process, which means that a Windows account is necessary in order to execute a package.

I tried changing the execution context (through EXECUTE AS) but that didn’t seem to help, so I resorted to using a Windows Authenticated Account in order to execute an SSIS package via stored procedure.

Execute SSIS via Stored Procedure (SSIS 2012) rated 4 out of 5 by 1 readers

Execute SSIS via Stored Procedure (SSIS 2012) , 4.0 out of 5 based on 1 ratings

* * *     8 votes
Tags: , , ,
14 replies
  1. Michal Poziemski says:

    Beware that this only works if you are logged in to sql server box. If you try to run this command from remote box (ie. SSMS connect to remote server from your local desktop) this won’t work. You’ll get error like “Access denied for user NT AUTHORITY\ANONYMOUS LOGON”

    Reply
  2. Armando C says:

    Hello, I hope can help me!

    I have made one stored procedure based on EXEC [SSISDB].[catalog][set_execution_parameter_value].

    My store receive three parameters “nombre” (Varchar type, without problems because I Set value from stored procedure), “FechaInicial” and “FechaFinal” (both are package’s parameter in “String” format in SSIS) When I try Execute the DTS show me an error.

    I think is because the @parameter_value clause expect the value in the “N’value” format, do you have some idea?

    Here are my code:

    Create procedure [dbo].[Sp_Ejecuta_dts_2012]
    @Nombre varchar(100),
    @FechaInicial varchar(100),
    @FechaFinal varchar(100)
    as
    Begin


    Declare @execution_id bigint
    EXEC [SSISDB].[catalog].[create_execution]
    @package_name= @Nombre,
    @execution_id=@execution_id OUTPUT,
    @folder_name=N’Folder’,
    @project_name=N’Project’,
    @use32bitruntime=False,
    @reference_id=Null
    Select @execution_id
    end


    begin
    declare @FechaInicial_ex nvarchar(100)
    set @FechaInicial_ex = @FechaFinal
    EXEC [SSISDB].[catalog].[set_execution_parameter_value]
    @execution_id,
    @object_type=20,
    @parameter_name=N’FechaInicial’,
    @parameter_value= @FechaInicial
    end


    begin
    declare @FechaFinal_ex nvarchar(100)
    set @FechaFinal_ex = @FechaFinal
    EXEC [SSISDB].[catalog].[set_execution_parameter_value]
    @execution_id,
    @object_type=20,
    @parameter_name=N’FechaFinal’,
    @parameter_value= @FechaInicial
    end

    begin
    exec [SSISDB].[catalog].[set_execution_parameter_value]
    @execution_id,
    @object_type=50,
    @parameter_name=N’SYNCHRONIZED’,
    @parameter_value=1


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

    Execution sentence:

    execute [dbo].[Sp_Ejecuta_dts_2012]
    @Nombre=’name.dtsx’,
    @FechaInicial= N’2013/01/01′,
    @FechaFinal = N’2013/01/01′

    Error Message in SQL

    “Msg 27147, Level 16, State 1, Procedure check_data_type_value, Line 26 The data type of the input value is not compatible with the data type of the ‘String’.

    Someone idea, Thanks!!

    Reply
    • admin says:

      Hey Armando,

      I think the issue stems from the fact that you are using nvarchar fields rather than sql_variant to set parameters on your execution context.

      Here is what sql_variant is on msdn

      I seem to recall that when setting SSIS parameters, you’ll need to pass the parameter value as a sql_variant data type.

      Give that a go and let me know how you get on :)

      Cheers!

      Reply
  3. Armando C says:

    Yes man, the problem was the datatype in the procedure… but now i have another problem…

    I have created my procedure and run without problems with my user my user is sysadmin in the database and ssis_admin in SSISDB but I need another user with basic permisions (reader or less) and with this user can execute my procedure, I tried 2 options the first i grant execution on the sp to my “basic user” and when I tried run the package say me:

    “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.

    (1 row(s) affected)
    Msg 27123, Level 16, State 1, Procedure set_execution_parameter_value, Line 36
    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 set_execution_parameter_value, Line 36
    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 set_execution_parameter_value, Line 36
    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.

    after this I add on my store procedure the clause EXECUTE AS OWNER when I did it and try run the stored with the “basic user” show me a message wich say:

    The server principal “domain\user” is not able to access the database “SSISDB” under the current security context.

    Something idea?

    Thanks!!

    Reply
  4. Armando C says:

    Hi man, me again:

    More in concret i need wich one user (sql login prefferly) with minimun rigths in the database can execute ssis througth store procedure, (my stored procedure wich i programed) i have read about the accouns for execute catalog view’s of ssis’ catalog and say only can execute this catalogs Windows authentification account because “SSIS catalog needs the windows authenticated tocontrol the package acces during run time” do you have any idea for can make it?

    thanks!

    Reply
    • admin says:

      Hey Armando,

      Thats right, executing SSIS packages requires a Windows Authentication, thats because SSIS is a separate service (from SQL Server) and requires access to system resources outside of the SQL Server environment (essentially to run the DTExec.exe for executing packages and any other system resources the package itself requires).

      To resolve the issue, you’ll need to give the ‘Domain\user‘ account execution rights over the SSIS project.

      You can do that by mapping the user to the SSISDB database (on the Public user-group), then right-click on the SSIS project folder (in the catalog) and select Properties, then add the ‘domain\user‘ details.

      There is an excellent article by Ke Yang on managing execution rights on the SSIS catalog that am sure you’ll find helpful!

      Let me know how you get on!! :)

      Cheers.

      Reply
  5. Armando C says:

    I give execution permissions on my procedure to one user with windows authentication (with db_owner permissions on my data base and ssis admin on the SSISDB) and he can run the procedure without problem, but just the db_onwer role is the problem,

    I tried run my stored procedure with clause with execute as OWNER and when I try run the stored procedure I get an error:

    “The server principal user is not able to access the database ssisdb under current security context”

    I have read about is and the problem is because need db across chaining I suppose because one db is mydtabase where are sp and the other database is SSISB (this for use execute as context) I followed the steps by db cross chaing but give an error because the sid of my master is diferent that sid of mydatabase and ssisdb database and I can’t change this….

    The main problem is that with db_onwer role run ok, but i need the user can’t see and take control of all objects in my data base, the user only must can run this sp!!

    someone idea?

    Thanks for all!

    Reply
    • Links Naji says:

      Hi Armando,

      Sorry for not replying for ages, I was away on holiday… I really hope you found the answer to your question!, but if not, and if I understand you correctly, then have you tried adding the following to your SP:

      EXECUTE AS ‘domain\user’

      Instead of EXECUTE AS Owner

      and give the ‘domain\user’ permissions to be the db_owner on the database, and the ssis_admin on the SSISDB database?

      I will try and replicate your scenario and see if I can get it to work, am sure there is an easy workaround, just need to get my head around the question exactly!

      Cheers

      Reply
      • Alan Machurey says:

        It sounds like I have the same setup as Armando and I can’t get this setup to work. This is the error I receive when I run the stored procedure.

        “The current security context cannot be reverted. Please switch to the original database where ‘Execute As’ was called and try it again.”

        I’m calling a SP in Database A with a SQL Login which is trying to execute an SSIS package in SSISDB. The SP is running with EXECUTE AS [windows login]. Is this possible to do??

        Reply
    • Juha S says:

      Did Armando, or any other, found a working solution to this :) I’m having the same problem now as well.

      My stored procedure is executing a SSIS-package. I’d need to call this procedure by using SQL Server Authentication.

      Reply
  6. Alex says:

    Hi, good article!

    Can you help me with one thing.
    I’m using similar ssis package execution in my project.
    I have a job with few steps, one of them (step 1) run ssis package synchronously, then all other steps.
    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id1, @object_type=50, @parameter_name=N’SYNCHRONIZED’, @parameter_value=1

    And everything worked fine.
    But recently I found out that if for some reason package will fail it won’t prevent failing the whole job and continue execution
    Do you know is there any way to configure this execution in such way that when the ssis package fail, it will throw error and prevent my process from further running.

    I would really appreciated for any help.

    Thanks,
    Alex

    Reply
  7. Punit Kishore says:

    Hi,

    I am getting below error when we are parametrizing the id.I am using string in SSIS.But in the sql we can’t provide string.How to resolve this .

    I Procedure check_data_type_value, Line 26
    The data type of the input value is not compatible with the data type of the ‘String’.

    Reply
    • Links Naji says:

      Hey Punit, I think u need to use sql_variant instead of string as the parameter’s type, but then again, I haven’t worked with SQL Server for a couple of years now, so things might have changed.

      Reply

Trackbacks & Pingbacks

  1. […] Be aware we can now call SSIS packages from within T-SQL scripts: Execute SSIS via Stored Procedure (SSIS 2012) […]

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply to Juha S Cancel reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>