Building an SSIS Test in SentryOne Test

In Designing a Test, we walked through the steps to design a test around an SSIS package. In this article, we build a SentryOne Test, allowing us to test the execution of an SSIS Package. 

Building an SSIS Test in SentryOne Test

Add a project by going to the Visual Studio menu and then selecting File >  New > Project.

In the Add New Project dialog, select on Visual C#, scroll down and select LegiTest Project – MSTest. Give it a name and select OK to create the project.

Implementing the design goes in reverse order of the design process.

1. Create the assets.
2. Define the actions.
3. Create the asserts.

Follow the steps in the sections below to build a test:

1. Connections

Select inside the Assets area. The SentryOne Test Toolbox updates with the items appropriate to the assets area. In the toolbox, select the Connection tool, then drag and drop it into the assets area.  

Select on the ADO.NET Connection block to display the SentryOne Test Element Editor.

In the provider drop down, select the SqlClient Data Provider.  

Select the Configure to display the Connection Properties window.

Select the server and the SentryOne Test Source database, and then select Test Connection to verify the connection is successful.  Also be sure to set the user ID correctly, for a simple test to localhost the Windows Authentication option will likely work fine. Select OK to finalize the connection properties.

Rename the asset to SentryOne Test Source, by selecting into the text on the connection block asset. When done it should look something like the image below.

Now repeat the above steps, creating a connection to the SentryOne Test Destination database. You can name it SentryOne Test Destination. As an alternative to dragging and dropping from the toolbox, try right clicking in the asset area, and then selecting Data Management > ADO.NET Connection from the context menu.

2. Queries

To meet our requirements four queries are needed. From the SentryOne Test toolbox, drop a Query tool into the assets area. Paste the following query in the text box:

SELECT COUNT(*) AS MySitesCount
 FROM [dbo].[MySites]

Name the query asset MySitesCount.

Now repeat the process, creating another query asset named DimMySitesCount and using the following query:

SELECT COUNT(*) AS DimMySitesCount
 FROM [dbo].[DimMySites]

To do the data compare we need queries to bring back the rows and columns from the source and target. Add a query MySitesData, with this query:

SELECT [PrimaryKey], [Name], [URL]
 FROM [dbo].[MySites]
ORDER BY [PrimaryKey]

And then another query, DimMySitesData, with this query:

SELECT [PrimaryKey], [Name], [URL]
 FROM [dbo].[DimMySites]
ORDER BY [PrimaryKey]

But wait, you say, wasn’t there a requirement to check the LastUpdate to ensure the row hadn’t changed? Good catch. However, instead of handling it as part of the data compare, we use a scalar comparison to get the value prior to and after the package executes, and use those to compare. For that we need to add another query asset DimMySites LastUpdate. For the query use:

SELECT [LastUpdate]
 FROM [dbo].[DimMySites]
WHERE [Name] = 'SentryOne'

We’re almost done. Even though it wasn’t mentioned as a requirement, one thing every test needs is a way to reset the target before each run. This can be accomplished in a variety of ways. For example, SentryOne Test has a backup / restore asset that allows you to restore the destination database from a baseline database previously backed up. You could also use the Execute Process task to deploy a dacpac to the target. 

In this case, we add a query to truncate the target table, then insert the needed rows. Create a query DimMySitesReset. Insert the following code:

TRUNCATE TABLE [dbo].[DimMySites];INSERT INTO [dbo].[DimMySites]
 ([PrimaryKey], [Name], [URL])
VALUES
  (1, 'SentryOne', 'http://sentryone.com')
  (3, 'SQLskills', 'http://sqlskills.com');

That's the last of the query assets we need to create. However, there are two more assets we must add before we can create our actions. 

3. Comparison Manifest

For SentryOne Test to be able to compare data sets, or grids (as the toolbox calls them), it needs to know how each column from the first data set (referred to as the Left Side) compares to the columns on the Right Side. 

Insert a Comparison Manifest tool into the assets area. Name it Compare MySites to DimMySites. In the SentryOne Test Element Editor, you see the first step of the Comparison Manifest wizard appear, titled Gather Left Column(s). Provide a connection to the Left Column by selecting Copy From Asset, and then selecting the SentryOne Test Source connection in the dialog that appears. 

Next the Comparison Manifest needs to know the query that gets the data for the left side. Select Copy From Asset next to the Query, and then select the MySitesData (Query) in the dialog that appears. After completing these steps  your editor should look like the image below.

Select Gather to proceed to the next step. Now we repeat the steps for the right side. For the Connection, copy from the asset SentryOne Test Destination, and for the query copy from DimMySitesData. When you are done select Gather. 

In the final page of the wizard we need to match up the columns. SentryOne Test automatically matches when the names are the same, which is a great reason to use aliasing in the queries if you can. In this demo, it wasn’t needed as the column names all matched up. 

If you needed to match up the columns, you could select on a column name, select edit to the right, and then change the mapping. You can also add mappings if SentryOne Test didn’t detect it, or delete ones you don’t want to compare. 

There is one thing we can do to help speed things along. Note the Key Columns area at the top is empty. We should move the PrimaryKey column up to this area to speed up the compare. 

First, select Add button at the top, next to the Key Columns. In the page that appears, select the PrimaryKey for both the left and right column name and then select OK. 

Next, in the Comparison Columns area, select on the PrimaryKey and then select the Delete. Since we’ve indicated it's the primary key, it isn’t going to change and doesn’t need to be compared. Your Comparison Manifest should now look like:

4. Package Reference (SSIS 2012)

The final item to add is a reference to the package we are testing. Select in the Assets area, and then add a Package Reference (SSIS 2012) asset from the SentryOne Test toolbox. In the SentryOne Test Element Editor, there are three locations to load the package from: 

• File 
• SQL 
• Catalog 

For this example the we select the File option, however you may opt to run from the catalog to make testing from multiple machines easier. 

Make sure File is selected, and then select Browse. Navigate to the folder with your package and select it. 

Finally, give the asset a good name. In this case, name it after the package, MyFirstSSISLegiTest. Now that we’ve finished creating the assets, take a second to save your work, then it’s time to start the actions.

5. Actions

Now that all the assets are set up, it’s time to create the actions. Actions are arranged at multiple levels. To fully understand how SentryOne Test organizes tests, see Designing a Test.

Group Level Actions

With the assets set up, move to the arrangement area in the designer, and then select on Test Group 1. Rename it to DimMySites Package. Now in the Group Initialization Steps area, we need to perform actions that are needed prior to testing. 

Referring back to our requirements, there were three actions that needed to be performed prior to doing any testing. The first action was reset the target table to a known state. To do so we execute the DimMySites Reset query. In the SentryOne Test Toolbox, drag an Execute Query (Command) action into the Group Initialization Steps area. Select it to open the Properties window. 

Select the SentryOne Test Destination Connection Asset from the drop-down box, and then select DimMySites Reset Query from the Query Asset drop-down box. Finally, rename the action to Exec DimMySites Reset.  

Now that the target table has been reset, we need to get a key piece of information from the table. For the row that isn't supposed to change, we get the LastUpdate column before the package executes. Later, we compare this to the LastUpdate column after the package runs to ensure there was no change.

Into the Group Initialization Steps, place an Execute Query (Scalar) action. For the Connection select the SentryOne Test Destination, for the Query select DimMySites LastUpdate. 

The only other option is Resource Key. Most actions in SentryOne Test have a Resource Key. Think of the Resource Key much like a variable. When an action has some output, the output is stored in the Resource Key, where it can be later accessed by other actions. For this reason, you should be sure to give all Resource Keys meaningful, clear names. 

In this case, the LastUpdate value retrieved by the query is stored in the Resource key we provide. As you can see from the image above, we name this DimMySitesLastUpdatePreRun. As the last step rename the action to Exec DimMySites LastUpdate Pre Run.

The third action in our list of things to do is load the package into memory. From the SentryOne Test toolbox, drag the Load Package action to the Group Initialization Steps. In the Properties window, select the Package Reference Asset MyFirstSSISSentryOneTest asset from the dialog. 

At last we are at the final action needed for our tests, and that is to run the package. From the SentryOne Test toolbox drop an Execute Package action into the Group Initialization Steps area. In the Properties window, select the MyFirstSSISLegiTestPackage (Package) from the dialog. This came from the Load Package assets Resource Key name in the previous step. Finally, rename the asset itself Exec MyFirstSSISSentryOneTest.

The Group Initialization Steps track should now look like:

6. The Tests – Actions and Asserts

Now that the group level actions are done, it's time to build each test. In each test, we first build the actions to get back any further data needed to do our asserts, then we build the asserts themselves. For this example we have five tests to perform. 

Test One – Row Counts

The initial requirements for test one is to test to ensure that the source and target tables have the same number of rows after the package executes.

