Automated SQL Server Unit Testing

Question

I know there are ways to automate SQL Server "unit tests." But my question is slightly different.

When validating requirements we write a series of SQL Scripts that basically return nothing if success.

So basically its like

1. Execute Query
2. Execute another Query
3. Run SSIS Package
4. Run Query.

And example with a little more context

Requirement #1 Description: Show SO my issue

1. Query to check if target table exists and is empty
2. Query to check if source table exist and has data
3. Run ETL SSIS package
4. Query Check that data transfered correctly
5. Query to assert business rules

We have found a way to automate this process by writing a custom program to parse through execute SQL Queries, load necessary data, run SSIS packages, etc and then report if we have a result (which shows test failure).

This seems like a wheel reinventing... but I haven't been able to find anything like it. Especially one that integrates with SSIS.

EDIT:

Someone suggested SSISunit, and there is little to no documentation on it. If we were to use SSIS unit is it more like the setup process would assert that the required conditions exist, steps 1 & 2 above? I always thought setup processes do not do validation?

Answer 1

You could write an SSIS package that executes all these steps.

Create a variable called myResult. View >> Other windows >> variables. Be sure you are at the control flow level of the package and not clicked into some task/step. You want this variable scoped to the package level. Give it Int32 data type and set the default value as your error code.

Execute SQL Task, Set the resultset property to 'Single Row'. Put result into a variable with the Result Set pane. Set Result Name = 0 and Variable Name = User::myResult.

Check Result by doubleclicking the line between these two SQL Tasks. Set to evaluate an expression and set expressions like this: @myResult == 0

Execute next SQL Task putting result into same variable

Check Result as before

Execute Package Task (execute your SSIS)

Continue as needed...

You can execute SSIS packages with the DTEXEC.exe runtime. Return codes are listed there, so you can perhaps integrate into another process.

---

--Additional stuff--

Since you want this to be generic for many cases, you could either write some code which would pull a test case and it's individual steps from a table or you could do the same thing in SSIS (maybe!).

In SSIS you could create a Foreach Loop Container which would operate on an ADO result set stored in a variable. Depending on the 'step type' - SQLCMD or SSIS package, you could branch to execute a package or execute a SQL statement using expressions to change the relevant info such as package path or sql statement. You would need a field for the server, sqlcmd and packagename for each step for simplicity's sake - SQL task doesn't need the packagename and ssis task doesn't need sqlcmd.

Answer 2

Its unclear to me whether your question is about unit testing ssis the business rules inside an ssis package or if that is just a means to an end perhaps this is useful:

http://ssisunit.codeplex.com/

Most xUnit frameworks support setup and teardown structure. I think what you would want is to use the setup portion of the test to execute the ssis package and the teardown step to reset database state.

I would look at this as a starting point, since it is built-in to visual studio.

http://msdn.microsoft.com/en-us/library/bb381703(VS.80).aspx

So to answer your question yes, I think you are reinventing the wheel; but maybe the existing wheel isn't a good fit for your problem ;)

Answer 3

After about a months worth of research there is no OTS program out there that does what this question asks about.

We were looking for a tool set that allows for you to define how to validate a requirement. In our case we needed a generic routine to execute SQL Scripts, Load Data, and Run SSIS packages and the only way to do it currently is to write your own orchestration tool.

We used .NET to orchestrate but @Sam did provide a useful way to do it with SSIS. Thanks to @JasonHorner advice we are now looking at making it look and feel more like SSISUnit but at a more organized level.