Home
The Dependency Executor provides a user interface to generate the commands to execute all the other tools (hover over the buttons for the command to be populated into the status bar), and to execute the commands and pipe the output back into an output window.
The DependencyAnalyzer can read the contents of your SSIS packages, either from the file system, from the SSIS Service, or from the SSIS Catalog, and find all data flows within them. It then gathers the metadata about these data flows, what they do, what they connect to, and column information within the data flow.
The DependencyAnalyzer can read the contents of your Analysis Services, and find all the databases within it. It then parses the information within to gather what data sources are used for the database, and also cubes, data source views, and dimensions. At this stage column information is not gathered...
The DependencyAnalyzer can read the contents of your reporting server, and retrieve data sources, and tables/functions/procedures used within the Queries.
The DependencyAnalyzer can connect to SQL Server's, and retrieve dependency information about databases that are requested to be analyzed.
The DependencyAnalyzer can scan one database on an Analysis Server, by including the Initial Catalog=Database setting in the connection string. Leaving this parameter out will scan all databases on the Analysis Server (old behaviour before Alpha 12).
The DependencyViewer will display graphically the information gathered by the DependencyAnalyzer. It can show Analysis Services, Integration Services, Relational Databases, Files, and Reports that are used.
Within the Analysis Services section, all servers that were analysed will be shown as separate expandable lists. Within each server, each database will be shown, and within each database, the Cubes, Data Sources, Data Source Views, and Database Dimensions will be shown. When you drill down into these, you will be able to see the data flows from source table to target dimension/partition etc.
Within the Integration Services section, all the SSIS packages that were loaded will be shown. Within each SSIS Package, all the data flow tasks that exist will be shown, with the tasks used within that data flow shown.
Within the Relational Database section all database tables that are accessed by either Analysis Services database or Integration Services packages that have been analysed will be listed. You can drill down into the tables, and see all items that use these tables graphically.
Within the Files section, all files that are accessed by either Analysis Services database or Integration Services packages that have been analysed will be listed. You can drill down into the files, and see all items that use these files graphically.
Within the Reporting Servers section all reporting servers that were analysed will be displayed, with drill down to all the reports.
There is now the ability to limit the number of parents and children of the selected object to display.
Right clicking on an object in the graph, and selecting Find Object, will locate that object in the Tree, and make it the current object for limiting Parents and Children.
To use this tool, you will have to run the msi file associated with the release. This will deploy the command line tools to execute analysis for SSIS, SSRS, SSAS and SQL Server versions 2005 through 2017, and the reports that can be loaded into a Reporting Server.
Before executing the tool, you will need to create a database in and SQL Server instance. The first execution of the Dependency Analyzer will create the required objects in this database.
Now you can analyse your SSIS, Analysis Services, Databases or Reporting Server items. To run the analysis execute the appropriate DependencyAnalyzer.exe (sorry about the American Spelling), specifying the server and database where the meta data is to be saved, and the other parameters.
By default the SSIS and Analysis Services scans will run using a default connection string. Use the Skip parameters below to disable these scans as you desire.
DependencyAnalyzer.exe /? will return the following:
/depDb:<string> ADO.Net SqlConnection compatible connection string to dependency database location. Default value:'Server=localhost;database=SSIS_Meta;Integrated Security=SSPI;' (short form /d)
/folders:<string> Root folders of file system packages. (short form /f)
/ssisFolders:<string> Root folders of SSIS datbase system packages. (short form /sf)
/isDbServer:<string> SQL Servers where SSIS packages are stored. If you need to use different passwords per server, use "Server=servername;User=username;Password=pwd" Default value:'localhost' (short form /i)
/isDbUser:<string> SQL Server user that has access to stored SSIS packages (short form /iu)
/isDbPwd:<string> SQL Server password for getting access to stored SSIS packages (short form /ip)
/isPkgPwd:<string> Passwords to access SSIS Packages. (short form /pp)
/asCon:<string> AMO compatible connection string to Analysis Services. Default value:'Provider=msolap;Data Source=localhost;' (short form /a)
/recurse[+](-) Whether to recurse file system sub folders when enumerating objects. Default value:'+' (short form /r)
/batchMode[+](-) Whether to start execution without asking the user to continue. Default value:'-' (short form /b)
/skipSQL[+](-) Whether to skip enumerating packages in SQL Server. Default value:'-' (short form /s)
/skipSSIS[+](-) Whether to skip enumerating packages completely. Default value:'+' (short form /ss)
/skipAS[+](-) Whether to skip enumerating Analysis Services objects. Default value:'+' (short form /sa)
/matchDBOnly[+](-) Whether to match on database name only for connection strings. Default value:'-' (short form /m)
/dbPrefix:<string> Database Prefixes to exclude when matching on database names. (short form /dp)
/clearDatabase[+](-) Remove all records from the database on execution. Default value:'-' (short form /clearDB)
/dbToScan:<string> SqlClient SqlConnection compatible connection string to SQL Server databases to analyse.
Server=(local);Database="AdventureWorks";Integrated Security=SSPI (short form /d2s)
/skipRS[+](-) Whether to skip enumerating Reporting Services. Default value:'+' (short form /sr)
/ReportUrl:<string> Reporting Services URL with ? and Path to Enumerate
eg. http://localhost/reportserver?/ (short form /rpt)
/threePartNames[+](-) Whether to store object names as three part names [dbname](dbname).[schema](schema).[object](object) Default value:'-' (short form /tpn)
@<file> Read response file for more options
The /folders:, /ssisFolders:, /isDbServer:, /isPfgPwd:, /asCon:, /dbPrefix:, and /ReportUrl: parameters can be specified many times, but each value must be unique.
For Example: This command will connect to a local named instance of SQL Server (SQL2008EE) using integrated security and the database BIMetaData, and get all the Analysis Services information from the local instance of AS. This runs in Batch mode, recursion is turned off, "SQL Server" (SSIS Service) is turned off, File System SSIS is turned off...
DependencyAnalyzer /d:"Server=.\SQL2008EE;database=BIMetaData;Integrated Security=SSPI;" /r- /b+ /s+ /skipSSIS+ /asCon:"Provider=msolap;Data Source=.\SQL2008EE;"
This command will connect to a local instance of SQL Server (SQL2008EE) using integrated security and the database BIMetaData, and get all the SSIS packages stored in "D:\Source\Projects\SSIS". This runs in prompt mode, recursion is turned off, "SQL Server" (SSIS Service) is turned off, Analysis Services is turned off, Datasources to Database Names only is turned on, the prefixes (dev0, dev1, sys0, uat0 and prd0) are all removed from the database names if found...
DependencyAnalyzer.exe /d:"Server=.\SQL2008EE;database=BIMetaData;Integrated Security=SSPI;" /r- /s+ /skipAS+ /f:"D:\Source\Projects\SSIS" /m+ /dbPrefix:dev0 /dbPrefix:dev1 /dbPrefix:sys0 /dbPrefix:uat0 /dbPrefix:prd0
Once you have analysed your packages, you then execute the DependencyViewer.exe program to show the results. DependencyViewer.exe /? will return the following:
/depDb:<string> ADO.Net SqlConnection compatible connection string to dependency database location. Default value:'Server=localhost;database=SSIS_Meta;Integrated Security=SSPI;' (short form /d)
@<file> Read response file for more options
For Example: This command will connect to a local named instance of SQL Server (SQL2008EE) using integrated security and the database BIMetaData...
DependencyViewer /d:"Server=.\SQL2008EE;database=BIMetaData;Integrated Security=SSPI;"
The following picture shows the output from this tool with stuff hidden to protect the innocent.
