keif888 / sqlservermetadata

SQL Server Metadata Toolkit

SQL Server Metadata Toolkit

Downloads here

Project Description

MSDN's SQL 2005 tool kit updated to 2008, 2012, 2014, 2016, 2017 and 2019 for detecting metadata in SQL Server, SSIS, SSRS and SSAS.

This project is an update on the project released on MSDN Code Gallery SQL Server Metadata Toolkit

It has the ability to scan the following versions 2005, 2008, 2008 R2, 2012, 2014, 2016, 2017 and 2019...

Please note that support for Transact SQL capabilities that have been introduced since isn't there. If you encounter a T-SQL statement that doesn't parse, please post it as an issue.

It can scan SSIS, SSAS, SQL Server Databases, and Reporting Services.

It has an "Executor", which allows the user to use a GUI to run the various analysers. If you hover over the buttons at the bottom, the actual command will be shown in the status bar, and you can then copy that command if you so desire...

Release Notes

Beta 28 Fixes issues #35, #37. This release adds SQL 2019 capabilities, and ODBC call handling to the Parser.

Beta 27 Fixes issues #23, #24, #25, #27, #30, #36.

Beta 26 Fixes issue #21. This release also adds missing configuration into the database to allow viewing of the database dependencies for SQL 2014 and above.

Beta 25 Fixes for issues #19 and #20. This release adds the ability for the analysis to ignore SSIS components that are not registered on the machine doing the analysis by treating them as uninteresting.

Beta 24 Further fixes for issue #17 where v0.23.0.0 did not include enough of the DLL's which make up SQL Server SMO capabilities for SQL 2017.

Beta 23 Fixes issue #17 which was caused by not including the SQL Server SMO capabilities for SQL 2017, as Microsoft are no longer packaging this capability into the GAC.

Beta 22 Adds capability to detect .ispac files, and scan them. Adds capability to detect .conmgr and/or .params files, and switch to "hybid mode". In "hybrid mode" the folder is checked for the expected .dtproj files, and these are compiled to .ispac files and scanned. If there are no .dtproj files, then no SSIS packages will be scanned in that folder. If there are no .conmgr or .params files (in sub folders) then standard SSIS scan will be performed.

Beta 21 Corrects issue with Project Deployed packages not seeing the connections or parameters. Also updates the TSQL parser to latest available.

Beta 20 Corrects issues with SSIS Catalog capabilites, adds SQL 2016 and 2017, and utlises the latest MSAGL libraries

Alpha 19 Adds SSIS Catalog capabilities

Alpha 18 corrects issues with missing DLL's, adds encrypted package support, adds multiple SSIS Servers (not SSIS Catalog) capability.

Alpha 17 corrected issues with databases requested to scan not being available, missing schema name for some objects, three part names appearing in reports. It has also added a command line option to return three part names for all database objects found.

Alpha 16 corrected a number of issues with cross database object references and synonyms, single quotes in object names, shared data sets in reports.

Alpha 15 added SQL 2014 support, and changed the installer to WIX

Alpha 14 wasn't released to the general public

Alpha 13 is a feature patch release, which adds SQL 2012 abilities, adds Insert statements (and many others), corrects a Primary Key violation error, adds Print and Save to the viewer.

Alpha 12 is a feature patch release, which corrects the SQL 2005/2008 Reporting Services analysis (so it works), corrects the database create on the first run, so all tables and procedures are created, and adds the ability to scan only one analysis services database.

Alpha 11 is a functionallity release which adds an execution tool, Reporting Services analysis, Database analysis, and tweaks to the Viewer. The DependencyViewer has been changed to use MSAGL to display the graph, with print, and save options, as well as the ability to change the layout that is selected from Top Down (old default) to Left Right, Right Left, and Bottom Up.

Alpha 10 was a feature patch release, which adds the ability specify specific Integration Services folders within Integration Services (as opposed to the file system).