Select Test 1 in the arrangement pane. Rename the test to  DimMySites_RowCounts by double clicking Test 1 and entering the name.

For an especially large project you may wish to extend your naming scheme even further, for example Project_Group_TestName. 

To fulfill this requirement, we need to match the rows in the source to the rows in the target. We must execute the two row count queries stored in the assets area, then compare the results. 

Begin by dropping an Execute Query (Scalar) action into the Execution Tracks area. These actions are used to run queries that return a single value, such as a SELECT COUNT or executing a stored procedure which returns a single value. 

In the Properties window, for the Connection Asset select the SentryOne Test Source. For the Query Asset, select the MySites Count. Next, give this action a good Resource Key, such as MySitesCount. As a last step give this a good name, such as Exec MySites Count. 

The last row, Timeout, represents the number of seconds the query runs before SentryOne Test times it out. For most queries 30 seconds is sufficient, but if you have a long running query you may need to increase the time. 

If you note the Execution Tracks header, you’ll see (1/1) after the track name. (Pointed at by the red arrow in the image below).

Unlike other areas, the main execution track of a test supports parallel processing. SentryOne Test understands that you may need to run long running queries as part of a test and it has the ability to run these in parallel. 

Even though the sample queries in this project run extremely fast, and don’t really need to be parallelized, we set up this one test just as an example. 

Select the + Add to add an execution track. The counter now shows (1/2), and the Previous / Next buttons become active. Select Next to advance to the second execution track. In it, place another Execute Query (Scalar) action. 

In the Properties window, for the Connection Asset select the SentryOne Test Destination. For the Query Asset, select the DimMySites Count. Next, give this action a good Resource Key, such as DimMySitesCount. Finally, following our naming pattern, name this action Exec DimMySites Count.

Next, it's time to see if our test passes or not. Select on the Assertions track. For this we are comparing row counts, which are integers, so we can use the Value Comparison assertion. 

In the SentryOne Test Element Editor, note first the Comparable Value Source. By default, this compares the key in the Resource Key field to a static value in the Comparison Value field. In this case we want to compare one resource key to another, so for the Comparable Value Source select in it, and change it to Resource. 

In the Comparable Resource Key field, select the MySitesCount (Scalar Value) resource. Then in the Resource Key area, pick the DimMySitesCount (Scalar Value). This now compares the values held in the resource keys. The Comparison Mode indicates they type of comparison, by default it is set to Equal, which is what is needed for this test. However it supports the standard comparisons like greater than, less than, etc.

The Comparison Value doesn’t apply when doing a Comparable Value Source of Resource. If this was still set to Static Value, what is in here would be compared to the key Resource Key (and by inference in Static Value mode the Comparable Resource Key field is ignored). 

Then, Tolerance is used to set a +/- value, this allows for either a set value or percentage; essentially how close can the numbers be and still pass the test. As a last step, rename the assertion to Compare Row Counts. 

Now that the first test is created, we need to generate the C# code that actually becomes our test. Select the Save or Save All button to generate the code. With that completed, we are ready to test our test.

After saving take a look at the solution explorer. Expanding the various branches you will see quite a few files have been added to the project.

Don’t worry, it isn’t necessary for you to understand any of these, just know that SentryOne Test generated this content. If you did want to look, the DimMySitesPackage.Generated.cs  would be the prime file to review.

Now we can run the test. Select Run All Tests above the group organization area to run the test.

Visual Studio builds the code, then executes the test. In a moment it opens up a new Test Explorer tab . If the window is already open, you likely need to switch to it manually.

Test Two – Data Compare

It's now time to add the second test; comparing data between the source and target. In the test organizer, select the Add Test button. Rename this new test DimMySites_DataCompare. After it's created, select on it to be sure it is the currently selected test. 

Place an Execute Query (Grid) action in the Execution Tracks for this test . This action gets a dataset from a query and places the entire dataset into memory. Select the SentryOne Test Source as the Connection Asset, and the MySites Data for the Query Asset. Finally, give this a good Resource Key, in this example MySitesData. Finally give the action itself a good name, Exec MySites Data.

We now repeat the above steps, only this time for the destination data. Rather than creating a second execution track as we did in the previous example, add this query to the existing execution track. For the Connection Asset, use SentryOne Test Destination. In the Query Asset, select DimMySites Data, and finally use DimMySitesData for the Resource Key. Name the new action Exec DimMySitesData.

In the Assert track, place a Grid Comparison assert. Select in the Left Grid row inside the SentryOne Test Element Editor. Select the ellipsis … button and then select the MySitesData (Grid) option. 

Next, select in the Comparison Manifest Action row, and select the Compare MySites to DimMySites (Comparison Manifest) option. Finally, in the Right Grid, select the DimMySitesData (Grid). 

Save your work, and Run All Tests again. Now it executes both tests, and if all went well, both should be green. 

Test Three – Ensure the Row Did Not Change

In our third test, we want to validate the logic of our package such that if a row does not need to be updated, it isn’t. Our method for testing this is to get the LastUpdate date-time for that row prior to the package execution, then again after it executes. This should be the same value both before and after. 

In the Group Initialization section, we already obtained the value before the package executed. Now we fetch it afterwards. Start by adding a new test, and naming it DimMySites_UnchangedRows_LastUpdate. 

Place an Execute Query (Scalar) action in the Execution Track . Use SentryOne Test Destination for the connection, and DimMySites LastUpdate for the query. Name the Resource Key DimMySitesLastUpdatePostRun. Finally, rename it to Exec DimMySites LastUpdate Post Run.

For the Assert place a Scalar Comparison Assert into the Assertions track. Use the Exec DimMySites LastUpdate Pre Run and Exec DimMySites LastUpdate Post Run resource keys for the comparable and resource key values, respectively. Be sure to change the Comparable Value Source to Resource. 

Finally, we want to change the Comparison Type to the appropriate data type. In this case they are both date time values, so update this to DateTime. Rename the assert to Compare DimMySites Last Update, save and again run all tests. The Test Explorer should now report this one as passed as well. 

Test Four – Validate the Unchanged Rows Variable

For this test we validate our row did not change by getting the value of the UnchangedRows variable we setup in the package.  Add a new test and name it DimMySites_UnchangedRows_Variable. 

Instead of a query, we want to get the value of a variable after the package has executed. To do so, place a Get Properties action into the Execution track of the test. Inside the SentryOne Test Element Editor, in the Target Package drop-down select the MyFirstSSISLegiTestPackage. 

 Select Add under Properties to Get. The editor updates with the package name at the top. Drill down into the Variables area, and then select on @User::UnchangedRows. When you do, the area at the bottom updates to show all of the variables properties. Select on the Value. 

Finally, we need to uniquely identify this value by giving it a Resource Key. At the very top of the editor, enter a resource key of UnchangedRowsValue.  Select OK to save and return to the test.

The new value should be listed in the Get Properties editor inside the SentryOne Test Element Editor. 

Rename the action to Get UnchangedRows.

For the Assert place an Integer Comparison into the Assertions track. This time instead of comparing two resources, we compare a resource to a static value, so the Comparable Resource Value can be left at its default of StaticValue. Change the Comparison Value to 1, which is the number of rows that should not be changed. Then select the UnchangedRowsValue for the resource key. 

Test the test by using the Run All Tests button above the arrangement area. 

Test Five – Package Execution Time

This final test is repeat of the fourth test, only instead of a variable we get the package execution time. 

Add a new test, DimMySites_ExecutionTime. Add a Get Properties action to the Execution Track. After selecting the package in the element editor, select Add, then select on the package itself. In the properties that appear for the package, select ExecutionDuration, and finally give a resource key of MyFirstSSISLegiTest_ExecutionTime. Name the action Get Execution Time. 

Next comes the assertion, again we can use the integer comparison. This is another static compare, using the just added MyFirstSSISLegiTest_ExecutionTime resource key. In the value, we need to enter the time in milliseconds, 5000 for five seconds. 

In the assertion we want the time to be less than the comparison value. As long as the run time is less than 5000 ms we are good. Be sure to change the Comparison Mode to Less Than. 

After saving, we run it a bit differently than the previous examples. First go to the Solution Explorer. Right click on the project holding your SentryOne Test (in this example we named it My First SSIS SentryOne Test) and select Build. 

Now go to the Test Explorer. After the build, the new test should appear under the Not Run Tests branch (you may have to expand it). Right click on it, and then select Run Selected Tests. 

Your test should now execute and give you a green result. 

Conclusion

Not only can this test be used to validate this particular package, but it can also buffer against changes made elsewhere in your system. For example, let’s say you had over 500 packages in your project, plus databases. There’s no way to easily test all 500 every time. With SentryOne Test, it's a simple matter of launching your automated SentryOne Tests. This helps ensure a change elsewhere didn’t impact the package being tested.