Testing your SSIS 2008

Define a best practice testing a SSIS solution is not an easy task, but I would like to share what I consider good practices from my experience and humble knowledge about the matter.

First of all you have to consider to test your SSIS packages not only when you finish your development but in every little step/task you are working on. Most of us are used to develop a whole package without any test before, cause the unique nature of this visual “language” programming, that is so easy, fast and thankful to work. That’s just an illusion, cause you cannot shouldn’t work on a whole package (or even solution) without testing it little by little.

Then the first good and reasonable practice is to test every task or dataflow you create and thanks to this, you will be able to detect the problems earlier (not funny surprises after some days developing a wrong idea) and reducing the cost of fix, isolating this issues in little problems to solve before any more development.

A good (and painful) practice is to apply “Continuous Integration“, that means, whenever you are working in a big solution (with even more developers) you have to test your code everytime you check in in your TFS, having special care not broking any other part of your entire solution, something called “Regression Testing“.

 

Strategy to implement a UnitTest

 

 

 

UnitTest & Test Suites

There is many good tools for SSIS beyond the C# Unit Test provide by MS Visual Studio, some of them could be:

 

  • * ssisUnit: What I consider the best and easiest one for SSIS with a lot of people working on it and sharing and blogging their experiences with this tool
    http://ssisunit.codeplex.com/

 

  • * xUnit: Probably a good test suit for SSIS but it is built for .net Developments (kind of what VS can offer you)
    http://xunit.codeplex.com/

 

  • * NUnit: Brother of xUnit, you can use it for any testing with your .net developments and some SSIS as well.
    http://www.nunit.org/

These test suites helps you to create an automated whole package/solution test in different levels. That is the more professional and right approach for a big solutions.

Some help to use them is this video :
http://files.sqlbits.com/event10/unit_testing_SSIS_packages/unit_testing_SSIS_packages_c_1000.mp4

 

Manual Testing

Here I would like to expose what for me are more than an alternative to the UnitTest tools, because from my point of view some of this little tips should be applied in the 99% of the cases, doesn’t matter if you are going to implement a whole testUnit solution right after o not, it will help you to check step by step if everything goes as we expected.

1) Using Logging in your SSIS with a SQL table. It is something that I won’t ever miss, cause it gives you for free a whole audit framework writing in a SQL table if some of your packages/tasks has any problem, or how long it takes (to improve the performance as well).

2) CheckPoints, it will help you a lot in a long development, saving some of your time not relaunching the whole solution till your failed point

3) If you are aware of your checkpoints a good practice would be to take care about the TransactionOption in case you don’t want to apply any dataflow that writes something in the begging and fails in the last step (for example).

4) Test the same package on another box. This would help to test properties like DelayValidation property and PackageProtectionLevel property. Whenever SSIS Packages are executed, first the SSIS runtime checks the package for validity and provided it passes the validation, then it executes the package.

5) Use the ForceExecutionResult property with each task to check how the flow behaves in case of failure, success, and completion.

Checking list

 

Design and best practices To check Passed

Check if best practices

have been followed

Best practices related to
optimization/tuning/design
have been followed.
 

OK

 

Naming convention at
project/packages/tasks/component
level is applied.
 

X

 

Package structure is aligned with
Package Design best practices.
 

OK

 

Testing To check Passed
Check if testing

has been completed.

Normal
flow scenario
Verify all tables/rows have
been imported.
 

 

Verify all data has been imported without
any truncation (for each column).
 

 

Error flow
scenario
Verify error flow when no input
data source available.
 

 

 

Verify error flow when no
output data source available.
 

 

 

Verify error flow related with input
data quality (NULL values, strings instead
of numeric values, etc.)
 

 

 

Logging Check if chosen logging mechanism is in
place and working properly for the selected events.
 

 

 

Deploying Check if package(s) has been deployed
and executed as expected in a proper testing environment.
 

 

 

Configuration Check if chosen configuration
mechanism is in place and working as expected.
 

 

 

 

Keeping in mind to have a complete and successful SSIS Test

1 Verify all the tables have been imported.
2 Verify all the rows in each table have been imported.
3 Verify all the columns specified in source query for each table have been imported
4 Verify all the data has been received without any truncation for each column.
5 Verify the schema at source and destination
6 Verify the time taken /speed for data transfer
7 Fields truncated due to difference in length of the field at destination.


Did you like this? Share it:

Leave a Reply

*