Based on Part 1 of Upgrading your SSIS Management Framework, you’ve decided to go with a hybrid approach for your framework. The hybrid approach which will use some components of the custom framework (this post will use the framework provided in SSIS PDS, but the concepts are applicable to any custom framework) and also utilize the standard SSIS framework. This allows you to tie your existing package ecosystem with the latest and greatest built-in framework. Let’s talk through an overview of what we’re going to do and then explain each of the steps needed to implement it.
When it comes down to it, we need to accomplish two main things for this hybrid approach: tie our logging tables together and tie our configuration tables together. When it comes to logging, each system has its own important identifier (ID) that can get you to anything else in the system. The important ID in the custom SSIS framework is the PackageLogID, and the important ID in the standard SSIS framework is the ServerExecutionID. The work you need to do is to map these two executions together. When it comes to configurations, you want to be able to have one place to modify your connection strings and common variables that will modify all of your packages.
Let’s start with logging. As previously mentioned, our goal here is to the two logging systems together. Since we don’t want to modify the standard SSIS framework (that would defeat the purpose of moving to that framework!), we’ll do our modifications in the existing framework. However, we want to be sure not to change anything in the packages themselves because that would cause a lot of rework. Fortunately, we can do this in the table and stored procedure that the framework utilizes.
Begin by adding a new column to your main table that contains package executions, such as:
IF NOT EXISTS(SELECT * FROM sys.columns WHERE [name] = N'ServerExecutionID' AND [object_id] = OBJECT_ID(N'PackageLog'))
Next, you will modify the stored procedure to populate the field you just added:
UPDATE dbo.PackageLogSET ServerExecutionId = (and package_name = @PackageName )WHERE @PackageLogID = @PackageLogID
This will insert the ServerExecutionID from the standard framework into the custom framework. Next, you can modify your reporting queries to utilize the new column. For example, I modified the existing standard framework query for the “All Executions” report to include information from the old framework in the following query:
a.[execution_id],CAST(a.[start_time] AS smalldatetime) AS shortStartTime,CONVERT(FLOAT, DATEDIFF(millisecond, a.[start_time], ISNULL(a.[end_time], SYSDATETIMEOFFSET())))/1000 AS durationa.[package_name] = b.[package_name] ANDa.[project_name] = b.[project_name] ANDa.[folder_name] = b.[folder_name]a.[status] = 7SELECT a.PackageLogID,CAST(a.[StartDateTime] AS smalldatetime) AS shortStartTime,CONVERT(FLOAT, DATEDIFF(millisecond, a.[StartDateTime], ISNULL(a.EndDateTime, SYSDATETIMEOFFSET())))/1000 AS durationFROM SSIS_PDS.dbo.PackageLog aWHERE d.ServerExecutionID = ?
In this way, you can see all executions together, and you can use similar queries to tie any executions from the old framework to the new one!
Next week, we’ll look at configurations and how to manage those in both frameworks.