Data Profiling Task for Multiple Tables

I am a big fan of BIML, so when I was faced with the task of creating DataProfile Tasks for several dozens of tables in the database, I naturally turned to a quick and easy BIML solution:

	<#@ import namespace="System.Data" #>
	<#@ import namespace="System.Data.SqlClient" #>
	<#@ template language="C#" tier="1" #>
	<#
	string _con = @"Data Source=SERVER;Persist Security Info=true;Integrated Security=SSPI;Initial Catalog=DATABASE";
	DataTable _extract_table_info = new DataTable();
	SqlDataAdapter _con_da1 = new SqlDataAdapter("select TABLE_NAME from INFORMATION_SCHEMA.TABLES a where a.TABLE_SCHEMA = 'XXX'", _con);
	_con_da1.Fill(_extract_table_info);
	#>
	<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<Projects>
	<PackageProject Name="DataProfiling">
	<Packages>
	<#foreach (DataRow _extraction in _extract_table_info.Rows) { #>
	<Package IsEntryPoint="false" PackageName="DP_<#=_extraction["TABLE_NAME"].ToString()#>"/>
	<# } #>
	<Package PackageName="DataProfiling.Master"/>
	</Packages>
	</PackageProject>
	</Projects>
	<Connections>
	<#foreach (DataRow _extraction in _extract_table_info.Rows) { #>
	<FileConnection Name="Profiling_<#=_extraction["TABLE_NAME"].ToString()#>.xml" FileUsageType="CreateFile" FilePath="C:\...\DataProfiling\Profiling_<#=_extraction["TABLE_NAME"].ToString()#>.xml" />
	<# } #>
	<AdoNetConnection Name="DBCON" ConnectionString="Data Source=SERVER;Initial Catalog=DATABASE;Integrated Security=True;" Provider="System.Data.SqlClient.SqlConnection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
	</Connections>
	<Packages>
	<#foreach (DataRow _extraction in _extract_table_info.Rows) { #>
	<Package Name="DP_<#=_extraction["TABLE_NAME"].ToString()#>" ConstraintMode="Linear">
	<Tasks>
	<DataProfiling Name="Data Profiling Task" OverwriteDestination="true" >
	<FileOutput ConnectionName="Profiling_<#=_extraction["TABLE_NAME"].ToString()#>.xml"></FileOutput>
	<ProfileRequests>
	<ColumnStatisticsProfileRequest Name="StatisticsReq" ConnectionName="DBCON" SchemaId="XXX" TableId="<#=_extraction["TABLE_NAME"].ToString()#>" />
	<ColumnNullRatioProfileRequest Name="NullRatioReq" ConnectionName="DBCON" SchemaId="XXX" TableId="<#=_extraction["TABLE_NAME"].ToString()#>" />
	<ColumnPatternProfileRequest Name="PatternReq" ConnectionName="DBCON" SchemaId="XXX" TableId="<#=_extraction["TABLE_NAME"].ToString()#>" />
	<CandidateKeyProfileRequest Name="KeyReq" ConnectionName="DBCON" SchemaId="XXX" TableId="<#=_extraction["TABLE_NAME"].ToString()#>">
	<KeyColumns>
	<Column></Column>
	</KeyColumns>
	</CandidateKeyProfileRequest>
	<ColumnValueDistributionProfileRequest Name="ValueDistReq" ConnectionName="DBCON" SchemaId="XXX" TableId="<#=_extraction["TABLE_NAME"].ToString()#>" />
	</ProfileRequests>
	</DataProfiling>
	</Tasks>
	</Package>
	<# } #>
	<Package Name="DataProfiling.Master" ConstraintMode="LinearOnCompletion">
	<Tasks>
	<#foreach (DataRow _extraction in _extract_table_info.Rows) { #>
	<ExecutePackage Name="EPT_<#=_extraction["TABLE_NAME"].ToString()#>">
	<ExternalProjectPackage Package="DP_<#=_extraction["TABLE_NAME"].ToString()#>.dtsx"/>
	</ExecutePackage>
	<#} #>
	</Tasks>
	</Package>
	</Packages>
	</Biml>

view raw gistfile1.xml hosted with ❤ by GitHub

There are however some other alternative solutions, like here: Using the Data Profiling Task To Profile All the Tables in a Database

Remember That The Regex Engine Is Eager

Just a reminder:

Regex is eager and will return the first match in the piped list expressions: (<= |<>|<)

that was important for me for trying to match equality signs, for example: "<>" or "<=" vs "<".

https://www.regular-expressions.info/alternation.html

Developing Custom SSIS Data Flow Component: helpful links

Great resource to start with. A sample source code is also available for download.
http://geekswithblogs.net/Compudicted/archive/2012/03/01/creating-a-custom-ssis-data-flow-component---an-example.aspx

How to work with Connections
https://docs.microsoft.com/en-us/sql/integration-services/extending-packages-custom-objects-data-flow-types/developing-a-custom-source-component

Debugging Custom component
https://docs.microsoft.com/en-us/sql/integration-services/extending-packages-custom-objects/building-deploying-and-debugging-custom-objects

1. Component Project in DEBUG mode
2. register .dll in GAC and copy it in the DTS\PipelineComponents
3. in SSIS project: breakpoint on Pre-Execute of the Data Flow Task. Wait till we hit breakpoint
4. in Component project - Debug - > Attach to process. Select DtsDebugHost.exe (Managed, x64)
5. in Component project - put a breakpoint in code in Validate or PreExecute or ProcessInput
6. in SSIS project: click Continue
7. you should hit the breakpoint in the Component project

Automate with Post-build event: registering .dll in GAC and copying it in the DTS\PipelineComponents
http://toddmcdermid.blogspot.ca/2009/01/custom-component-developing-post-build.html

this worked:
copy "$(TargetDir)$(TargetName).DLL" "C:\Program Files (x86)\Microsoft SQL Server\110\DTS\PipelineComponents\"
gacutil.exe -i "$(TargetDir)$(TargetName).DLL"

Supporting different SQL Server platforms:

Support multi-targeting in your custom components

Getting your SSIS custom extensions to be supported by the multi-version support of SSDT 2015 for SQL Server 2016

Using PerformUpgrade

https://www.red-gate.com/simple-talk/sql/ssis/developing-a-custom-ssis-source-component/

https://blogs.perficient.com/microsoft/2016/04/developing-custom-ssis-component-9-lessons-learned/

old but good - MSIL Disassembler tool

Thanks to a pesky issue with a custom icon in the Custom SSIS Data Flow Component, I found out how useful MSIL Disassembler tool could be!
http://www.sqlis.com/sqlis/post/How-to-add-an-icon-to-your-component.aspx

GAC and debugging

Just a quick note: NEVER deploy your class library in the GAC on your DEV machine - debugging becomes hell, because Visual Studio 2015 referes to the class library in the GAC instead of the one in your Solution.

This is not too easy if you need to use GAC in order to refer to such class in your SSIS Script Component!

So now I have to uninstall it from GAC every time I need to debug

MySQL

Amazing! Just casually researching MySQL, I found out that it was named after co-founder Michael Widenius's daughter, My. And it is posible that My was named after Little My - a character from The Book About Moomin, Mymble and Little My, by Tove Jansson. I think it is super cute!

Michael Widenius has then developed two more DB Management Systems - MariaDB and MaxDB named after his subsequent children.

And he is trying to have MariaDB replace MySQL, after it was acquired by Oracle. MariaDB is aspiring to become a bridge bewteen SQL and NOSQL on top of that...

http://www.vertabelo.com/blog/notes-from-the-lab/mysql-history

Interesting collection of articles on fact table design from Kimball Group

http://www.kimballgroup.com/category/fact-table-core-concepts/