Working with properties in ssisUnit

One of the ssisUnit commands is a PropertyCommand. It allows you to read or set a property of the task, the package or the project. As of the time of writing – you can’t test the properties of the precedence constraints or data flow elements (but you can’t currently test data flow at all). How do you use it?

Share
Continue reading

Writing ssisUnit test using API

In the post about using MSTest framework to execute ssisUnit tests, I used parts of the ssisUnit API model. If you want, you can write all your tests using this model, and this post will guide you through the first steps. I will show you how to write one of the previously prepared XML tests using C# and (again) MSTest.

Share
Continue reading

Testing the loops in ssisUnit

In the Q & A post after the webinar on ssisUnit (in 2013) John Welch answered the question about the loops: “If possible, can you demo if a container can be executed? Especially a For loop or For Each loop?” I didn’t have time to demo this during the presentation. Good thing too, because there was an error around handling of containers. This has now been fixed in the source code version of the project. There is no example though, so let’s add one.

Share
Continue reading

Setting package references in ssisUnit

When you set the packages’ references in the ssisUnit tests you have four options for the source (StoragePath) of the package: Filesystem – references the package in the filesystem – either within a project or standalone MSDB – package stored in the msdb database Package store – packages managed by Integration Services Service SsisCatalog – references the package in the Integration Services Catalog In this post, I will show you how to set the package reference (PackageRef) for each option.

Share
Continue reading

Executing ssisUnit tests in MSTest framework

One of the drawbacks of ssisUnit is that it has only its own test runner. You can run the tests either using GUI or the console application, but the output is not that easy to parse and to present on the report. I’m used to working with Pester output or using NUnit/MSTest frameworks that integrates nicely with other tools. In this post, I will show you how to prepare and execute ssisUnit tests using MSTest framework, how to automate this process, and how to run those tests with TFS (or VSTS).

Share
Continue reading

Using cached datasets in ssisUnit

In the previous post, I wrote about using datasets in the ssisUnit test. By default, the dataset query is executed against the data source each time the test is run. But we also have an option to store the dataset’s result in the test file. In this post, I will show you how you can use it.

Share
Continue reading

Using Connections and Datasets in ssisUnit

One of the elements you can define in ssisUnit is a Dataset. In this post of the ssisUnit series, I will show you how to prepare and use it later in a test. The Dataset As you can see in the image above, the dataset is a named result that contains data. It has five attributes (although you can see just four of them in the GUI): Name (required) – the name of the dataset, used for referencing the dataset in a test IsResultsStored (required) – the boolean flag informing if we have the results cached (true) or we always ask the external source (false) Connection (required) – the connection

Share
Continue reading

Testing database connections with ssisUnit

Previously we successfully prepared tests for variables and parameters using VariableCommandand and ParameterCommand. Now it’s time to communicate with the database, and for that, I will use connection manager defined on the project level. I know from the ssisUnit tutorials it works perfect with package connection managers, so it’s time to verify it against the projects. I will test the package 10_ProjectCM.dtsx – it is just getting a single value from the table in a database and storing it in a variable. All the packages and unit tests are on my GitHub.

Share
Continue reading

Writing first tests with ssisUnit

Previously I wrote about the importance of testing the SSIS packages and introduced you to ssisUnit. In this post, I will show you how to write simple tests for the variables and parameters using Test Suite Builder. As I wrote before: just start slow and small, don’t write your first tests for the most complicated part of the package. Create a new SSIS project and use the automatically generated Package.dtsx. Open it and add two parameters:

Share
Continue reading

Testing SSIS Projects with ssisUnit

During the upcoming SQLDay 2018 conference (10th edition of SQLDay!) I’ll be speaking about testing SSIS packages and projects. From my observations, I see that we don’t like testing (I’m talking about database and ETL people), but when we start doing it – it becomes a natural part of our work. In my current project, we started slow, with some data quality testing for some parts of the process. Today you can hear “let’s write a test for it”, and it’s just a regular part of the process. I want to take a testing experience a bit further. We already have data quality testing (and the number of tests grows

Share
Continue reading