06 April 2017

6095 views

8 0

Comparing SSIS Catalog Contents Using DBFit Framework

When you are doing the rapid deployment of an updated SSIS project, there are a number of things you have to check to make sure that the deployment will be successful. These will include such settings as the values in environment variables, Package parameters and project parameters. The DbFit test framework turns out to be ideal for the purpose of doing final checks as part of a deployment process, as Nat Sundar demonstrates.

Unfortunately you can’t easily compare SQL Server Integration Services (SSIS) projects with previous versions of the same projects. Because of this, it is very difficult to detect potential problems with a release before a deployment takes place.

If there were a way of comparing the SSIS Catalogs of the release candidate with those of the released version, Data Warehouse release managers could detect problems more easily before they could cause a failed release

SSIS deployments can fail in several common ways.

The values of parameters in the Production package are sometimes overwritten by those in User-Acceptance Testing (UAT)
Additional parameters can be deployed to Production unnecessarily
Packages have been accidentally omitted from the deployment to Production.
SSIS packages sometimes exist only in Production and not in other test environments because their release was uncontrolled.
Bug fixes are occasionally applied directly to production packages, and the relevant changes are not in other environments because they were not placed in Source Control
Poor use of the source control system results in different versions of the SSIS packages being deployed to different deployment environments

The SSIS Catalog consists of SSIS packages, Project Parameters, Package parameters and Environments. After a change has been released, you must validate the release before you run the SSIS packages. The reason for this is that most of the deployment issues are introduced while we are setting up values for Package, project parameters and Environment variables, and any mistakes will show up when you compare the SSIS Catalog with another instance.

In addition, the SSIS Catalog comparison will be helpful after SSIS Catalog migration.

Traditional approach for validation:

Usually, the release manager has the unenviable task of checking through everything ‘by eye’ to understand the differences. This is a tedious and tiresome job when the release consists of hundreds of SSIS projects. Any mistake in the process could introduce an error that causes the SSIS package to fail in the production servers. We have to be able to use a more robust and automated procedure for this validation checks.

Solution Overview:

In this article, I will be demonstrating an approach to automate the SSIS Catalog comparison process with the help of the DbFit framework.

I will be comparing the properties of

Environment Variables
Package parameters and values
Project parameters and values
Package parameter value mapping with Environment variables

I am hoping that this article will be helpful for Developers, Release managers, Dev-ops consultants and architects whenever they need to compare two SSIS Catalogues and have the differences displayed on-screen.

Pre-requisite:

In this article, we’ll have to assume that that you have already gone through the previous article about “Using the DbFit Framework for Data Warehouse Regression Testing” here. This article provides all the necessary information to set up DbFit, and run a test case for SQL server instance.

Solution Details:

SSIS provides Catalog T-SQL views to manage the deployed SSIS projects in a SQL Server. These views provide very detailed information about the properties of the SSIS Catalog. The full list of SSIS Catalog views can be found here.

$C:\WorkArea\Nat\SQL\Blog\SimpleTalk\SSIS Compare\Image\SolutionOverview.png$

This solution will use the Catalog views to get the SSIS project details from two instances of SQL server and compare them in the DbFit framework.

Why DbFit?

We selected DbFit as a framework to compare data and display the results. It has a servicable GUI that allows you to understand differences between data and missing data. Additionally DbFit is an open source framework and can be easily integrated with a continuous integration tool. Also DbFit can be run from command line. Hence DbFit seems a good choice for displaying, and reporting, the comparison results.

To simplify the demonstration of how it all fits together, I have created two deployed SSIS projects for the purpose of comparison. These projects are available in a single SQL Server instance. These projects are in the folders namely ‘Integration Services Project1’ and ‘Integration Services Project2’ and they have their own ‘Dev’ environment as well.

$C:\WorkArea\Nat\SQL\Blog\SimpleTalk\SSIS Compare\Image\SSIS_Catalog_Overview.png$

I have developed the SQL stored procedures and the DbFit test cases to compare these projects. In addition, I’ve created several test cases to compare the projects, environment variables, project parameters, package parameters and packages. This framework can also be extended to compare multiple SSIS Catalogs.

The test cases compare the two SSIS projects

Between multiple SSIS Catalog environments.
Their lists of packages.
Their Package Parameters.
Their Project Parameter.
Environment variables that reference their Package Parameters.

