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
Posted
by Kevin Shyr
on Geeks with Blogs
See other posts from Geeks with Blogs
or by Kevin Shyr
Published on Wed, 14 Nov 2012 20:20:07 GMT
Indexed on
2012/11/14
23:01 UTC
Read the original article
Hit count: 647
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
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
© Geeks with Blogs or respective owner
