Time to stop using “Execute Package Task”– a way to execute package in SSIS catalog taking advantage of the new project deployment model ,and the logging and reporting feature

I set out to find a way to dynamically call package in SSIS 2012.  The following are 2 excellent blogs I found; I used them heavily.  The code below has some addition to parameter types and message types, but was made essentially derived entirely from the blogs.

http://sqlblog.com/blogs/jamie_thomson/archive/2011/07/16/ssis-logging-in-denali.aspx

http://www.ssistalk.com/2012/07/24/quick-tip-run-ssis-2012-packages-synchronously-and-other-execution-options/

 

The code:

Every package will be called by a PackageController package.  The packageController is initialized with some information on which package to run and what information to pass in.

 

The following is the stored procedure called from the “Execute SQL Task”.  Here is the highlight of the stored procedure

  • It takes in packageName, project name, and folder name (folder in SSIS project deployment to SSIS catalog)
  • The stored procedure sets the package variables of the upcoming package execution
  • Execute package in SSIS Catalog
  • Get the status of the execution.  Also, if exists, get the error message’s message_id and store them in the management database.
  • Return value to “Execute SQL Task” to manage failure properly

CREATE PROCEDURE [AUDIT].[LaunchPackageExecutionInSSISCatalog]

       @PackageName NVARCHAR(255)

       , @ProjectFolder NVARCHAR(255)

       , @ProjectName NVARCHAR(255)

       , @AuditKey INT

       , @DisableNotification BIT

       , @PackageExecutionLogID INT

AS

BEGIN TRY

       DECLARE @execution_id BIGINT = 0;

       -- Create a package execution

       EXEC [SSISDB].[catalog].[create_execution]

                    @package_name=@PackageName,

                    @execution_id=@execution_id OUTPUT,

                    @folder_name=@ProjectFolder,

                    @project_name=@ProjectName,

                    @use32bitruntime=False;

 

       UPDATE [AUDIT].[PackageInstanceExecutionLog] WITH(ROWLOCK)

       SET [SSISCatalogExecutionID] = @execution_id

       WHERE [PackageInstanceExecutionLogID] = @PackageExecutionLogID

 

       -- this is to set the execution synchronized so that I can check the result in the end

       EXEC [SSISDB].[catalog].[set_execution_parameter_value]

                    @execution_id, 

                    @object_type=50,

                    @parameter_name=N'SYNCHRONIZED',

                    @parameter_value=1; -- true

 

       /********************************************************

        ********************************************************

             Section: setting parameters

                    Source table:  SSISDB.internal.object_parameters

             object_type list:

                    20: project level variables

                    30: package level variables

                    50: execution parameter

        ********************************************************

        ********************************************************/

       EXEC [SSISDB].[catalog].[set_execution_parameter_value]

                    @execution_id, 

                    @object_type=30,

                    @parameter_name=N'FromParent_AuditKey',

                    @parameter_value=@AuditKey; -- true

 

       EXEC [SSISDB].[catalog].[set_execution_parameter_value]

                    @execution_id, 

                    @object_type=30,

                    @parameter_name=N'FromParent_DisableNotification',

                    @parameter_value=@DisableNotification; -- true

 

       EXEC [SSISDB].[catalog].[set_execution_parameter_value]

                    @execution_id, 

                    @object_type=30,

                    @parameter_name=N'FromParent_PackageInstanceExecutionID',

                    @parameter_value=@PackageExecutionLogID; -- true

       /********************************************************

        ********************************************************

             Section: setting variables END

        ********************************************************

        ********************************************************/

 

 

       /* This section is carried over from example code

          I don't see a reason to change them yet

       */

       -- Set our package parameters

       EXEC [SSISDB].[catalog].[set_execution_parameter_value]

                    @execution_id, 

                    @object_type=50,

                    @parameter_name=N'DUMP_ON_EVENT',

                    @parameter_value=1; -- true

 

       EXEC [SSISDB].[catalog].[set_execution_parameter_value]

                    @execution_id, 

                    @object_type=50,

                    @parameter_name=N'DUMP_EVENT_CODE',

                    @parameter_value=N'0x80040E4D;0x80004005';

 

       EXEC [SSISDB].[catalog].[set_execution_parameter_value]

                    @execution_id, 

                    @object_type=50,

                    @parameter_name=N'LOGGING_LEVEL',

                    @parameter_value= 1; -- Basic

 

       EXEC [SSISDB].[catalog].[set_execution_parameter_value]

                    @execution_id, 

                    @object_type=50,

                    @parameter_name=N'DUMP_ON_ERROR',

                    @parameter_value=1; -- true

 

                   

       /********************************************************

        ********************************************************

             Section: EXECUTING

        ********************************************************

        ********************************************************/

       EXEC [SSISDB].[catalog].[start_execution]

                    @execution_id;

       /********************************************************

        ********************************************************

             Section: EXECUTING END

        ********************************************************

        ********************************************************/

 

 

       /********************************************************

        ********************************************************

             Section: checking execution result

                    Source table:  [SSISDB].[catalog].[executions]

             status:

                    1: created

                    2: running

                    3: cancelled

                    4: failed

                    5: pending

                    6: ended unexpectedly

                    7: succeeded

                    8: stopping

                    9: completed

        ********************************************************

        ********************************************************/

       if EXISTS(SELECT TOP 1 1

                           FROM [SSISDB].[catalog].[executions] WITH(NOLOCK)

                           WHERE [execution_id] = @execution_id

                                 AND [status] NOT IN (2, 7, 9)) BEGIN

 

             /********************************************************

              ********************************************************

                    Section: logging error messages

                           Source table:  [SSISDB].[internal].[operation_messages]

                    message type:

                           10:  OnPreValidate 

                           20:  OnPostValidate 

                           30:  OnPreExecute 

                           40:  OnPostExecute 

                           60:  OnProgress 

                           70:  OnInformation 

                           90:  Diagnostic 

                           110:  OnWarning

                           120:  OnError

                           130:  Failure

                           140:  DiagnosticEx 

                           200:  Custom events 

                           400:  OnPipeline

                    message source type:

                           10:  Messages logged by the entry APIs (e.g. T-SQL, CLR Stored procedures) 

                           20:  Messages logged by the external process used to run package (ISServerExec) 

                           30:  Messages logged by the package-level objects 

                           40:  Messages logged by tasks in the control flow 

                           50:  Messages logged by containers (For, ForEach, Sequence) in the control flow 

                           60:  Messages logged by the Data Flow Task 

                   

              ********************************************************

              ********************************************************/

 

             INSERT INTO AUDIT.PackageInstanceExecutionOperationErrorLink

                    SELECT @PackageExecutionLogID

                                 ,[operation_message_id]

                           FROM [SSISDB].[internal].[operation_messages] WITH(NOLOCK)

                           WHERE operation_id = @execution_id

                                 AND message_type IN (120, 130)

            

             EXEC [AUDIT].[FailPackageInstanceExecution] @PackageExecutionLogID, 'SSISDB Internal operation_messages found'

 

             GOTO ReturnTrueAsErrorFlag

 

             /********************************************************

              ********************************************************

                    Section: checking messages END

              ********************************************************

              ********************************************************/

 

             /* This part is not really working, so now using rowcount to pass status

             --DECLARE @PackageErrorMessage NVARCHAR(4000)

             --SET @PackageErrorMessage = @PackageName + 'failed with executionID: ' + CONVERT(VARCHAR(20), @execution_id)

 

             --RAISERROR (@PackageErrorMessage -- Message text.

             --     , 18 -- Severity,

             --     , 1 -- State,

             --     , N'check table AUDIT.PackageInstanceExecutionErrorMessages' -- First argument.

             --     );

             */

       END

       ELSE BEGIN

             GOTO ReturnFalseAsErrorFlagToSignalSuccess

       END

       /********************************************************

        ********************************************************

             Section: checking execution result END

        ********************************************************

        ********************************************************/

