Fri 28 Mar 2008
Some of you may have visited the link to Microsoft’s whitepaper on the BI Metadata Toolkit only to find the referenced samples download link is not working. Many of us have always had a copy of the whitepaper, but not the samples download. We’ve received that missing download and are sharing it here. The folks at Microsoft are aware of the bad samples download link and are working to get that resolved.
The whitepaper focuses on sharing metadata between Microsoft’s BI tools and is a very good read. It touches on what is “metadata,” how can you use it, where to use it, and most importantly *why* you’d want to use it. Sarbanes-Oxley is mentioned and is important to consider when deploying SSIS solutions. Sarbanes-Oxley (a.k.a. the knee-jerk reaction to the Enron debacle), while broad in nature, basically boils down to enforcing accountability and separation of duties. The BI Metadata Toolkit can help in ensuring SOX compliance.
As part of the samples download, Microsoft includes the following tools:
- Dependency Analyzer: loads into a database lineage data between SSIS, SSAS, and SQL Server
- Dependecy Viewer: graphically see dependencies from the above tool
- Data Source View: A DSV for use in Reporting Services (SSRS) to view dependency repository
- Lineage Repository: A database called SSIS_META that can be used to house metadata from nearly any system
- Reports
- Report Model: For use with Report Builder to allow creating ad-hoc reports
- Integration Services Samples: Sample packages to start auditing and viewing lineage on
BI Metadata Toolkit Whitepaper (.doc – 994 kb)
BI Metadata Toolkit Samples (.msi – 311 kb)
September 8th, 2008 at 9:05 am
Hi,
Thanks for this mirror.
It’s a pity that when you use not native components in a package the DependancyAnalyser will not be able to do it’s job.
Is there another tool more recent ?
a+, =)
-=Clement=-
Configuration :
BIDS 2005
July 30th, 2009 at 5:42 am
After I followed the instructions and populated the SSIS_META database I hashed together the following script so I can check for dependencies on a particular field before I change it:
DECLARE @LookupColumn varchar(200)
DECLARE @LookupTable varchar(200)
DECLARE @Database varchar(200)
SET @LookupColumn = ‘ColumnName’
SET @LookupTable = ‘TableName’
SET @Database = ‘DatabaseName’
DECLARE @Statement NVarchar(Max)
SET @Statement = ‘declare @dbname sysname ‘
+ ‘declare @objid int ‘
+ ‘declare @sysobj_type char(2) ‘
+ ’select @objid = object_id, @sysobj_type = type from ‘ + @Database + ‘.sys.all_objects where name = ”’ + @LookupTable + ”’ ‘
+ ‘if exists (select * from ‘ + @Database + ‘.sys.all_columns where object_id = @objid) ‘
+ ‘begin ‘
+ ‘declare @numtypes nvarchar(80) ‘
+ ’select @numtypes = N”tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney” ‘
+ ’select ”Column_name” = name, ‘
+ ‘ ”Type” = type_name(user_type_id), ‘
+ ‘ ”Length” = convert(int, max_length), ‘
+ ‘ ”Prec” = case when charindex(type_name(system_type_id), @numtypes) > 0 then convert(char(5),IsNull(ColumnProperty(object_id, name, ”precision”),”0”)) else ”0” end, ‘
+ ‘ ”Scale” = case when charindex(type_name(system_type_id), @numtypes) > 0 then convert(char(5),OdbcScale(system_type_id,scale)) else ”0” end, ‘
+ ‘ ”Nullable” = case when is_nullable = 0 then ”false” else ”true” end ‘
+ ‘from ‘ + @Database + ‘.sys.all_columns where object_id = @objid AND [name] = ”’ + @LookupColumn + ”’ ‘
+ ‘end ‘
exec(@Statement)
SELECT O.ObjectName,
A.ObjectAttrValue As ObjectDesc,
Oi.ObjectName,
Oi.ObjectDesc,
Oii.ObjectName,
Oii.ObjectDesc,
Oiii.ObjectName,
Oiii.ObjectDesc,
Oiiii.ObjectName,
Oiiii.ObjectDesc
FROM Objects O
INNER JOIN ObjectAttributes A ON O.ObjectKey = A.ObjectKey
LEFT OUTER JOIN ObjectDependencies ODi ON ODi.TgtObjectKey = O.ObjectKey
LEFT OUTER JOIN Objects Oi ON ODi.SrcObjectKey = Oi.ObjectKey
LEFT OUTER JOIN ObjectDependencies ODii ON ODii.TgtObjectKey = Oi.ObjectKey
LEFT OUTER JOIN Objects Oii ON ODii.SrcObjectKey = Oii.ObjectKey
LEFT OUTER JOIN ObjectDependencies ODiii ON ODiii.TgtObjectKey = Oii.ObjectKey
LEFT OUTER JOIN Objects Oiii ON ODiii.SrcObjectKey = Oiii.ObjectKey
LEFT OUTER JOIN ObjectDependencies ODiiii ON ODiiii.TgtObjectKey = Oiii.ObjectKey
LEFT OUTER JOIN Objects Oiiii ON ODiiii.SrcObjectKey = Oiiii.ObjectKey
WHERE A.ObjectAttrName = ‘QueryDefinition’ AND
A.ObjectAttrValue Like ‘%’ + @LookupColumn + ‘%’ AND
A.ObjectAttrValue Like ‘%’ + @LookupTable + ‘%’
August 7th, 2009 at 7:36 am
Is there anything similar for SQL Server 2008? Utilizing the SSIS native components?
March 3rd, 2010 at 5:07 am
Hey, I like you blog, I\’d really love to write an article if I could.