Alpha 9 was a bug patch release which fixes cases where SSIS' AccessMode is incorrectly set (3 instead of 1). Also improves the EXEC parser. It also addresses the issue in previous versions not enumerating the SSIS Components when they are only installed in the x32 SSIS directory (as most component installers do). There are enhancements to add Column names as Attributes, and with the Lookup, Fuzzy Lookup, Derived Column and Multiple Hash components, additional information is added to the Column name attributes.

Alpha 8 was a functionallity release which added SQL Commands from Variables

Alpha 7 was a bug patch release which adds a number of new statements into the Parser. (CAST, EXECUTE, WITH CTE)

Alpha 6 wasn't released to the general public

Alpha 5 now includes additional handling for the Kimball SCD component.

Alpha 4 now processes most SQL Statements to get the names of tables, and link these into the display.

Alpha 3 wasn't released to the general public

Alpha 2 has added handling for Containers, which was missing from the MSDN version. These are now recursively checked for Data Flows.

Alpha 1 wasn't released to the general public The Database has been updated to handle longer names for attributes, and some views have been updated to handle SQL 2005 and SQL 2008 GUIDs.

Known Issues:

The Executor doesn't have the option to change the SSIS server.

Example Screenshot

The following picture shows the Dependency Viewer:

Other Downloads Required:

To be able to analyse SSIS requires either the appropriate version of Visual Studio/BIDS/SSDT, or the appropriate version of SSIS to be installed.

To be able to analyse SQL/AS 2005, without having SQL 2005 installed, requires the SQLSERVER2005_ASAMO10, SQLSysClrTypes and SharedManagementObjects (32 bit). But these are no longer available from Microsoft.

To be able to analyse SQL/AS 2008, without having SQL 2008 installed, requires the SQLSERVER2008_ASAMO10, SQLSysClrTypes and SharedManagementObjects (32 bit) from here: https://www.microsoft.com/en-us/download/details.aspx?id=44272

To be able to analyse SQL/AS 2012, without having SQL 2012 installed, requires the SQL_AS_AMO, SQLSysClrTypes and SharedManagementObjects (32 bit) from here: https://www.microsoft.com/en-us/download/details.aspx?id=56041

To be able to analyse SQL/AS 2014, without having SQL 2014 installed, requires the SQL_AS_AMO, SQLSysClrTypes and SharedManagementObjects (32 bit) from here: https://www.microsoft.com/en-us/download/details.aspx?id=42295

To be able to analyse SQL/AS 2016, without having SQL 2016 installed, requires the SQL_AS_AMO, SQLSysClrTypes and SharedManagementObjects (32 bit) from here: https://www.microsoft.com/en-us/download/details.aspx?id=103444

From 2017 onwards, SQL and Analysis services capability has been provided directly in the download.

sqlservermetadata's Issues

Reading package from SSISDB fails

Hi,

I am trying to read packages from the SSISDB, as I am using project connection managers most packages fail. However the dependency analyzer does not even scan all packages.

I have the following:

Error enumerating packages on SQL Server 'hostname123': Could not find a part of the path 'C:\Users\fmoeller\AppData\Local\Temp\3\SSISMDaf87bdd9-ae00-47e6-b85d-d53c358e1b8a\DWH.ZIP'.
Strack Trace : at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost)
at System.IO.File.InternalWriteAllBytes(String path, Byte[] bytes, Boolean checkHost)
at System.IO.File.WriteAllBytes(String path, Byte[] bytes)
at Microsoft.Samples.DependencyAnalyzer.SSISEnumerator.EnumerateProjectPackages(ProjectInfo project, DirectoryInfo tempDirectory, String server)
at Microsoft.Samples.DependencyAnalyzer.SSISEnumerator.EnumerateSqlPackages(String server, String user, String pwd, String[] rootFolders, Boolean storeThreePartNames, String[] storePackagePasswords)
Committing analysis information to database...Completed.
Looking in the Temp folder I can see that the ZIP archive is created however disappears after just a few seconds.

I'd be willing to test any updates.

regards and thanks
Felix

Run an analysis upon a existing RUN scan ID with "Delta" Mode for ISPAC files analysis

Hello

