SSIS – Generic StoredProcedure to execute SSIS 2012 packages
SSIS 2012 has full of surprises with new features specifically when it comes to the execution of packages and logging mechanism. We can make lot these new features into dynamic process include package execution and logging and intern a game changer for potential SSIS 2012 frameworks.
Here planning to present a generic stored procedure to execute any package including cross project packages with required input details.
Step 1: Development
Developed a generic stored procedure with required input values and using my existing Control database for this demo. Most of the logic is self-explanatory with required comments and included the testing scripts at the end to place all the logic in one place for development and testing.
USE [Control]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*****************************************************************************************
** File : StoredProcedure [dbo].[Usp_ExecPackage]
** Name : dbo.Usp_ExecPackage
** Desc : Generic Stored proc to execute SSIS 2012 packages, includes parallel execusion of cross project packages
** Author: Bipassion
** Date : 2012-11-17
******************************************************************************************
** Change History
******************************************************************************************
** Version Date Author Description
** -- --------- ------- ------------------------------------------------
** 1.0 2012/11/17 Bipassion dbo.Usp_ExecPackage developed using TDD with all possible cases
*******************************************************************************************/
CREATE PROCEDURE [dbo].[Usp_ExecPackage] (@packageName VARCHAR(1000)
--= 'Employee.dtsx' -- Input
,
@projectName VARCHAR(100)
--= 'Database_Load' -- Input
,
@folderName VARCHAR(100)
--= 'Database_Load' -- Input
,
@objectType INT
--= 50 -- Input
,
@parameterName VARCHAR(100)
--= 'LOGGING_LEVEL'-- Input
,
@parameterValue SMALLINT
--= 1 -- Input
,
@environmentName VARCHAR(20)
-- = 'DEV' -- Input
)
AS
BEGIN
DECLARE @executionId BIGINT
DECLARE @referenceId INT
DECLARE @processCode INT = 0 -- Logical process check ID
DECLARE @validationMessage VARCHAR(100) = NULL
IF( @processCode = 0 )
BEGIN
-- Input @projectName validation
IF NOT EXISTS (SELECT *
FROM [SSISDB].[catalog].[projects]
WHERE name = @projectName)
BEGIN
SET @validationMessage =
'Project Name is not validated to system Project Name'
SET @processCode = 1
END
END
IF( @processCode = 0 )
BEGIN
-- Input @folderName validation
IF NOT EXISTS (SELECT F.name
FROM [SSISDB].[internal].[folders] F
JOIN [SSISDB].[internal].[projects] P
ON F.folder_id = P.folder_id
WHERE F.name = @folderName)
BEGIN
SET @validationMessage =
'Folder Name is not validated to system Folder name of corresponding Project Name'
SET @processCode = 1
END
END
IF( @processCode = 0 )
BEGIN
-- Input @packageName validation
IF NOT EXISTS (SELECT PK.*
FROM ssisdb.[catalog].[packages] PK
JOIN [SSISDB].[internal].[projects] P
ON PK.project_id = P.project_id
WHERE p.name = @projectName
AND PK.name = @packageName)
BEGIN
SET @validationMessage =
'Package Name is not validated to system Package name of corresponding Project Name'
SET @processCode = 1
END
END
IF( @processCode = 0 )
BEGIN
-- Input @environmentName validation
IF NOT EXISTS (SELECT *
FROM [SSISDB].[catalog].environment_references
WHERE environment_name = @environmentName)
BEGIN
SET @validationMessage =
'Environmemt_name is not validated to system Environments'
SET @processCode = 1
END
END
IF( @processCode = 0 )
BEGIN
-- Input @objectType and @parameterName validation
IF ( @objectType NOT IN ( 50, 20, 30 )
OR @parameterName NOT IN ( 'LOGGING_LEVEL', 'DUMP_ON_ERROR',
'DUMP_ON_EVENT',
'DUMP_EVENT_CODE',
'CALLER_INFO', 'SYNCHRONIZED' ) )
BEGIN
SET @validationMessage =
'Object type is not validated to system Object type'
SET @processCode = 1
END
END
IF( @processCode = 0 )
BEGIN
-- Input @parameterValue validation
IF ( @parameterValue NOT IN ( 0, 1, 2, 3 ) )
BEGIN
SET @validationMessage =
'Parameter Value is not validated to system Parameter Values'
SET @processCode = 1
END
END
IF( @processCode = 0 )
BEGIN
-- Get referenceId
SELECT @referenceId = ER.reference_id
FROM [SSISDB].[catalog].environment_references ER
JOIN [SSISDB].[catalog].[projects] P
ON ER.project_id = P.project_id
WHERE ER.environment_name = @environmentName
AND P.name = @projectName
-- Create Instance of package
EXEC [SSISDB].[catalog].[Create_execution]
@package_name=@packageName,
@execution_id=@executionId output,
@folder_name=@folderName,
@project_name=@projectName,
@use32bitruntime=false,
@reference_id=@referenceId
-- Assign parameters to the instance of package
EXEC [SSISDB].[catalog].[Set_execution_parameter_value]
@executionId,
@object_type=@objectType,
@parameter_name= @parameterName,
@parameter_value=@parameterValue
-- Execute the instance of package
EXEC [SSISDB].[catalog].[Start_execution]
@executionId
END
IF( @processCode = 1 )
BEGIN
-- Log validation check to custom SSISDBLog table
INSERT INTO [Control].[dbo].[ssisdblog]
SELECT @projectName,
@folderName,
@packageName,
@environmentName,
@objectType,
@parameterName,
@parameterValue,
@validationMessage,
Getdate()
END
/*
-- Create SSISDBLog table for Validation message log
-- Used the exsiting my [Control] DB, one time creation of table
-- USE [Control]
-- GO
-- CREATE TABLE [dbo].[SSISDBLog](
-- [ProjectName] [nvarchar](1000) NULL,
-- [FolderName] [nvarchar](1000) NULL,
-- [PackageName] [nvarchar](1000) NULL,
-- [EnvironmentName] [nvarchar](1000) NULL,
-- [ObjectType] [nvarchar](1000) NULL,
-- [ParameterName] [nvarchar](1000) NULL,
-- [ParameterValue] [nvarchar](1000) NULL,
-- [Message] [nvarchar](1000) NULL,
-- [DateLog] [datetime] NOT NULL
-- ) ON [PRIMARY]
-- GO
-- Unit Testing Query
-- Execute the Pacakge for testing in all inputs
-- EXEC [Control].[dbo].[Usp_ExecPackage]
-- 'Employee.dtsx' -- Input
-- ,'Database_Load' -- Input
-- ,'Database_Load' -- Input
-- , 50 -- Input
-- , 'LOGGING_LEVEL'-- Input
-- , 1 -- Input
-- , 'DEV'
-- Check for any Validation Errors
-- SELECT * FROM [Control].[dbo].[SSISDBLog]
-- Check for any package status from catalog info
-- SELECT project_name
-- , folder_name
-- , package_name
-- , reference_id
-- , reference_id
-- , environment_name
-- , executed_as_name
-- , CASE WHEN [status] = 1 THEN 'created'
-- WHEN [status] = 2 THEN 'running'
-- WHEN [status] = 3 THEN 'canceled'
-- WHEN [status] = 4 THEN 'failed'
-- WHEN [status] = 5 THEN 'pending'
-- WHEN [status] = 6 THEN 'ended unexpectedly'
-- WHEN [status] = 7 THEN 'succeeded'
-- WHEN [status] = 8 THEN 'stopping'
-- WHEN [status] = 9 THEN 'completed'
-- END AS [status_text]
-- , start_time
-- , end_time
-- , DATEDIFF(ss,start_time,end_time)DurationInSeconds
-- FROM [SSISDB].[catalog].[executions]
-- ORDER BY start_time DESC
*/
END
GO
Step 2: Testing with sample packages
Developed two sample SSIS packages with Project parameters as
Step 3: Deployed to SSISDB and created a 3 environments which reference to project
Step 4: Create a Log table at Control database (i.e. have used my Control DB for this demo)
-- Create SSISDBLog table for Validation message log -- Used my systems exsiting [Control] DB, one time creation of table USE [Control] GO CREATE TABLE [dbo].[SSISDBLog]( [ProjectName] [nvarchar](1000) NULL, [FolderName] [nvarchar](1000) NULL, [PackageName] [nvarchar](1000) NULL, [EnvironmentName] [nvarchar](1000) NULL, [ObjectType] [nvarchar](1000) NULL, [ParameterName] [nvarchar](1000) NULL, [ParameterValue] [nvarchar](1000) NULL, [Message] [nvarchar](1000) NULL, [DateLog] [datetime] NOT NULL ) ON [PRIMARY] GO
Step 5: Execute the stored procedure for testing and logging the validation issues as
Step 6: Execute the stored procedure for testing with correct input values and logging information from SSISDB catalog as
Points to consider:
1. Used only existing functionality of SSIS 2012 features.
2. Haven’t discussed regarding how we are passing the input values to stored procedure and thinking, it is a logical execution flow of dependencies.
3. My understanding, SSIS 2012 is amazing with package execution and catalog logging mechanism.
Categories: SSIS
SSIS2012 Exec package, SSISDB
Leave a comment
BI passion 
Hello I have a similar stored procedure but when I tried Exec I have problems because in the @parameter_value=@parameterValue Sql hope receive the @paramatereValue in format N’Value… someone idea for how to into correct this value?
Thanks!
When I tried exec the procedure with the parameters say me:
“The data type of the input value is not compatible with the data type of the ‘String’. ”
Thanks again!
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!!
I have created similar sp using procs of ssis catalog, I need wich one user with minimum rigths can run this store, someone idea?
Hi there to every one, it’s really a nice for me to visit this web site, it consists of precious Information.