Checklist for testing and reviewing ssis packages

Friday, February 18, 2011

At UruIT we work a lot with SQL Server Integration Services and compiled several best practices found on the web that we already adopted. Many of them related to performance tips and conventions. At the end of this post I am sharing some usfeul links.

Nevertheless, we still couldn’t find something like a checklist that we could follow in order to say: ‘ok, if all this bullets are in green we can move forward to going live’. Hence, we built our own basic checklist which are already following in order to review packages. We use to apply this checklist in peer reviews too and now, decided to share with the community.

Please, if you find it useful and have feedback to improve it, I will be really glad to hear it.

So, here is the checklist:

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.