how to do impact analysis in ssis packages to find database tables and stored procedures

BIDocumenter is the only commercial product currently doing very accurate Impact Analysis across full BI Stack.

Performing Impact Analysis can be complex because of the following facts

• You have to read all stored procedures, tables, triggers and virtually any scripts which can refer certain sql database objects
• You have to read all SSRS Reports and find all datasets to capture sql statements
• Parse all sql statements and findout 3 part or 4 part naming convension to find object releationships
• Scan all objects in SSIS Packages to findout which stored proc , sql table of user defined functions they are using
• Represent all objects in a visual way so it make sense which is source and which is target.

If you already have BI Documenter installed then fine if not then download from here.

Launch BIDocumenter after installing it.

On very first screen you will be asked to setup Database to store documentation complete that step

Create Solution and Click Next
Add SQL Database, Cubes, Reports and SSIS Packages you want to Document
Now click next twice to start snapshot process
Once snapshot is completed open Impact Analysis tool to view object relationships
Right click on any objects on various tabs and render diagram to see relationships between objects.

 One problem with this window is you dont have flexibility to slice and dice your metadata  so here is the script to find objects you need to know. You can modify below script to fit you r need to find out all objects rather than only objects releated to SSIS package. Just remove

How to find tables and stored procs used in SSIS Package

Declare @SOLUTIONID int
Declare @PKG_NAME varchar(100)
Declare @SOLUTION_NAME varchar(100)
----------------------------------
set @SOLUTIONID=-1
 -- OR --
set @SOLUTION_NAME='%'  --<<<< Enter package name or leave '%' for all solutions
----------------------------------
set @PKG_NAME='%'  --<<<< Enter package name or leave '%' for all packages
----------------------------------

--SELECT * FROM SOLUTIONS
/*
SELECT distinct NAME FROM IS_PACKAGES
*/
SELECT PACKAGEID,NAME,VERSIONID
into #tmpPkgList
FROM IS_PACKAGES 
WHERE [VERSIONID] in (
	SELECT Max([VERSIONID]) FROM VERSIONS WHERE SOLUTIONID 
	IN (select SOLUTIONID from SOLUTIONS WHERE NAME LIKE @SOLUTION_NAME OR SOLUTIONID=@SOLUTIONID) 
	GROUP BY SOLUTIONID
	)	
and NAME LIKE @PKG_NAME

--select * from #tmpPkgList

SELECT
     [SOURCENAME] , [TARGETNAME]
    ,[SOURCETYPEFRIENDLY] , [TARGETTYPEFRIENDLY]
    ,[SOURCEINFO] ,[TARGETINFO]
    , b.NAME as PACKAGENAME
INTO #tmpObjMap
FROM OBJECTMAPPINGS a
JOIN #tmpPkgList b on a.VERSIONID=b.VERSIONID

-- Modify or comment this FIlter to get all objects
WHERE ((TARGETTYPEFRIENDLY LIKE 'IS_%' AND SOURCETYPEFRIENDLY LIKE 'SS_%') 
		or (TARGETTYPEFRIENDLY LIKE 'SS_%' AND SOURCETYPEFRIENDLY LIKE 'IS_%')
	 )

--select * from #tmpObjMap

select PackageName,ObjectType,ObjectName,ObjectMapType from 
(
select Distinct SOURCENAME as ObjectName
	,'Source' as ObjectMapType
	,SOURCETYPEFRIENDLY as ObjectType
	,PACKAGENAME as PackageName 
from #tmpObjMap where SOURCETYPEFRIENDLY <> 'IS_PACKAGE'
UNION ALL
select Distinct TARGETNAME as ObjectName
	,'Target' as ObjectMapType
	,TARGETTYPEFRIENDLY as ObjectType
	,PACKAGENAME as PackageName 
from #tmpObjMap where TARGETTYPEFRIENDLY <> 'IS_PACKAGE'
) as a
Group by PackageName,ObjectType,ObjectName,ObjectMapType
Order by PackageName,ObjectType

--select * from #tmpPkgList
--select top 10 * from BIDocumenter.dbo.OBJECTMAPPINGS

drop table #tmpPkgList
drop table #tmpObjMap


/*
--select distinct TARGETTYPEFRIENDLY FROM OBJECTMAPPINGS order by TARGETTYPEFRIENDLY
Source or Target Mapping Types 
===============================
AS_ACTION
AS_ACTIONS_DT_CUBEATTRIBUTE
AS_ACTIONS_DT_MEASURE
AS_CALCULATIONPROPERTY
AS_COMMAND
AS_CUBE
AS_CUBEATTRIBUTE
AS_CUBEDIMENSION
AS_CUBEHIERARCHY
AS_DATASOURCE
AS_DATASOURCEVIEW
AS_DIMENSION
AS_DIMENSIONATTRIBUTE
AS_DIMENSIONDATASOURCEVIEW
AS_DSVTABLE
AS_DSVTABLECOLUMN
AS_HIERARCHY
AS_KPI
AS_LEVEL
AS_MDXSCRIPT
AS_MEASURE
AS_MEASUREGROUP
AS_MININGMODEL
AS_MININGMODELALGORITHMPARAMETER
AS_MININGMODELCOLUMN
AS_MININGSTRUCTURE
AS_MININGSTRUCTURECOLUMN
AS_MININGSTRUCTUREDATASOURCEVIEW
AS_MININGSTRUCTURESOURCE
AS_PERSPECTIVE
AS_PERSPECTIVEACTION
AS_PERSPECTIVEATTRIBUTE
AS_PERSPECTIVECALCULATION
AS_PERSPECTIVEDIMENSION
AS_PERSPECTIVEHIERARCHY
AS_PERSPECTIVEKPI
AS_PERSPECTIVEMEASURE
AS_PERSPECTIVEMEASUREGROUP
IS_COMPONENT
IS_COMPONENTCONNECTION
IS_CONNECTIONMANAGER
IS_DTSCONTAINER
IS_INPUT
IS_INPUTCOLUMN
IS_LOGPROVIDER
IS_OUTPUT
IS_OUTPUTCOLUMN
IS_PACKAGE
IS_TASKHOST
IS_VARIABLE
RS_DATASET
RS_DATASETFIELD
RS_DATASOURCE
RS_IMAGE
RS_QUERYPARAMETER
RS_REPORT
RS_REPORTDATASOURCE
RS_REPORTPARAMETER
RS_TABLE
RS_TEXTBOX
SS_CHECK
SS_FOREIGNKEY
SS_RULE
SS_SCHEMA
SS_SPPARAMETER
SS_STOREDPROCEDURE
SS_TABLE
SS_TABLECOLUMN
SS_TABLEDEFAULTCONSTRAINT
SS_TABLEINDEX
SS_TABLETRIGGER
SS_UDDT
SS_UDFPARAMETER
SS_UNKNOWN_TABLE_OR_VIEW
SS_USERDEFINEDFUNCTION
SS_VIEW
SS_VIEWCOLUMN
SS_VIEWINDEX
SS_XMLSCHEMACOLLECTION

*/

And here is the output of above script