Imagine that a ispac file (SSIS project) is modified it would be great to update the analysis of this project for an existing RUN. (using ISPAC file name and Timestamp)

The use case of this would be to run an analysis if the program have failed for any reason
Manage the change of an ispac file after a change in dtsx after a development process.

Regards

SSASEnumerator doesn't find DSV-Tables, that are SQL Server Views

SSASEnumerator assumes that DSV-Tables with TableType "View" are "Named Queries" and only reads their "QueryDefinition".
But there are also DSV-Tables with TableType "View", that represent Views from the SQL Server. Those should be treated like DSV-Tables with TableType "Table", so that the Dependency to the SQL Server View can be recorded.

I agree that the relationship should be differentiated for full lineage and transparency. I second that vote.
--Steven Neumersky

Store ISPAC filename + analyse timestamp

hello

Would it be possible to store those information into the SSIS_META database ?
ISPAC filename + analyse timestamp.

Regards

Analysis doesn't support SSIS DB Catalog

This was added to a closed issue:

I am using SQL 2012 and store my packages in the new SSISDB catalog. When I select the server name containing this new SSIS catalog, the dependency executor is still attempting to enumerate the SSIS package from the OLD AREA in SQL SERVER and not the new "Integration Services Catalogs" area (SSISDB)

I know this because the output window tells me this:

Loading SQL package '\Data Collector\SqlTraceUpload'... Error occurred: 'The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.
'
Loading SQL package '\Data Collector\TSQLQueryCollect'... Completed Successfully.
Loading SQL package '\Data Collector\TSQLQueryUpload'... Completed Successfully.
Loading SQL package '\BTSDESQL110\DTS Packages\Copy Database Wizard Packages\CDW_BTSDETSTSQL24_BTSDESQL110_0'... Completed Successfully.
Enumerating Analysis Services metadata.

Am I doing something wrong?

Add Column Level tracking

Capture details on each column that passes through an SSIS package, and not what transformations are applied to it.

Update statements with table alias are not parsed correctly

For updating dimensions we have a lot of update tasks that are similar to:

 update a
       set 
a.UnitofMeasurement=b.UnitofMeasurement,
a.UnitofMeasurementText=b.UnitofMeasurementText
 from [Ods].[DimUnit] a
 join  [Ods].[DimUnitUpdate] b
 on a.UnitKey=b.UnitKey

The are displayed as follows in the Dependency Viewer:

However, for sure there is no table [dbo].[a]...

Avoid parsing other connections type than SQL Server

hello,

Error log show many 46010 error coming from MDX or Oracle...(as package connect to mutiples environments)

Would it be possible to avoid parsing sqlstm from other origins than SQL SERVER ?
Regards

Create Script for LookupConnectionID Table

Script inserts values to 2008.

I updated my script to:

