Automated SQL Server Unit Testing [closed]

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

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 2

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.