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

Question

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 data source 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 1

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.