This project has moved and is read-only. For the latest updates, please go here.

How Do I : using SSISUnit with designing ETL packages

Aug 18, 2009 at 5:45 PM

First, thank you for your hard work on this. You wrote in one post that it was a labor of love, and that love shows. I'm very impressed by what you have put together.
I know you have a lot planned for this project, but I was wondering if there were any plans to include testing random bits of data during an ETL process? To clarify this, I need to put it in context: We have an Oracle database that we are planning to migrate to SQL Server by June of 2010. We can handle testing rowcounts pretty easily. It's the veracity of the transfered data we are most concerned about right now (and automated test generation, but that is another topic for another day). Can SSISUnit handle testing such things? If so, how can we create tests within SSISUnit to handle that task? I can see how to assert, as well as how to call a SQL Command, but what I can't figure out is how to pass that value into an object (most likely a collection of some sort) and compare the values of the source to the destination. If not, what level of effort and difficulty would be involved with implementing such an enhancement?


Joey Filichia.


Aug 18, 2009 at 9:45 PM

Thanks for the comments, Joey. I'm glad you are seeing some benefit from it.

Currently, you can test specific bits of data using the Asserts (which can contain Commands) to retrieve and validate a specific value. So, for example, if you wanted to verify that a particular Customer was written with the correct phone number, you would use a SQLCommand in the Assert to retrieve that customer's phone (SELECT phone_nbr FROM Customer WHERE CustID = 101) and compare that to a known value in the Assert. Granted, that's not terribly useful for testing large amounts of data.

There are two planned enhancements that might help with this. One would allow you to store a value in the Setup for a test, and use that stored value in the Assert. That allows you to make the Asserts dynamic - for example, you could retrieve the source table's row count in the Setup, and then compare that value to target table's row count in the Assert. That still focuses on comparing single values, though. The other planned enhancement is to allow the comparision of datasets as part of the Assert - basically a row by row, column by column comparision of two resultsets.

Would either of those meet your needs? Conceptually, they aren't too difficult to add, but there are some challenges when you start getting into the details.


Aug 19, 2009 at 2:49 PM

Thanks for a fast reply John. Truth be told, I'm a developer who has been moved into the conversion project I mentioned (apparently, I can do "anything" in the eyes of my company). Since I have the skills, let's put them to use. I can put the functions in that you mentioned because they sound like a good possibility, and I'm all about good possibilities. Contact me at mjfilichia<at> and let's talk. No need to expect you to put in enhancements I request when I can help you with doing that.