END TRY

BEGIN CATCH

       DECLARE @SSISCatalogCallError NVARCHAR(MAX)

       SELECT @SSISCatalogCallError = ERROR_MESSAGE()

 

       EXEC [AUDIT].[FailPackageInstanceExecution] @PackageExecutionLogID, @SSISCatalogCallError

 

       GOTO ReturnTrueAsErrorFlag

END CATCH;

 

 

/********************************************************

 ********************************************************

   Section: end result

 ********************************************************

 ********************************************************/

ReturnTrueAsErrorFlag:

       SELECT CONVERT(BIT, 1) AS PackageExecutionErrorExists

ReturnFalseAsErrorFlagToSignalSuccess:

       SELECT CONVERT(BIT, 0) AS PackageExecutionErrorExists

 

GO

 

 

[2013-06-10] Update:

Evan, I tried several times, but the system wouldn't allow me to post a comment.  I have no idea why.  Anyway, regarding your question, the SQL Task I used was just a stored procedure call.  You can script that out of SSMS so that your column mapping will have those parameter name, or just list them in order.  The parameters passed in are just those listed in the stored procedure.  The section just before the stored procedure code explains what those parameters are.  You might need to try running through an actual SSIS project deployment to understand what those values should be.

Print | posted on Wednesday, November 14, 2012 2:20 PM

Feedback

# re: Time to stop using “Execute Package Task”

Left by Evan Van Gelder at 6/5/2013 12:37 PM
Gravatar Thanks, this looks like what I need to convert to a Project Deployment Model.

Can you help me with the calling sequence used in the SQL Task? Specifically, what parameters are you passing in?

Thanks again,

Evan

# Help with similar procedure..

Left by Armando C. at 6/12/2013 7:22 PM
Gravatar 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!!

Your comment:





 
 

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski