Archive

Posts Tagged ‘SSIS2012 Exec package’

SSIS – Generic StoredProcedure to execute SSIS 2012 packages

November 19, 2012 5 comments

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.

Advertisements
Categories: SSIS Tags: ,