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:
Just wondering if there are any other processes/systems/db-testing applications we should be considering also.
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.
Legacy Databases are always challenging ones.
I would advise you to have a look at this post: http://quality-gates.com/?p=1284, where I cover what are the basic tests that need to be done to validate the ETL process in the different layers: Operational, Datawarehouse and more.
We choose to use Quality Gates in our project. Since it is not part of the ETL it makes the validation clean and objective. seems to me like what you are looking for.
链接地址: http://www.djcxy.com/p/10378.html