Simple Talk is now part of the Redgate Community hub - find out why

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

  1. Between multiple SSIS Catalog environments.
  2. Their lists of packages.
  3. Their Package Parameters.
  4. Their Project Parameter.
  5. 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.

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:

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:

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:

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:

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.

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.

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:

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:

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:

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:

How you log in to Simple Talk has changed

We now use Redgate ID (RGID). If you already have an RGID, we’ll try to match it to your account. If not, we’ll create one for you and connect it.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue

Simple Talk now uses Redgate ID

If you already have a Redgate ID (RGID), sign in using your existing RGID credentials. If not, you can create one on the next screen.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue