Memorable MemberSSIS Catalog DB Dashboard
This is an amazing job you have done. We are in the process of selecting an ETL and I am afraid of the black box effect of SSIS. This is a way to mitigate. Do you think it could work at field level to do data lineage?
The answer is probably no as it would require to parse each package code, but we never know :)
Thanks
- stretcharm
Yes it can be tricky to get the full lineage of SSIS. The SSISDB Catalog doesn't hold this level of data, but it's there in the pacakge xml.
Probably the ideal solution is to use metadata and build packages using BIML. This way your lineage design builds the package so you get documentation and generate the code. BIML is not going to work with complex SSIS requirements though.
Doc Xpress does a decent job of extracting the SSIS guts for documentation. It has lineage functions, but this not as useful as it sounds.
http://help.sentryone.com/docxpress/Index.html?SSISPackage.html
https://www.sentryone.com/products/pragmatic-workbench/doc-xpress/sql-metadata-management
You can also extract code from SSIS using powershell
We are moving to a metadata management of package and source dependancies. Eventually we may add column level lineage, but for now we are doing it on a table/data set level. This will allow us to see which target areas are impacted by any source system issues.
