Automated SQL Server Block Testing

Question

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

When checking requirements, we write a series of SQL scripts that basically do not return anything if successful.

So basically it looks like

• Fulfill request
• Run another request
• Run SSIS Package
• Run request.

And an example with a little context

Requirement No. 1 Description: Show SO my problem

• A query to check if the target table exists and is empty.
• Query to check the availability of the source table and the availability of data
• Run ETL SSIS Package
• Request Check for correct data transfer
• Request to approve business rules

We found a way to automate this process by writing a special program for parsing SQL query execution, loading the necessary data, running SSIS packages, etc., and then reporting whether there is a result (which shows a test loss).

It looks like a wheel reinventing ... but I could not find anything like it. Especially one that integrates with SSIS.

EDIT:

Someone suggested SSISunit, and there is little documentation on it. If we were to use an SSIS block, would it be more like an installation process that would claim that the required conditions exist, steps 1 and 2 above? I always thought that the configuration process does not validate?

Answer 1

After about a two-month study, there is no OTS program that does what this question asks.

We were looking for a tool kit that allows you to determine how to verify a requirement. In our case, we needed a general procedure for executing SQL scripts, loading data and running SSIS packages, and the only way to do this at the moment is to write your own orchestration tool.

We used .NET to orchestrate, but @Sam is a really useful way to do this using SSIS. Thanks to @JasonHorner's advice, we are now looking at how to make it look and feel more like SSISUnit, but at a more organized level.

Answer 2

It is unclear to me your question about unit testing of ssis in business rules inside the ssis package or if this is just a means to an end, maybe this is useful:

http://ssisunit.codeplex.com/

Most xUnit frameworks support a configuration and break structure. I think you want to use the test setup part to execute the ssis package and the teardown step to the state of the reset database.

I would consider this as a starting point, as it is built into the 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 is not suitable for your problem;)

Answer 3

You can write an SSIS package that will complete all of these steps.

Create the variable myResult. View → Other windows → variables. Make sure that you are at the control flow level of the package, and not click on any task / step. You want this variable to cover the package level. Give it an Int32 data type and set the default value as the error code.

Run the SQL task, set the resultset property to "Single Row". Put the result in a variable with the result set area. Set the result name = 0 and the variable name = user :: myResult.

Verify the result by double-clicking the row between these two SQL tasks. Set to evaluate the expression and set the expression as follows: @myResult == 0

Run the following SQL task, putting the result in the same variable

Check the result as before

Perform package task (execute your SSIS)

Continue as needed ...

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

---

- Additional materials -

Since you want this to be common to many cases, you could write some code that pulled out the test case, as well as individual steps from the table, or you could do the same in SSIS (maybe!).

In SSIS, you can create a Foreach contour container that will work with the ADO result set stored in a variable. Depending on the type of step — SQLCMD or the SSIS package, you can branch to execute the package or execute the SQL statement using expressions to modify the relevant information, such as the package path or the sql statement. For simplicity, you will need a server field, sqlcmd and packagename for each step - the SQL task does not need the packagename command and ssis does not require sqlcmd.