Start testing your SSIS packages with ssis Unit

Start testing your SSIS packages with ssis. Unit SQL Saturday Oslo, 1 st September 2018

A big thanks to all of our sponsors!

Download the slides and the code http: //www. sqlsaturday. com/746/Sessions/Schedule. aspx https: //github. com/Bartek. R/ssis. Unit. Learning Read more: https: //blog. bartekr. net

Bartosz Ratajczyk SQL Server consultant @ 7 N, trainer Works with SQL Server for over 12 years Advisory Board member of Data Community Poland SQLDay conference co-organizer Open source contributor (dbatools, ssis. Unit) SQL Saturday volunteer @b_ratajczyk https: //bartekr. net

„I’m already testing…” Manually setting the parameters Manually running the packages Manually disabling the tasks Manually enabling them again Manually running the code in SSMS Are you sure the table has 0 records? Did you commit with all tasks enabled? Is it setting the variable’s value? Does it work with unexpected parameters?

How about Writing tests for the packages or tasks? Running tests automatically? Using free tools? (or free and paid if you wish)?

My goal (achieved)

SSIS testing options Write your own solution SSISTester BEST (BIML Enabled SSIS Test) ssis. Unit Bi. Xpress Legi. Test ORAYLIS BI. Quality Db. Fit Biz. Unit Green == commercial tool

ssis. Unit Why, and what is it?

ssis. Unit Testing framework by John Welch Open source, C#, XML With a GUI and an API Released on Code. Plex in 2008 Works with SSIS 2005 – 2014 (and 2017) Used in Pragmatic Workbench (only for SSIS 2005 – 2014)

ssis. Unit Test Suite • Test Suite contains Tests • Tests have Asserts (definitions of the expected test outcome) • Asserts have Commands (run to get the test outcome) • Tests apply to Packages and tasks in the packages • Commands can use Connections and Datasets • Tests and Test Suite can have Setup and Teardown phases

Commands Project and package parameters Comparing data Working with directories Using files Running programs Package and project properties Running SQL code Operations on variables

Test file structure

Test fragment

Libraries Ssis. Unit. Base. dll (< 20 k. B) Ssis. Unit<version>. dll Ssis. Unit 2017. dll (< 150 k. B)

DEMO Starting with ssis. Unit

Starting simple Parameters Variables Expressions Variables scope Sensitive values Parameter. Command Variable. Command

Getting further Merging data with SQL Task Sql. Command Data. Compare. Command

Automation ssis. Unit + MSTest + TFS

Why use an additional test framework? ssis. Unit has its own test runners (GUI + cmd) Both not suitable for easy automation and analysis Let’s use a test runner from VS! http: //www. ravipal. net/2016/03/ssis-unit-testing-with-ssisunit. html https: //github. com/rarpal/Sample. SSISUnit

DEMO Using ssis. Unit with MSTest and TFS

Starting simple with MSTest – [Test. Method]

(and some additional methods)

Getting a bit further – T 4 template

Target acquired

Step forward – using ssis. Unit API

Summary ssis. Unit helps you easily start with testing (GUI) You can integrate it with CI/CD tools You can write the tests in XML or using ssis. Unit API

Now, ask me some questions

A big thanks to all of our sponsors!