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.