Bartosz Ratajczyk shows us how to create ssisUnit tests in MSTest with C#:
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.
Why MSTest? Because I don’t want to write some application that will contain all the tests I want to run, display if they pass or not. When I write the MSTest tests, I can run them using the Test Explorer in VS, using a command line, or in TFS.
UIs are great for learning how to do things and for one-off actions, but writing code scales much better in terms of time.
When executing the following query (on the Adventure Works 2012 sample database/cube), you’ll see two columns in the result displayed by SSMS. It’s probably what you’re expecting, you’re only selecting one specific level of the hierarchy [Date].[Calendar Date] and one measure.
You’re probably expecting that NBi will also consider two columns. Unfortunately, it’s not the case: NBi will consider 4 columns! What are the additional and unexpected columns? The [Date].[Calendar].[Calendar Year] and [Date].[Calendar].[Calendar Semester] are also returned. In reality, this is not something specific to NBi, it’s just the standard behaviour of the ADOMD library and SSMS is cheating when only displaying one column for the level!
Click through for the solution. And if NBi sounds interesting, check out Cedric’s prior post on the topic.
Haroon Ashraf walks us through a simplified example of test-driven database development:
In TDDD, business requirements are encapsulated in database unit tests.
In case the requirement is adding a new category to the Category table, it is necessary to implement TDDD according to the following steps:
Creating of database unit test to check the existence of AddNewCategory database object.
Failing of the unit test because of the database object absence.
Creating the AddNewCategory object in order for the unit test to pass.
The unit test determines whether AddNewCategory stored procedure is actually adding a new category or not.
That unit test also fails.
AddNewCategory procedure code changes to add a new category that verifies afterrerunning the unit test, which is able to pass now.
Laying out my biases, I’m not a fan of TDD for application development and definitely not a fan of it for database development. “Unit testing” inside a database is extremely limited, particularly when there are so many side effects and encapsulation tends to be actively harmful.
Rob Sewell shows us how to use Pester to ensure that a set of SQL scripts are valid T-SQL:
This is a quick Pester test I wrote to ensure that some SQL Scripts in a directory would parse so there was some guarantee that they were valid T-SQL. It uses the SQLParser.dll and because it was using a build server without SQL Server I have to load the required DLLs from the dbatools module (Thank you dbatools 🙂 )
It simply runs through all of the .sql files and runs the parser against them and checks the errors. In the case of failures it will output where it failed in the error message in the failed Pester result as well.
This particular example doesn’t ensure that the scripts do what you want them to do, but hey, Pester was built for that as well.
Attempting to debug production performance problems in your development environment can be problematic in many ways, leading to a frustrating troubleshooting experience. One very common situation is the resources on the development environment are substantially less robust than on the production system; for instance prod has 128 GB of RAM, while dev only has 16 GB, prod has 16 cores, while dev only has 4 cores. Unintuitively, this disparity can result in queries running faster in development than in production.
SQL Server has a little-known (and undocumented and unsupported) troubleshooting-related DBCC command that can be used to mimic production resource levels in your development environment. As with all undocumented features, do not try this in production.
Read on to learn how DBCC OPTIMIZER_WHATIF can lead the optimizer to choose different plans. I almost never use this command, but it is helpful to have it in your back pocket.
Bartosz Ratajczyk shows us a few methods for 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
msdbdatabase- 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.
Read on for an example of using each method.
Bartosz Ratajczyk shows how to convert ssisUnit tests to work with the NUnit or MSTest frameworks:
MSTest v2 is the open source test framework by Microsoft. I will not write a lot about it. If you want to learn more – read the excellent blog posts by Gérald Barré.
I took the idea and parts of the code from Ravi Palihena’s blog post about ssisUnit testing and his GitHub repository. Then I read the source code of the SsisUnitTestRunner, SsisUnitTestRunnerUI and posts by Gérald and changed the tests a bit.
I will use MSTest to execute ssisUnit tests from the file
20_DataFlow.ssisUnit. For that, I created a new Visual C# > Test > Unit Test Project (.NET Framework) –ssisUnitLearning.MSTest– within the solution. I also set the reference to theSsisUnit2017.dllandSsisUnitBase.dlllibraries and loaded required namespaces
Bartosz gives us the initial walkthrough, and then builds a T4 template to automate the task. You can grab that template on his GitHub repo, and hopefully something makes its way into ssisUnit to make integration with NUnit / MSTest official.
Bartosz Ratajczyk shows how to store result sets in a test file with ssisUnit:
The ssisUnit GUI does not support creating the persisted dataset. If you switch the IsResultsStored flag to true on the dataset’s properties, it gives a warning “The expected dataset’s (<the dataset name>) stored results does not contain data. Populate the Results data table before executing.” during the test run.
To find out more about it, take a look at the source code.
This is a nice explanation of a current limitation in the tool and a workaround.
David Wilson shows how to build integration tests in nUnit when you’re using spatial data types:
I was recently working on a .NET 4.6 based project that was using EF 6 and nUnit for unit testing. While setting up some integration tests against a local SQL database I was receiving this error:
Spatial types and functions are not available for this provider because the assembly ‘Microsoft.SqlServer.Types’ version 10 or higher could not be found.
We had recently been using SQL Server spatial types for tracking geograpic locations and the tests which performed updates and inserts against these fields were failing.
Read on for the setup instructions.
Ed Elliott has a post showing how to perform unit testing on ETL processes:
In tSQLt, we can call tSQLt.FakeTable and then do an insert, if we don’t use tSQLt what do we do? Well, we need to setup the data we want, this could be by using a tool or by writing a load of insert statements. I have seen this done in various ways such as:
- Writing manual insert scripts
- Using a tool to setup the data
- Making use of API’s in the application to setup the data we need
- Some wierd and wonderful things that we shouldn’t recommend
Ultimately, for each test that you do you need to know what data you need for it. There isn’t really any way around this, and the sooner you get yourself in the position where you can setup the data you need for a test, the better.
Read the whole thing.
Kevin Feasel
2018-08-13
Integration Services, Misc Languages, Testing
No Comment