INSERT [dbo].[LookupConnectionID] ([ConnectionGUID], [ConnectionDescription]) VALUES (N'{5F2826BC-648B-4f3e-B930-587F4EF331D4}', N'ODBC 2005')
INSERT [dbo].[LookupConnectionID] ([ConnectionGUID], [ConnectionDescription]) VALUES (N'{9B5D63AB-A629-4A56-9F3E-B1044134B649}', N'OLEDB 2005')
INSERT [dbo].[LookupConnectionID] ([ConnectionGUID], [ConnectionDescription]) VALUES (N'{72692A11-F5CC-42b8-869D-84E7C8E48B14}', N'ADO.NET 2005')
INSERT [dbo].[LookupConnectionID] ([ConnectionGUID], [ConnectionDescription]) VALUES (N'{4CF60474-BA87-4ac2-B9F3-B7B9179D4183}', N'ADO 2005')
INSERT [dbo].[LookupConnectionID] ([ConnectionGUID], [ConnectionDescription]) VALUES (N'RelationalDataSource', N'olap relational data source')
INSERT [dbo].[LookupConnectionID] ([ConnectionGUID], [ConnectionDescription]) VALUES (N'{09AD884B-0248-42C1-90E6-897D1CD16D37}', N'ODBC 2008')
INSERT [dbo].[LookupConnectionID] ([ConnectionGUID], [ConnectionDescription]) VALUES (N'{3BA51769-6C3C-46B2-85A1-81E58DB7DAE1}', N'OLEDB 2008')
INSERT [dbo].[LookupConnectionID] ([ConnectionGUID], [ConnectionDescription]) VALUES (N'{A1100566-934E-470C-9ECE-0D5EB920947D}', N'ADO 2008')
INSERT [dbo].[LookupConnectionID] ([ConnectionGUID], [ConnectionDescription]) VALUES (N'{894CAE21-539F-46EB-B36D-9381163B6C4E}', N'ADO.Net 2008')
INSERT [dbo].[LookupConnectionID] ([ConnectionGUID], [ConnectionDescription]) VALUES (N'{32808317-2AFC-4E1C-A03A-9F8477A3BDBA}', N'ODBC 2012')
INSERT [dbo].[LookupConnectionID] ([ConnectionGUID], [ConnectionDescription]) VALUES (N'{3269FBD7-897B-4CDF-8988-2E1A24B10FBB}', N'OLEDB 2012')
INSERT [dbo].[LookupConnectionID] ([ConnectionGUID], [ConnectionDescription]) VALUES (N'{68FFA586-FFA5-41DC-8EDE-13102087EF33}', N'ADO 2012')
INSERT [dbo].[LookupConnectionID] ([ConnectionGUID], [ConnectionDescription]) VALUES (N'{39CAF1E8-6582-4C31-A5C6-405A8661EEC1}', N'ADO.Net 2012')
INSERT [dbo].[LookupConnectionID] ([ConnectionGUID], [ConnectionDescription]) VALUES (N'{E3D5D606-997B-4EF6-90AD-43483A788CC3}', N'MSOLAP 2012')
INSERT [dbo].[LookupConnectionID] ([ConnectionGUID], [ConnectionDescription]) VALUES (N'{1818FF09-AF4D-4EA8-8C9D-0AB43B5775E5}', N'ODBC 2014')
INSERT [dbo].[LookupConnectionID] ([ConnectionGUID], [ConnectionDescription]) VALUES (N'{F3F3005C-C3CB-4C61-B2A9-056035E4D8F2}', N'OLEDB 2014')
INSERT [dbo].[LookupConnectionID] ([ConnectionGUID], [ConnectionDescription]) VALUES (N'{FFEDAAC9-D6BD-4E6B-90AB-D4D296B5096A}', N'ADO 2014')
INSERT [dbo].[LookupConnectionID] ([ConnectionGUID], [ConnectionDescription]) VALUES (N'{D5353B56-34DA-4C97-AC94-722B91013E89}', N'ADO.Net 2014')
INSERT [dbo].[LookupConnectionID] ([ConnectionGUID], [ConnectionDescription]) VALUES (N'{05B2302B-4C20-43FD-92B3-3A067A037436}', N'MSOLAP 2014')

WIP - Confirming:
'F4D9470A-E60A-4BAD-ACC6-2E3AA759E0BD', 'MSOLAP 2016'
'6D9EBD9B-8087-4C5B-83BF-F48E7B095E9D', 'OLEDB 2016'
'4696FBDD-C1BC-4009-AABD-D2AE31E24F0D', 'ADO 2016'
'5C9E27BC-DF64-48F1-855E-92EF415C638C','ADO.Net 2016'

no .exe file per wiki documentation

Hello, there is no .exe file in SQLServerMetadata-master.zip. Please explain how to install this

Unexpected error occurred: GenericArguments[0]

This issue is split from Issue #17
The error below occurred when trying to scan a SSIS 2017 Catalog.
Enumerating Integration Services metadata.
Unexpected error occurred: GenericArguments[0], 'Microsoft.SqlServer.Management.IntegrationServices.Catalog', on 'Microsoft.SqlServer.Management.Sdk.Sfc.SfcCollection`3[T,K,ParentT]' violates the constraint of type parameter 'T'.
Stack Trace:
at Microsoft.Samples.DependencyAnalyzer.SSISEnumerator.EnumerateSqlPackages(String server, String user, String pwd, String[] rootFolders, Boolean storeThreePartNames, String[] storePackagePasswords)
at Microsoft.Samples.DependencyAnalyzer.Program.EnumerateSSIS(DependencyArguments dependencyArguments, Repository repository)
at Microsoft.Samples.DependencyAnalyzer.Program.Main(String[] args)

Execute SQL Task with project connection manager fails

When a package contains an "Execute SQL Task" which references a project connection manager SSISEnumerator fails on EnumerateSqlTask.

I tested it for packages targeting SSIS 2012 and received the stracktrace below when analyzing my SSIS source directory. SSIS project is in project deployment mode.

Stacktrace:
Enumerating projects...done.
Processing Project package 'XY'... Error The connection "{FD08C64A-...}" is not found. This error is thrown by Connections collection when the specific connection element is not found.
occurred whilst attempting to handle ispac C:\Development..
With stack trace at Microsoft.SqlServer.Dts.Runtime.Connections.get_Item(Object index)
at Microsoft.Samples.DependencyAnalyzer.SSISEnumerator.EnumerateSqlTask(Package package, String location, Int32 packageRepositoryID, TaskHost taskHost) in C:\Users\xy\Source\Repos\SQLServerMetadata\DependencyAnalyzer2008\SsisEnumerator.cs:line 1132
at Microsoft.Samples.DependencyAnalyzer.SSISEnumerator.EnumerateTask(Package package, String location, Int32 packageRepositoryID, DtsContainer dtsContainer) in C:\Users\xy\Source\Repos\SQLServerMetadata\DependencyAnalyzer2008\SsisEnumerator.cs:line 1062
at Microsoft.Samples.DependencyAnalyzer.SSISEnumerator.EnumerateTask(Package package, String location, Int32 packageRepositoryID, DtsContainer dtsContainer) in C:\Users\xy\Source\Repos\SQLServerMetadata\DependencyAnalyzer2008\SsisEnumerator.cs:line 1086
at Microsoft.Samples.DependencyAnalyzer.SSISEnumerator.EnumeratePackage(Package package, String location) in C:\Users\xy\Source\Repos\SQLServerMetadata\DependencyAnalyzer2008\SsisEnumerator.cs:line 1226
at Microsoft.Samples.DependencyAnalyzer.SSISEnumerator.EnumerateIntegrationServicePack(String integrationServicePack, String locationName) in C:\Users\xy\Source\Repos\SQLServerMetadata\DependencyAnalyzer2008\SsisEnumerator.cs:line 628.
Committing analysis information to database...Completed.

Hangs on some SSIS solutions

This issue is split from #17.
When it was scanning .dtproj it would hang on one of my solutions. I have no error message or log, it just hung. It must be something about that particular project cause it was able to scan others. This is more an FYI at this point.

tool cannot see variable defined at data flow task level and used by source

I have one SSIS 2012 package with a data flow task using as source a SQL query defined in string variable.
This variable is not defined at package level but at Data Flow Task level.
but the code is looking for variable at package level only.
Exception is triggered by GetVariable method.

Long SQL code in variable expression : Objecname column size

Hi Keith,
In an SSIS package we have long queries as expression in string value.. More than 1000 chars, and even more than 4000. those values are stored in Objectname column, defined as NVARCHAR(1000)
This lead to following issue:
"Committing analysis information to database...Unexpected error occurred: The given value of type String from the data source cannot be converted to type nvarchar of the specified target column."
We have change the column definition in Repository.cs
sqlCommand.CommandText = "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Objects]') AND type in (N'U'))\r\n" + "BEGIN\r\n" + "CREATE TABLE [dbo].[Objects](\r\n" + " [RunKey] [int] NOT NULL,\r\n" + " [ObjectKey] [int] NOT NULL,\r\n" + " [ObjectName] [nvarchar](MAX) NULL,\r\n" +
What do you think about it?

Error Number: 46010

Hi there. Any idea why I get 100's of 46010 errors when running the dependency analyzer?

localIColumn.CustomPropertyCollection["JoinToReferenceColumn"].Value is null

Hi Keith,

I mentioned an Exception in #4 i was now able to narrow it down. However I am not sure what a "correct" fix is:

Processing Project package 'STG_TL_xxx'... Error enumerating packages on SQL Server 'x.x.x.x': Cannot perform runtime binding on a null reference
Strack Trace :   at CallSite.Target(Closure , CallSite , Object )
   at System.Dynamic.UpdateDelegates.UpdateAndExecute1[T0,TRet](CallSite site, T0 arg0)
   at CallSite.Target(Closure , CallSite , Object )
   at Microsoft.Samples.DependencyAnalyzer.SSISEnumerator.EnumerateDataFlowComponent(Int32& packageRepositoryID, Package package, TaskHost taskHost, String packageLocation, Int32& dataFlowRepositoryObjectID, Dictionary`2& componentIDToSourceRepositoryObjectMap, Dictionary`2& componentIDToRepositoryObjectMap, IDTSComponentMetaData100 component)
   at Microsoft.Samples.DependencyAnalyzer.SSISEnumerator.InspectDataFlow(IDTSPipeline100 pipeline, Int32 packageRepositoryID, Package package, TaskHost taskHost, String packageLocation)
   at Microsoft.Samples.DependencyAnalyzer.SSISEnumerator.EnumerateTask(Package package, String location, Int32 packageRepositoryID, DtsContainer dtsContainer)
   at Microsoft.Samples.DependencyAnalyzer.SSISEnumerator.EnumerateTask(Package package, String location, Int32 packageRepositoryID, DtsContainer dtsContainer)
   at Microsoft.Samples.DependencyAnalyzer.SSISEnumerator.EnumeratePackage(Package package, String location)
   at Microsoft.Samples.DependencyAnalyzer.SSISEnumerator.EnumerateProjectPackages(ProjectInfo project, DirectoryInfo tempDirectory, String server)
   at Microsoft.Samples.DependencyAnalyzer.SSISEnumerator.EnumerateSqlPackages(String server, String user, String pwd, String[] rootFolders, Boolean storeThreePartNames, String[] storePackagePasswords)

After adding a lot of debugging information, i could find the error in the expression

repository.AddAttribute(componentRepositoryID, localInput.Name + " [" + localIColumn.Name + "] [ID: " + localIColumn.ID.ToString() + "]", "From [" + localIColumn.UpstreamComponentName + "] " + FormatColumnDescription(localIColumn.Name, localIColumn.DataType, localIColumn.Length, localIColumn.Precision, localIColumn.Scale) + " Reference Column [" + localIColumn.CustomPropertyCollection["JoinToReferenceColumn"].Value.ToString() + "]");

https://github.com/keif888/SQLServerMetadata/blob/master/DependencyAnalyzer2008/SsisEnumerator.cs#L1141

The problem is that localIColumn.CustomPropertyCollection["JoinToReferenceColumn"] is null.

Changing this to
localIColumn.CustomPropertyCollection["JoinToReferenceColumn"].Value != null ? localIColumn.CustomPropertyCollection["JoinToReferenceColumn"].Value.ToString() : "null"

Makes the reading of SSIS packages work for me.

Thanks

No Installer - Please advise

Forgive me but I cannot figure out how to install this. I have installed a version from codeplex that has SS 2014 but I am trying to upgrade to this version. I have it opened in Visual Studio but that has been a dead end as far as getting it running. I cannot locate an exe or msi file. Please advise. Thanks

Intermediate commit on each ISPAC...

hello,
Great tool and I'm glade to use it and see that some other use it to...

I use it to analyse 1200 dtsx files upon 100 ispac files. I build a pivot table enabling the analysis of all the material stored in the SSIS_META db by adding some objects in the database. (i would be happy to share if you want).

For the time being I'm wondering if this is possible to have an intermediate commit after each ISPAC analysis file as the analysis phase take around 3 hours and i get a timeout at final commit ?

