Andrew Smith Posted on Testing of SSIS packages can be an ah-hoc, manual process that’s performed only at the package level. Checking that running a whole package (or set of packages) produces the expected data. This approach will likely not test all paths through an ETL, as well as failing to thoroughly test priority fixes that urgently need to go into production. John Welch, in his talk on at SQLBits XII, presents a unit testing framework called ssisUnit that he developed for SSIS.
NBi is a testing framework (add-on to NUnit) for Business Intelligence and Data. ETL and reports is limited to Microsoft Technologies (SQL Server - SSIS/SSRS). Unit Testing SSIS Packages Unit testing is a widely accepted best practice, yet it is difficult to do well with SSIS packages. This session will focus on both the practice of test driven development as it applies to SSIS, and the tools for actually implementing it. Repaint manager program level d.
This post highlights some of the key points from his session, as well as documenting my experience with installing and using ssisUnit with SSIS on SQL Server 2014. Unlike the ad-hoc manual approach to testing, ssisUnit can be automated, and results in a process that John explains is: (1) Fast (2) Repeatable (3) Verifiable and where this replaces the “deploy and hope” approach to ETL deployment with a healthy degree of confidence instead. SsisUnit testing is also performed at the SSIS task level, and so is at a lower granularity than testing that only considers the effects of a whole package. SsisUnit is based upon the well established xUnit framework that resulted from work by on Smalltalk in 1998. An individual ssisUnit/xUnit test has this structure: (1) Setup (put all the pre-conditions in place) (2) Test (execute the code and then check assertions) (3) Teardown (undo any changes made by the test) This results in individual unit tests that are atomic and independent of other tests. Cid episode 1 video download.
Installation ssisUnit can be found on Codeplex. The official release is for SQL Server 2008, but there’s also a beta download for SQL Server 2012 and an alpha download for SQL Server 2014. I tried the 2012 version first since it looked more stable, but it could not find the SSIS.NET assemblies it needed on my SQL Server 2014 installation.
I then replaced this with the SQL Server 2014 version, which did run successfully, albeit only after I’d edited the ssisUnitTestRunner2014.exe.config file to include to overcome a runtime error. The installation took place on my Windows 10 laptop. From CodePlex it appears that no releases have been done to this open source project within the last 2 years, possibly because Pragmatic Works have included ssisUnit within their BI xPress product, so maybe that’s where the ongoing development is taking place. That said, from below it can be seen that even this alpha version for SQL Server 2014 is functional and possibly of great use. There are 2 executables that implement ssisUnit, which can be run from the command line and/or added to the SSDT Tools menu: They were added to this menu by entering their names and file system paths via the “External Tools” menu item shown above. The ssisUnit File Structure The XML file used to specify the tests begins with the following sections: These specify any connection managers required by ssisUnit and also the package(s) under consideration. Following this are optional XML sections that can be used to specify any initial setting up for the test suite as a whole.
Then come the specification of the tests themselves. An example is: So this unit tests the “Load Products” Data Flow Task in the control flow shown previously. The setup stage copies a.CSV file into place that will be loaded by the data flow, and creates the target table if it doesn’t already exist. The Data Flow Task is then executed, and an assertion is run that checks that the data flow results in 6 rows being loaded into the target table. (As an alternative to row counts, ssisUnit also allows for the contents of the target table to be compared with the contents of a test table with the known correct contents.) No tear down section exists at the end of this test, but one can be included if required. Following all the test sections, a final tear down section can also be included, which specifies anything that needs undoing for the test suite as a whole.
Running the Tests If the “SSISUnit” item is executed from the SSDT Tools menu whilst highlighting the desired test file in Solution Explorer, the following appears in the Output window (or alternatively it can be sent to a command prompt window): This shows that the “Load Products” test referred to previously has passed, as well as the test following it. A lot of data appears in this window and it’s not easy to see quickly what’s passed and what’s failed. As an alternative the “SSISUnit UI” item can be executed from the SSDT Tools menu.
For the same test file, this produces a GUI interface: This can be used to build the XML test file to begin with (as opposed to manual coding), but here it’s just used to run the test. The last few lines of its test results are shown in the GUI here: So the “Passed” column to the right greatly eases viewing of the results, as does the final summary row of the test output, and also the information in the GUI status bar. Summary Whilst not a polished product – the alpha version for SQL Server 2014 does have a couple of obvious GUI bugs – ssisUnit still looks impressive at first sight and has clearly been very well thought through. The fact that it is free of charge does mitigate some of the minor niggles also. There’s no doubt that writing tests for most or all of the tasks in a package or set of packages will take some time, but that’s no criticism of this particular framework – this will always be the case. The big advantage here is that the end result is a test harness that can be run repeatedly and automatically, and that produces an unambiguous pass/fail result. SsisUnit can also be included within a continuous integration approach to developing and building SSIS packages, since it can be called as an executable from any scripting environment, and where the results of automatically run tests can be made available for human or machine parsing.
Testing is performed at the task level only – it’s not possible to test each transform individually within a data flow. This doesn’t have to be a bad thing though, as it will help keep data flows minimal and focused (as far as is practical). And as John Welch states, developers really need their own copy of the source database to test with, since tests can’t be run reliably if there’s a chance that other people or other programs are changing the data that’s being tested against. All in all though, it looks to be a big step in the right direction for moving from ad-hoc, manual testing to more thorough automated testing. Post navigation.
I'm Andrew Smith and I'm a Microsoft Certified database professional with 10 years' combined experience in Oracle/SQL Server development & administration, and an MCSA certification in SQL Server 2012/14. My focus is on using my development and RDBMS experience to bring best practice software engineering principles to ETL & BI development on the SQL Server platform.
I took a career break for the whole of 2016 to cross-train from Oracle to SQL Server, and passed the Microsoft exams for SSIS, SSRS, SSAS, T-SQL and SQL Server DBA. I am now working full time at IQVIA, as a SQL Server developer with a focus on ETL.
The most Basic way to perform a SSIS Unit Testing is to create your own testing package. Example below:. The Most popular Tools to perform SSIS Unit Testing are the ones you listed:. SSISUnit. SSISTester But after making a deep search i found a new way that is. BizUnit Framework which is predominantly used for the Biz Unit testing can be customized to test SSIS Package as well.
More info in the link below. Also if you mean by testing Package Validation (metadata, connections.etc), you can follow my answer in this SO question.