Unit testing ssis load

Question

Post reply

Unit testing ssis load

Anjan@Sql

SSCrazy

Points: 2316
More actions
May 25, 2017 at 9:35 am

#325934

Good morning folks.
We have a ssis package which is loading multiple files from an FTP location to the SQL server database.
I am looking for the suggestions on how to unit test this load process.we have close to 30 files with millions of records which should be loaded into the database tables.of course the ultimate testing should be the count should be matching between number of records in files and the count ib tables after the load. I am new to this ssis.any suggestions...?thanks in advance..

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 1

You aren't unit testing if you look at the full load. That's more of a system test.

There are unit testing frameworks, SSISTester and LegiTest and more.

If you want to test, I'd cut down the list of files to a few, 2-3, and include all the strange cases you need to cover in those files. This will help you test the process (multiple files) and the logic. Keep this version controlled, and as you need to add data to handle cases, add a row or two to the test data set. This gives you a way as a developer to manage this without wasting time.

Whoever verifies this will only look at total row counts, so you should have an input row count and an output row count. They may be different if you do transforms, but have this as the test of the complete system. If you get error rows, make sure you capture those and ensure that all input rows are processed. Some of this is setup, getting the raw counts from files, some is logging, ensuring that you capture information on what's happening at different stages.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com