keif888 / sqlservermetadata Goto Github PK

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)

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

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?

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

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]...

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?

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.

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

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

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.

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.

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

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

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'

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

hello

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

Regards

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?

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ß

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)

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)

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

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

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

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.

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?

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

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)

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.

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

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

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

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

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 ?

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

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