Unit testing is of the the key development practice that should be religiously followed. A proper unit tested package can reduce 50% defects, that would come down post system testing or end-to-end testing.
The first part of unit testing starts from matching the developed package against the requirements. Below are some tips that can help to make unit testing of ssis packages, thorough, broad, and efficient. I have followed these ideas or approach of testing SSIS packages, while working with SSIS 2005 or SQL Server 2005.
1) Use a Script Task to put your debugging messages, and log them to the configured log provider. This can be particularly used to check variable values getting updated whenever they are updated by a task.
2) Use the ForceExecutionResult property with each task to check how the flow behaves in case of failure, success, and completion.
3) To emulate a long - running query, use the TSQL waitfor statement in the query. This will help to test scenarios like connection issues as well as functional dependencies, for eg, if a particular task completes faster than other.
4) Check naming conventions are followed as per the corporate / project naming convention policy document. This applies to packages, variables, tasks and components.
5) 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.
6) Execute the package using the DTExec Utility instead of executing it everytime from BIDS. Also if the package would be getting called from other package, for eg. master package, execute the package everytime from the parent package. This would help to test package configurations, as in such scenarios the package configuration used is parent package configuration.
7) Check against each task if errors are configured to be ignored. If Yes, cross-verify with developer the reason for the same and get it documented as part of annotation in the package or somewhere in the Technical Specification Document (TSD) of the package. These can filter out issues with Destination Adapters and components like Lookup task/component.
Article 
6 comments:
You might also have a look at http://www.codeplex.com/ssisunit
Well... I am not impressed with the ideology behind ssisunit. In practical scenarios, where related commands needs to be written to a config file and executed against the package. This might still work for routine data massaging applications, but I am sure that it can't live upto the testing requirements of the kind of packages that are developed for populating data warehouse; especially fact tables and SCD2 dimensions.
Hi, I'm the developer of ssisUnit. I'm curious as to why you feel it can't meet the requirements testing packages for data warehousing? My company uses it extensively for exactly that kind of testing.
If your concern is related to load or volume testing, then I agree, that is not what ssisUnit was developed for. However, I don't really consider that to be unit testing. We work with small sets of test data for unit tests, and then run integration tests against a larger set of data to verify load and volume.
If there is some missing functionality that you feel would make it more usable, then I'd like to hear about it. I'm always looking for ways to improve it.
Firstly thanks for visiting my blog, I feel honoured. My apollogies, intention of my comment is not to offend by an means :) I have some questions for you.
1) I agree that for tasks such as Execute SQL Task, this could work. How easy would it be to create test cases for tasks such as Pivot and Script task and how much would be the time to prepare a test case for the same thru this framework.
2) Can this framework test all the points that I have put down in my post for unit testing ?
3) In master-child package loads, where there are many container and tasks, I am not sure how much straightforward it would be to execute a test case where N number of tasks run in parallel. For ex. Master Extract package.
4) By and large, I felt that more complex and exhaustive the package, more is the time it would take to develop a test case. Also the same can't be reused as it's a case to case basis and not a generic framework. That is not just due to framework but also due to nature of SSIS development. Framework is something that I look up to is for example, NUnit or XUnit which is a generic testing framework.
These are my independent views. Nothing personal and No offence buddy :) May be after you provide more insight about the tool, I would start liking it :) !!
Good Article
Brilliant article I master some new things today. Many thanks google to receive me the info I wanted, stick with it.
Post a Comment