Now let’s have a look at each test case in detail. In each case, I have defined the usage scenario and provided a test case to identify and rectify the issue.

1. Compare between multiple SSIS Catalog environments

SSIS Catalog Environments are collections of variables and their values. These environments can be created at the server level, folder level and the project level. An environment can hold server-wide variables or business-specific details. The SSIS package can use the current value of an environment variable at the time of execution.

Usually the environment variables will be used to provide the connection string details, so any difference in the environment variable value, or any missing environment variable, could easily cause serious problems to the deployment of SSIS packages. Any potential issues will emerge when you compare the source and target server environment variables.

In this example, I’ve created an environment in the server called ‘Dev’ . It has two variables named ‘ServerName’ and ‘SourceFileLocation’ as shown below.

$C:\WorkArea\Nat\SQL\Blog\SimpleTalk\SSIS Compare\Image\SSIS_Dev_Environment_variables.png$

Stored procedure to list the environment variables details:

This stored procedure lists all the details of the environment variables. This stored procedure will make use of the SSIS Catalog views such as catalog.environments, catalog.folders, catalog.projects and catalog.environment_references.

This is the build script for the procedure.

CREATE procedure [dbo].[GetSSISEnvironmentDetails] (@FolderName varchar(50))

Begin

SELECT

Convert(varchar(255),E.name) AS EnvironmentName

, Convert(varchar(255),F.name) AS FolderName

, Convert(varchar(255),P.name) AS ProjectName

, Convert(varchar(255),EV.name) as EnvironmentVariableName

, Convert(varchar(255),EV.Description) as EnvironmentVariableDescription

, Convert(varchar(255),EV.type) as EnvironmentVariableType

, Convert(varchar(255),EV.sensitive) as EnvironmentVariableSensitive

, Convert(varchar(255),EV.Value) as EnvironmentVariableValue

FROM

SSISDB.catalog.environments AS E

INNER JOIN

SSISDB.catalog.folders AS F

ON F.folder_id = E.folder_id

INNER JOIN

SSISDB.catalog.projects AS P

ON P.folder_id = F.folder_id

INNER JOIN

SSISDB.catalog.environment_references AS ER

ON ER.project_id = P.project_id

INNER JOIN SSISDB.[catalog].[environment_variables] EV

on E.environment_id = EV.environment_id

Where F.name = @FolderName

ORDER BY

ER.reference_id;

End

This procedure accept the name of a folder as a parameter and return the following details of environment variables

Environment Name
Project Name
Environment Variable Name
Variable Description
Variable Type
Variable Value

To do the comparison, the stored procedure must exist in both the source and target server. A test case has been prepared in DbFit to execute this stored procedure on both the source and target SQL servers. In addition, this test case will compare the results and will list discrepancies.

Test Case Content:

!path lib/*.jar

!|dbfit.SqlServerTest|

!|Connect|jdbc:sqlserver://localhost\sql2012;integratedSecurity=true|

!|Store Query|Execute Staging.dbo.GetSSISEnvironmentDetails 'Integration Services Project1'|query1|

!|Store Query|Execute Staging.dbo.GetSSISEnvironmentDetails 'Integration Services Project2'|query2|

!|Compare Stored Queries Hide Matching Rows|query1|query2|

Here is the DbFit screen that shows the creation of the test case to compare the environment variables in DbFit.

$C:\WorkArea\Nat\SQL\Blog\SimpleTalk\SSIS Compare\Image\SSISEnvironmentCompareTestCaseEdit.png$

In the screenshot below, the test case has been executed successfully and there are no discrepancies.

$C:\WorkArea\Nat\SQL\Blog\SimpleTalk\SSIS Compare\Image\SSISEnvironmentCompareTestCaseSuccessfulExecution.png$

When a test case fails because the value of environment variable is different between source and target:

To illustrate what happens when a change is detected, I have modified the value of the environment variable ‘ServerName’ to ‘localhost\SQL2014’ in SSMS, as shown in the image below.

$C:\WorkArea\Nat\SQL\Blog\SimpleTalk\SSIS Compare\Image\SSIS_Target_Variable_Changed.png$

When we re-run the same test case. DbFit has highlighted that the actual value in the target environment is different from the source project as you’ll see below.

$C:\WorkArea\Nat\SQL\Blog\SimpleTalk\SSIS Compare\Image\SSISCompareEnvironmentChangeOfValueIdentified.png$

When a test case fails because the value of environment variable is missing in the target:

To demonstrate when a variable goes missing, I have removed a variable called ‘SourceFileLocation’ in the target environment as shown below.

$C:\WorkArea\Nat\SQL\Blog\SimpleTalk\SSIS Compare\Image\VariableRemovedInTargetEnvironment.png$

If we now rerun the test case, we can see that it has correctly identified the missing variable as shown below.

$C:\WorkArea\Nat\SQL\Blog\SimpleTalk\SSIS Compare\Image\MissingTargetEnvironmentVariableTestCaseFailure.png$

So we can now be more confident that the test case is able to compare source and target environment and return the results

2. Identifying missing SSIS packages in the target servers.

It is easy to miss the fact that an SSIS package is missing from the deployment., and this package could be the master package to start other packages. It is therefore essential to do a preventative check.

We can do this easily by comparing the list of packages available in a SSIS folder in both source and target with the help of Catalog views.

Here is a stored procedure to list all the SSIS package details in a given folder. This stored procedure will make use of the Catalog views [catalog].[projects] , catalog.folders and catalog.[packages] to return all the SSIS packages in a SSIS catalog.

Once again, we will need to install this stored procedure in both the server environments.

This is the SQL script to create a stored procedure to identify missing SSIS packages:

CREATE procedure [dbo].[GetSSISPackageDetails] (@FolderName varchar(50))

Begin

Select Convert(varchar(255),FOl.name) as FolderName,

Convert(varchar(255),PRJ.name) as ProjectName,

Convert(varchar(255),PRJ.description) as ProjectDescription,

Convert(varchar(255),PKG.name) as PackageName

from SSISDB.[catalog].[projects] PRJ

INNER JOIN SSISDB.catalog.folders FOL

ON PRJ.folder_id = FOl.folder_id

INNER JOIN SSISDB.catalog.[packages] PKG

ON PRJ.project_id = PKG.project_id

Where FOl.name = @FolderName

End

This procedure requires the folder name as a parameter and will return the following details about the package

Project Name
Project Description
Package Name

For the purpose of demonstration, a new package called ‘Staging’ has been added to the source project which is not available in the target folder.

$C:\WorkArea\Nat\SQL\Blog\SimpleTalk\SSIS Compare\Image\AdditionalPackageForSourceWhichIsNotInTarget.png$

Now that a test case has been prepared in the DbFit to execute this stored procedure on both the source and target servers. This test case will list the discrepancies as well.

Test case content:

!path lib/*.jar

!|dbfit.SqlServerTest|

!|Connect|jdbc:sqlserver://localhost\sql2012;integratedSecurity=true|

!|Store Query|Execute Staging.dbo.GetSSISPackageDetails 'Integration Services Project1'|query1|

!|Store Query|Execute Staging.dbo.GetSSISPackageDetails 'Integration Services Project2'|query2|

!|Compare Stored Queries Hide Matching Rows|query1|query2|

|PackageName?|

The below mentioned image represents that the test case has been executed and the missing package has been identified by the DbFit test case.

$C:\WorkArea\Nat\SQL\Blog\SimpleTalk\SSIS Compare\Image\MissingPackageInTheTargetHasBeenIdentified.png$

So, with this result, we can be sure that the test case will compare and list any missing SSIS packages.

3. Comparing Package Parameters

In SSIS, Package parameters hold literal values and are scoped within the package. The value of a package parameter can be assigned while executing a package,or the value can be set from the environment variable.

An incorrect value of a Package parameter could result a package failure.

The package parameters and their current values for the source package are shown below.

$C:\WorkArea\Nat\SQL\Blog\SimpleTalk\SSIS Compare\Image\SourcePackageParameters.png$

In the target package, the value of the parameter ‘Sourcefilelocation’ has been deliberately changed to a different value to that of the source package.

$C:\WorkArea\Nat\SQL\Blog\SimpleTalk\SSIS Compare\Image\TargetpackageParameters.png$

In this example, we will be comparing two SSIS package parameters with the help of SSIS catalog views.

I have developed a stored procedure to list all SSIS package parameter details for a given folder. This stored procedure will make use of the catalog views [catalog].[object_parameters] and SSISDB.[catalog].[projects] to return all the SSIS package parameters in a SSIS catalog.

SQL script to create a stored proc to list the SSIS package parameter details:

CREATE procedure [dbo].[GetPackageParameterDetails] (@ProjectName varchar(50))

Begin

Select

Convert(varchar(255),object_name) as PackageName,

Convert(varchar(255),parameter_name) ParameterName,

Convert(varchar(255),data_type) as ParameterDataType,

Convert(varchar(255),default_value) as ParameterValue

from SSISDB.[catalog].[object_parameters] PARA

INNER JOIN SSISDB.[catalog].[projects] PRJ

on PARA.project_id = PRJ.project_id

where

PRJ.name = @ProjectName

and object_type=30 -- Package Parameter

End

This requires the project name as a parameter and will return the following details about the package parameter

The Package Name
The Parameter Name
The Parameter Data Type
The Parameter Value

Once again, you will need this stored procedure in both servers

Here is the test case that I have created in DbFit.

!path lib/*.jar

!|dbfit.SqlServerTest|

!|Connect|jdbc:sqlserver://localhost\sql2012;integratedSecurity=true|

!|Store Query|Execute Staging.dbo.GetPackageParameterDetails 'Integration Services Project1'|query1|

!|Store Query|Execute Staging.dbo.GetPackageParameterDetails 'Integration Services Project2'|query2|

!|Compare Stored Queries Hide Matching Rows|query1|query2|

In the image below, the test case has been successfully executed and shows a difference between the actual and expected results..

$C:\WorkArea\Nat\SQL\Blog\SimpleTalk\SSIS Compare\Image\PackageParameterComparisonTestCase.png$

4. Project Parameter comparison

Project parameters contain literal values and they have project scope. As the scope is wide, an incorrect value of a project parameter may introduce issues across multiple packages in a project. So it is important to compare and identify the missing project parameters in the target server.

As with the previous test cases, the project parameters can be compared with the help of SSIS catalog views.

To do this, I’ve created a stored procedure to list all SSIS project parameter details in a given folder. This stored procedure will make use of the catalog views [catalog].[object_parameters] and SSISDB.[catalog].[projects] will return all the SSIS project parameters in a SSIS catalog.

This is the SQL script to create a stored proc to compare SSIS project parameters. It must, of course, have already been deployed to both the environments.

CREATE procedure [dbo].[GetProjectParameterDetails] (@ProjectName varchar(50))

Begin

Select

Convert(varchar(255),object_name) as ProjectName,

Convert(varchar(255),parameter_name) ParameterName,

Convert(varchar(255),data_type) as ParameterDataType,

Convert(varchar(255),default_value) as ParameterValue

from SSISDB.[catalog].[object_parameters] PARA

INNER JOIN SSISDB.[catalog].[projects] PRJ

on PARA.project_id = PRJ.project_id

where

PRJ.name = @ProjectName

and object_type=20 -- Project Parameter

End

To call this procedure, you must pass the name of the project as a parameter, and it will return the following details about the package

The project name
The parameter name
The parameter data type
The parameter value

Her in SSMS is the configuration of the project parameter for the source project.

$C:\WorkArea\Nat\SQL\Blog\SimpleTalk\SSIS Compare\Image\SourceProjectParameters.png$

I will now update the target project parameter with an incorrect value as shown below.

$C:\WorkArea\Nat\SQL\Blog\SimpleTalk\SSIS Compare\Image\TargetProjectParameters.png$

Now, the source and target project parameters can be compared using a DbFit test case.

Here is a test case in DbFit to execute this stored procedure on both the source and target servers. In addition this test case will list the discrepancies as well.

Test case content:

!path lib/*.jar

!|dbfit.SqlServerTest|

!|Connect|jdbc:sqlserver://localhost\sql2012;integratedSecurity=true|

!|Store Query|Execute Staging.dbo.GetProjectParameterDetails 'Integration Services Project1'|query1|

!|Store Query|Execute Staging.dbo.GetProjectParameterDetails 'Integration Services Project2'|query2|

!|Compare Stored Queries Hide Matching Rows|query1|query2|

|ParameterName?|ParameterDataType?|ParameterValue?|

The difference will be highlighted by DbFit

$C:\WorkArea\Nat\SQL\Blog\SimpleTalk\SSIS Compare\Image\ProjectParameterComparison.png$

This test case will, it seems, compare and list any discrepancies in the project parameters..

5. Package Parameters referenced from environment variable

Finally, we must tackle Package Parameters that have a value derived from an environment variable.

Let us assume that, in the source environment, the package parameter has been correctly mapped with an environment variable. If this mapping has not been set up correctly during deployment, this will lead the package to execute with an incorrect value. We can create a stored procedure to compare the mapping of the parameter in the source package with the target package.

In our example, the source package parameters have been mapped to an environment variable as shown below.

$C:\WorkArea\Nat\SQL\Blog\SimpleTalk\SSIS Compare\Image\SourcePackageParametersWithEnvironmentVariableMapping.png$

The target package is still using the package parameter.

Let us assume for the purpose of our demonstration that, due to a deployment issue, the target package has not been mapped with the environment variable. Hence the target package is still using the literal value.

The list of packages available in a SSIS folder can be easily compared with the help of Catalog views with a stored procedure like the one below that lists the properties of all SSIS package parameters details for a given folder.

This stored procedure uses catalog views [catalog].[object_parameters] and [catalog].[projects] to return all the properties of the parameters of SSIS packages in a SSIS catalog.

We must, of course, have deployed the stored procedures to both the environments.

Here is a SQL script to create a stored procedure to list all the properties of the parameters of the SSIS packages:

Create procedure [dbo].[GetPackageParameterReferenceByEnvironmentDetails] (@ProjectName varchar(50))

Begin

Select

Convert(varchar(255),object_name) as PackageName,

Convert(varchar(255),parameter_name) ParameterName,

Case

When PARA.value_type='V'

then 'Direct Parameter'

When PARA.value_type='R'

then 'Parameter Referenced from Environment'

else 'NA'

End as 'ParameterType',

Convert(varchar(255),referenced_variable_name) as EnvironmentVariableName,

Convert(varchar(255),data_type) as ParameterDataType,

Convert(varchar(255),default_value) as ParameterValue

from SSISDB.[catalog].[object_parameters] PARA

INNER JOIN SSISDB.[catalog].[projects] PRJ

on PARA.project_id = PRJ.project_id

where

PRJ.name = @ProjectName

and object_type=30 -- Package Parameter

End

The above mentioned procedure will accept project name as a parameter and will return the following details about the package parameter

Package Name
Parameter Name
Mapped environment Variable Name
Parameter data type
Parameter value

Now a test case has been prepared in the DbFit to execute this stored procedure on both the source and target servers. In addition this test case will list the discrepancies as well.

Test case content:

!path lib/*.jar

!|dbfit.SqlServerTest|

!|Connect|jdbc:sqlserver://localhost\sql2012;integratedSecurity=true|

!|Store Query|Execute Staging.dbo.GetPackageParameterReferenceByEnvironmentDetails 'Integration Services Project1'|query1|

!|Store Query|Execute Staging.dbo.GetPackageParameterReferenceByEnvironmentDetails 'Integration Services Project2'|query2|

!|Compare Stored Queries Hide Matching Rows|query1|query2|

The below mentioned image confirms the fact that the DbFit was able to find the difference between the source and the target

$C:\WorkArea\Nat\SQL\Blog\SimpleTalk\SSIS Compare\Image\PackageParameterReferencedByEnvironment.png$

Known Limitations:

The SSIS Catalog view “[catalog].[packages]” will provide the details about the Package Name, Description, Project Id and version details. However there is no facility available in the SSIS Catalog to view package data XML. This limits the ability to compare two packages using this method.

However the DbFit framework can be extended to download the SSIS project and the package XML can be extracted for comparison purposes.

Conclusion:

The DbFit test cases and the suite can be executed from the command line. Hence the above mentioned methodology can be easily integrated with a Continuous Integration tool for a post deployment validation.

Reference Links:

Comparing SSIS Catalog Contents Using DBFit Framework

Traditional approach for validation:

Solution Overview:

Pre-requisite:

Solution Details:

Why DbFit?

1. Compare between multiple SSIS Catalog environments

Stored procedure to list the environment variables details:

When a test case fails because the value of environment variable is different between source and target:

When a test case fails because the value of environment variable is missing in the target:

2. Identifying missing SSIS packages in the target servers.

3. Comparing Package Parameters

4. Project Parameter comparison

5. Package Parameters referenced from environment variable

Known Limitations:

Conclusion:

Reference Links:

Subscribe for more articles

Rate this article

Nat Sundar

Related articles

Fabric and Azure SQL in a few clicks: Mirroring a Database