1

I currently have some standard SSIS packages in SQL Server that load and transform data from CSV files into a SQL Server database.

I would like to capture data lineage for these SSIS packages but am unsure how this can be done. Ideally i don't want to re-write the packages and was hoping if something could be 'switched on' as such. Is there anything built into SSIS/SQL Server or are there any 3rd party tools available?

Any advice would be greatly appreciated. Cheers

Justin
  • 63
  • 1
  • 5
  • 1
    What exactly are you calling "data lineage"? – Tab Alleman Apr 20 '18 at 17:21
  • Depends on what you need. Basic data lineage inside SSIS can be extracted rather easily since DTSX files are just XMLs. However if you want to also represent connections to database, process any SQL queries etc. it gets much harder. There are tools for that ([the company I work for](https://getmanta.com/manta-microsoft-sql-server/) makes one such tool), but they are way more than "just a switch" - and tool recommendations are out of SO scope. – Jiri Tousek Apr 20 '18 at 19:15
  • As data is processed by SSIS various adjustments can be made to the figures (rounding, multiplication, division etc....). These adjustments might be dynamic (based upon other data). I would like the ability to take a final value and look at history (data lineage) of this and see all the adjustments that have been made to get this value. – Justin Apr 23 '18 at 08:27
  • In that case you'll also need to parse the expressions used in SSIS, and possibly also configurations, property expressions, etc. Depending on your specific use case you might be able to do this using static analysis, or you might need to track it dynamically in your SSIS projects and packages as Tab Alleman says in thier answer. SSIS can be very dynamic. – Jiri Tousek Apr 27 '18 at 08:56

1 Answers1

2

As data is processed by SSIS various adjustments can be made to the figures (rounding, multiplication, division etc....). These adjustments might be dynamic (based upon other data). I would like the ability to take a final value and look at history (data lineage) of this and see all the adjustments that have been made to get this value

You would have to program your package to actively store this data in whatever form you want to be able to retrieve it. There is nothing built into SSIS to do this automatically or even to make it especially easy to do.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52