Or is it possible to add (and replace would be the cherry on top of the cake) ispac to existing RUN ID ?

What do you thing about this features ? Do you think this if feasible ? and can you help me to locate the part of the code i would have to change....

Of course i already to look at it by myself and make some changes but would be great if someone can help me to understand some part of the architecture of the application.

Reagrds

How to build this myself

Hi Keith,

I wanted to investigate my exeception myself. However, how do i build the Dependency Analyzer correctly?

I was able to get Visual Studio to produce the "DependencyAnalyzer.exe" in either the Release or Debug project configuration. However, copying them into the stuff the installer gave me I get a lot of missing DDL errors. e.g. Microsoft.SqlServer.Diagnostics.STrace.dll, Microsoft.SqlServer.SqlClrProvider.dll, Microsoft.SqlServer.Management.IntegrationServicesEnum.dll.

How is the build for the setup package done, which does not need these DLLs to be explicitl copied to the folder?

Thanks for your support.
Felix

SSIS Package more than 22 throw StackOverflowException

Hi Keith,

I have gone through your meta data harvester for SQL and it is really awesome. I see there is a limitation when I try to analyze SSIS packages. It would be nice to be able to analyze it on Project level under SSIS. The main issue is that I got following Stack over flow error. There are more than 200 packages and it occurs after 22 packages. Can you please look into it? Please see the attached file.
SSISMetaDataErr.txt

SSISMetaDataErr.txt

sqlStatement.getTableNames() not returning all tables used

Hi, as I was comparing the table name extraction from the TSqlParser (which uses Microsoft.SqlServer.TransactSql.ScriptDom) with an extraction I once wrote (which uses Microsoft.SqlServer.Management.SqlParser). I realized one issue, where the TSqlParser is not returning the correct tables names.

Example (see query.txt attached):

• My parser recognized 15 distinct tables
• TSqlParser recognized 14 distinct tables. A nested query within a column was not successfully discovered (Table9 in the attachment)

Expected result	Actual result
#TempTable1	#TempTable1
#TempTable2	#TempTable2
#TempTable3	#TempTable3
#TempTable4	#TempTable4
#TempTable5	#TempTable5
#TempTable6	#TempTable6
Table1	Table1
Table2	Table2
Table3	Table3
Table4	Table4
Table5	Table5
Table6	Table6
Table7	Table7
Table8	Table8
Table9	-

---

Sample code

SqlStatement sqlStatement = new SqlStatement();
if (sqlStatement.ParseString(mySqlQuery))
{
// Sorry, I am lazy so I aggregate the result and compare it in Excel
string tableNames = sqlStatement.getTableNames().OrderBy(p => p).Aggregate((i, j) => i + "\n" + j);
}

---

query.txt

(Edit: 14.11.2018, false query.txt was uploaded. Find attached the correct one)

Please update DependencyAnalyzer for SQL Server 2019

Hi, is it possible to use DependencyAnalyzer for SQL Server 2019?
So please update the DependencyAnalyzer to support SQL Server 2019, thanx!

Invalid column name 'data'

Hello,

I have two questions please :

1/ Is the DependencyAnalyzer able to find dependencies directly from the SSIS Catalog ? If so, how indicate that parameter?

2/ While showing results, i get always this message : Unhandled exception has occured in your application.

Invalid column name 'data'

************** Exception Text **************
System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'data'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
....

Do you have any idea to resolve this issue and get a Dependency between SSIS Packages and Tables ?

The data from the scan is not being written to the repository database.

This issue is split from #17
The data from the scan is not being written to the repository database. The object and RunScan tables are blank. No errors showing up to indicate there is an issue. Repo is on a 2017 server.

How to avoid duplicate entries for same table in objects table?

When SSIS project folders and databases are analyzed two entries are saved to dbo.objects table for the same database object.

It looks like table from SSIS package does not contains [dbo] schema in the name column and the corresponding entry from database analysis seems to contain [dbo] schema. In consequence the relationships before my object from "Relational databases" can't be displayed in the "Dependency Viewer". I can see two entries with different IDs in SSISMeta dbo.objects table.

