March 24, 2014 at 11:11 am
Guys,
one of the high level requirements for our new Data Warehouse is that the ETL processes are documented WITHIN the DW framework. By this, they mean that they want to be able to query the database and understand
1) if I change this stored procedure, which packages will break
2) Given the name of a package, can you tell me all the source and target tables and all the ETL decisions that were made on that data.
We don't need a full human readable document, nor do we want to store the raw XML for the package but rather a simplified process that shows all the parent-child/sibiling relationships between the control and dataflow tasks and the metadata related to those tasks.
E.g.
Package.dtsx
1.0 - Log Package Start: Calls sproc "USP_Start" returns int to variable "intTaskID"
1.0 to 2.0 on success
2.0 - dataflow task "DFT_GetData"
2.1 - Data source OLE connector: ServerName = "SourceSystem"; userID = "SSISUser"; TableName="MyTable"
2.2 - Derived Columns: Col1 = BatchNumber; Col2 = LoadDate
2.3 - Data Destination Flat File: Filename="C:\datadump\Mydata.txt"
2.0 to 3.0 on Success
3.0 - log Package End Success; Calls sproc "USP_End" with parameter "Status" = "Success"
2.0 to 4.0 on Fail
4.0 - - log Package End Success; Calls sproc "USP_End" with parameter "Status" ="Error"
Has anyone ever done this, or know of a codeplex or commercial tool to do this. I tried to do it in Excel using VBA once and it is extremely difficult because the XML is a mess and containers can be nested indefinitely.
March 25, 2014 at 1:05 am
We don't use this tool, but it promises to do at least some of what you are looking for:
http://www.embarcadero.com/products/er-studio-data-lineage
Not free though ...
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 25, 2014 at 3:00 am
Ouch!
They have a big budget for this project though, so they can afford some nice toys 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply