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 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.