How can this be avoided?

SSIS Connections Not Found in Analyzer - Project Connections

When a Project Connection is Used, analyzing the package fails with the following error.

Loading file package 'XXXXXXXXXXXX.dtsx'... Error occurred: 'The connection "{4EC7FAD8-B346-4C08-830D-5470BED82476}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

I can confirm that the *.conmgr files exist in the directory.

Analyzer moves on to other packages and completes those successfully when they dod not use Project Connections.

I am encountering the exact same issue when using SQL against the XML since the connection in not referenced in the package except through the object that references it. I think there needs to be a subprocess that examines connections referenced in the objects then enumerating the directory for conmgr files for the properties.

Execute SQL task - SQL command from variable built from expression

I've been trying the tool out and I'm quite impressed. I have noticed one thing that doesn't seem to work though - we have some instances where we have an Execute SQL Task which runs a SQL statement from a variable. The variable is constructed using an expression
and is thus dynamic. The tables referenced by these statements are not shown as dependencies in the viewer.
 
Is there a setting that I can change or argument I can specify for the analyzer to try to parse these kinds of statements?

can't open this url : http://archive.msdn.microsoft.com/SqlServerMetadata

can't open this url : http://archive.msdn.microsoft.com/SqlServerMetadata
So how to download the installation package

sqlStatement.getTableNames() not returning all tables used if you identify insert statement with nested derived tables

Hi @keif888
When the insert statement is identified, not all tables are returned when a derived-table is used.
I only get two tables back, instead of 4.

I also took the example of Microsoft, with the same effect.
Or show the SQL Script in attachment.
SQLQuery1.sqlSample Insert Into.txt

https://docs.microsoft.com/de-de/sql/t-sql/statements/insert-transact-sql?view=sql-server-2017#v-inserting-data-returned-from-an-output-clause

Where is my mistake? Which classes do I have to use to analyze nested derived tables?

Vielen dank für deine Unterstützung .

Viele Grüße Frank Kreß

2017 Error Enumerating Database - Could not load file or assembly

I am receiving the following error message when trying to scan a SQL Server 2017 (with a 2017 Repo). I have tried this on two machines and I am getting the same error on both (windows 10 machines). The 2014 and 2016 analyzer worked fine. I tried in the GUI and command line and received the error both places. I have SSMS 17.7 installed locally. I googled the error for a while thinking it was my local but than tried on a second machine.
Unexpected error occurred: Could not load file or assembly 'Microsoft.SqlServer.Smo, Version=14.100.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
Stack Trace:
at Microsoft.Samples.DependencyAnalyzer.SQLDBEnumerator.EnumerateDatabase(String dbConnection, Boolean storeThreePartNames)
at Microsoft.Samples.DependencyAnalyzer.Program.EnumerateDatabase(DependencyArguments dependencyArguments, Repository repository)
at Microsoft.Samples.DependencyAnalyzer.Program.Main(String[] args)

Error analyzing SSIS 2012 packages with project connection managers

Hi there.

I tried out analyzing some SSIS packages and tables in my SQL Server 2012 test environment and ended up with the following error:

Error occurred: 'The connection "{B1505881-47D0-4DE3-9B2A-77F02CD58D9F}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

I analyzed the packages using file folders, not SSIS folders (I did not find out the right syntax for analyzing my SSISDB-deployed projects yet, any advise here?) and the connection is a project connection manager. When I convert it to a package connection manager everything works fine.

Am I doing something wrong or are project connection managers not supported by now (or only from SSISDB)?

Best regards and thank you for this great tool!

Boris

Order of execution of tasks within an SSIS dtsx file

Hi,
I need to analyze the order of excecution of tasks within an SSIS dtsx files, and their dependency with different files dtsx in a SSIS project.

Best regards
Federico

Execution result to be stored in db table.

hello

Would it be possible to store execution result in a db table saying package is ok or not :

run_key/project/packages/analysis_result (0,1)/Run_Date_Time.

with such table we can focus on packages having some issues and even be informed each day that something goes wrong and fix it.

Regards