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 »

Upgrading SSIS projects – part III

In the first part of the series I mentioned two methods of upgrading SSIS projects (well – packages, for now) – Application.Upgrade() and  Application.SaveAndUpdateVersionToXml(). This post is about the latter. The documentation of the method is also a bit sparse at the moment, but is self-explanatory:

Share
Continue reading »

Upgrading SSIS projects, part I

In the previous post, I wrote about migrating SSISDB database. When we migrate the database the packages still have the version of the source SSIS catalog. When you start the execution of the migrated package, you get the information like “The package was migrated from version 6 to version 8. It must be saved to retain migration changes.” This information is written to the log no matter which logging level we choose (also with None). The question is: will it blend should we upgrade the packages (or better – the projects)? And if the answer is ‘yes’ – why should we do it and what are the options? Should we

Share
Continue reading »

T-SQL Tuesday #96: Folks Who Have Made a Difference

This post is a part of T-SQL Tuesday series started by Adam Machanic in 2009. Ewald Cress (b | t) asked to give a shout-out to people (well-known or otherwise) who have made a meaningful contribution to your life in the world of data. As I was thinking about the people that had the most impact on my career I have to give the biggest kudos to my friend, Leszek Kwaśniewski (t). About six years ago I attended the SQL Server courses about T-SQL programming and database administration where Leszek was one of the trainers. During one of the breaks, he told about user group meetings about SQL Server and

Share
Continue reading »

So, you want to migrate SSIS(DB)?

Excellent! I wanted to, and after few trials and errors I finally did! And it’s pretty easy (as with all things you know when you learn it). For a start I will warn you a bit – SSISDB isn’t the database you just backup on one server and restore on another. There are some more steps to do. The same procedure will work for migrations from version 2012 to 2017 or 2016 to 2017, I didn’t check (yet) 2014 to 2017. I also tested the migration of 2012 to 2016 version but had some problems with this when it came to upgrading database part. Will investigate it later (probably some

Share
Continue reading »

Learning something new: getting information from SSIS packages with PowerShell

In the series of learning something new, I started with analysing of the SSIS package XML. I know what I want to extract, so let the fun begin. I will use Powershell to get the data from the .dtsx files and save it to the database. The whole script is presented below with comments. For more information scroll down. #I will use Out-DbaDataTable and Write-DbaDataTable from dbatools, so import it Import-Module dbatools   # find recursively all Executable nodes in .dtsx file function Find-Executables($fileName, $executable) { foreach($item in $executable) { # are we there, yet? (if no – recursion) if($item.Executables) { Find-Executables $fileName $item.Executables.Executable }   # if yes –

Share
Continue reading »