none
Unit Testing RRS feed

  • Question

  • Well was wondering this weekend. How do you do unit testing with SSIS? Is anyone trying it?

    What are you doing and do you have any suggestions?
    Monday, June 20, 2005 7:58 PM

Answers

  • Folks use a variety of techniques for this:
    - Row Count to count the number of rows that're going into their database and a precedence constraint based on the expression that says this Row Count is within a range.
    - A directory of SQL scripts to run after data flow is done. This is typically done with a foreach file loop over sql script files in a directory tree.
    - Use script task's logging functionality to log interesting information during execution. While not directly related to unit testing, this approach can be used for logging customized information that can be queried on later for historical analysis. This is also useful because you'll likely not have assertions in the code with interactive UI but rather log anomalies to a server and cause failure of the package in the script itself.

    These are built into the packages with a precedence constraint on them to execute only if a debug variable is turned on.

    You can, of course, also do it outside the tool and go directly against the database.

    If you have other interesting techniques, do let us know!

    thanks
    ash

    Wednesday, June 22, 2005 12:15 AM

All replies

  • Yeah, this is on my wishlist as well.  It's sort of nerve-wracking writing a script and just hoping it works correctly when I test it for the first time (as often running the package may have side-effects).  Also, since packages will often be run regularly, it would be nice to have "assertions".  For example, I could test that each column conforms to certain constraints.  If not, it would fail and pop up a message box indicating the problem.

    Without these I feel really uncomfortable using SSIS Sad

    It'd be nice to have unit tests for individual data flows (or specific units of work in the control flow) as well, where these data flows can also be copied to other packages.
    Tuesday, June 21, 2005 4:36 PM
  • So far, the only successful way I can see to do any unit testing is to do it outside of the existing tool sets.
    For me this means using NUnit, creating, populating, executing test, validation of action all with temporary databases. It will be significant work, but I don't see another way.
    Tuesday, June 21, 2005 8:02 PM
  • Folks use a variety of techniques for this:
    - Row Count to count the number of rows that're going into their database and a precedence constraint based on the expression that says this Row Count is within a range.
    - A directory of SQL scripts to run after data flow is done. This is typically done with a foreach file loop over sql script files in a directory tree.
    - Use script task's logging functionality to log interesting information during execution. While not directly related to unit testing, this approach can be used for logging customized information that can be queried on later for historical analysis. This is also useful because you'll likely not have assertions in the code with interactive UI but rather log anomalies to a server and cause failure of the package in the script itself.

    These are built into the packages with a precedence constraint on them to execute only if a debug variable is turned on.

    You can, of course, also do it outside the tool and go directly against the database.

    If you have other interesting techniques, do let us know!

    thanks
    ash

    Wednesday, June 22, 2005 12:15 AM
  • Anyone have any links to the MS way to do unit testing?  It's the first topic in 70-441...
    Monday, November 28, 2005 11:19 AM
  • Hi Ash,

    would you mind expanding or give an example on the 2nd approach you mentioned - ''A directory of SQL scripts to run after data flow is done. This is typically done with a foreach file loop over sql script files in a directory tree"

    Thanks in advance.

     

     

    Monday, February 27, 2006 7:13 AM
  • What about DB state? My main problem is to execute the SSIS packages against a DB with a specific state otherwise the unit testing will not produce the same result.

    Is there a particular technique to restore the DB state easily ?

    Tuesday, September 26, 2006 8:54 AM
  • I've been reading about this but haven't done anything yet. Here are a couple of things I found:

     

    There is a section of a paper on MSDN about this, in the VS Team Edition context.

     

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

     

    One might be able to poke around there and steal some ideas for use outside that context. For instance, in the paper they talk about the data generation tool built into VSTE but there exist other data generation tools and one can merely use standard, static ways to populate parts of a database (SELECT from somewhere, an XML file containing the before-state data, etc.).

     

    There is a section "Setting up database tests" on this page:

     

    http://www.agiledata.org/essays/databaseTesting.html

     

    In an earlier life (SQL Server 2000) I did regression testing on big ugly SQL queries by running bat files that invoked VBS scripts that invoked the query with the results going into a text file; the bat file then invoked a diff utility to compare the new results with a file of correct results and pumped the diff results into another text file; then a script looked at that file and if the diff output file length was greater than zero bytes the script signalled a problem. Obviously this was testing a read-only function.

     

    There is also www.ndbunit.org which says "NDbUnit is a .NET library for putting a database into a known state. NDbUnit may be used to increase repeatability in unit tests that interact with a database by ensuring that the database's state is consistent across execution of tests. NDbUnit does this by allowing the unit test to perform an operation on a dataset before or after the execution of a test, thus ensuring a consistent state."

    Friday, July 6, 2007 7:37 PM
  • The latest version of SQL Server Data Tools (SSDT) provides you with a feature to perform Sql Server Database Unit Testing, and thus helps to raise and maintain the quality of your SQL Server databases. You can write your unit tests, assert for various test conditions, verify database code, create custom test conditions, to name a few. A brief introduction can be found here:

    http://blogs.msdn.com/b/ssdt/archive/2012/12/17/10375495.aspx

    And you can download the latest version of SSDT for free and try the feature out:

    http://blogs.msdn.com/b/ssdt/archive/2012/12/13/available-today-ssdt-december-2012.aspx

    Monday, January 7, 2013 7:15 PM