Dynamic SSIS package for Data Profiling

The data cleansing element of a data warehousing projects can take a long time depending on the quality of the source data. You need to know the extent of the data quality problems early in the project. The data profiling task in SSIS is a good starting point assuming you have access to the source data and know which tables within the source are likely to be required.

I wanted a dynamic data profiling template that I could configure using variables and schedule during  a maintenance window to collect profiling data for specified tables within one or more databases. Looking into the problem I quickly found two good references.

The first was Jamie Thompsons blog from back in March/April 2008 - I'm not planning on moving to SQL Server 2012 any time soon so 2008 and 2008 R2 references are fine for now. The 10 articles are an excellent introduction to the data profiling task and interpreting the output.

But the most relevant article I found was by John Welch who seemed to have the same idea as me to create a re-useable data profiling package.

Data profiling template originally from John Welch.

It's a simple package and doesn't do everything I wanted. Specifically, it's not able to loop through multiple databases or limit to specified tables only. Also, I try to avoid direct input SQL statements in Execute SQL Tasks as I find administering SSIS packages with code buried in tasks to be less efficient than using parameterised stored procedures.

However, being pragmatic about things, it's not a package I'll run every day and it'll run in environments where I have no access to create an Admin database for DBA scripts and procedures, so I'll accept it as is. And I can schedule a SQL Admin job with multiple steps each calling the package with different configuration parameters to achieve my original goal.

You can download the source from the original blog here or with some minor amendments from me here.