Eoin Campbell Eoin Campbell - 9 months ago 54
SQL Question

How should I perform automated testing of SQL ETL Scripts & SPROCs?

We have a number of DTSX Packages that Extract-Transform-Load data from a number of legacy Pervasive Databases -> SQL Server 2k8 R2. (via ODBC)

The majority of our ETL process T-SQL scripts

The legacy data is a badly structured mess & most often, the tweaks & changes we make to our ETL scripts are to deal with this malformed data.

What we'd like to do is put some process in place to automate testing of these SPROCS / Scripts that perform these transformations.

What we've come up with ourselves is to:

  • Have some known datasource input (hosted in a local pervasive database)

  • Run the DTSX Packages to generate the output

  • Have some expected & pre-created output somewhere else within SQL Server.

  • Write our own verification scripts to compare the expected with the actual.

Just wondering if there are any other processes/systems/db-testing applications we should be considering also.

Answer Source

Have a look at tSQLt - the Database Unit Testing Framework for SQL Server. It makes writing robust tests fairly easy.

It does not (yet) allow for testing of scripts, but you could wrap those into stored procedures.