7

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.

Vikrant
  • 4,920
  • 17
  • 48
  • 72
Eoin Campbell
  • 43,500
  • 17
  • 101
  • 157
  • This will help perhaps abit http://stackoverflow.com/questions/1007701/can-i-run-test-create-dtsx-packages-outside-visual-studio – Sergey Benner Jan 13 '12 at 14:24

2 Answers2

3

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.

Sebastian Meine
  • 11,260
  • 29
  • 41
-1

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.

uzi
  • 1
  • 1