none
Find out the objects(including SSIS packages) dependent on Table.

    Question

  • Hi, i have been searching online if there is any way i could find out the SSIS packages and other objects dependent on Table but didnt find right article. i tried using sp_depends but it doesnt give right results. and i also tried using the following script :

    select name from syscomments c join sysobjects o 
                     on c.id = o.id 
                where TEXT like '%Customer_ID%' and TEXT like '%Customer_Planning_Detail%'

    but it doesnt show any SSIS package dependent on it and there is one SSIS package using that table.

    Any Idea?

    Tuesday, March 20, 2012 6:33 PM

Answers

  • If you dont' mind investing in a 3rd party tool, BI Documenter , by Pragmatic works is an excellent tool that will help you document table dependencies in SSIS packages. Unfortunately, if you have in - line sql and tables/views as sources and destination directly in Data Flow tasks, there is no sql query that can document such dependencies. 

    If you only use stored proc to interface with tables ( your SSIS packages have no in-line TSQL or tables/views as sources and destination directly in DFTs), then you could possibly use the stored procs as an indirect way of figuring out SSIS dependencies on tables. Hope this helps .

    http://pragmaticworks.com/Products/Business-Intelligence/BIDocumenter/


    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer.

    • Marked as answer by KJian_ Tuesday, March 27, 2012 8:54 AM
    Tuesday, March 20, 2012 6:42 PM

All replies

  • SQL server has no way of telling what client applications may be dependant on a table.  Ultimately SSIS is just another client application
    Tuesday, March 20, 2012 6:40 PM
  • If you dont' mind investing in a 3rd party tool, BI Documenter , by Pragmatic works is an excellent tool that will help you document table dependencies in SSIS packages. Unfortunately, if you have in - line sql and tables/views as sources and destination directly in Data Flow tasks, there is no sql query that can document such dependencies. 

    If you only use stored proc to interface with tables ( your SSIS packages have no in-line TSQL or tables/views as sources and destination directly in DFTs), then you could possibly use the stored procs as an indirect way of figuring out SSIS dependencies on tables. Hope this helps .

    http://pragmaticworks.com/Products/Business-Intelligence/BIDocumenter/


    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer.

    • Marked as answer by KJian_ Tuesday, March 27, 2012 8:54 AM
    Tuesday, March 20, 2012 6:42 PM
  • You may try:

    Select 
    	SSIS.name As PackageName 
    	,SSIS.description As PackageDescription 
    	,SSIS.createdate As PackageCreateDate  
    	,SSIS.ownersid 
        ,(Case SSIS.packagetype
            when 0 then 'Undefined'
            when 1 then 'SQL Server Import and Export Wizard'
            when 2 then 'DTS Designer in SQL Server 2000'
            when 3 then 'SQL Server Replication'
            when 5 then 'SSIS Designer'
            when 6 then 'Maintenance Plan Designer or Wizard'
    	End) As PackageType 
        ,(Case SSIS.packageformat
            when 0 then 'SSIS 2005 version'
            when 1 then 'SSIS 2008 version'
        End) As PackageFormat 
    	,CAST(CAST(SSIS.packagedata AS VARBINARY(MAX)) AS XML) PackageXML
    From 
    	[msdb].[dbo].[sysssispackages] As SSIS

    This will list all SSIS packages in your Server. In the Next step, you have to read from PackageXML's XML data. The element name would be SQLCommand. 

    Hope this will give you some idea.


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    Tuesday, March 20, 2012 6:45 PM
  • Thanks Arbi, i am checking in msdb but i dont have any sysssispackages table in there. it throws error Invalid Object name msdb.dbo.sysssispackages
    Tuesday, March 20, 2012 6:56 PM
  • What is your SQL Server version?

    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    Tuesday, March 20, 2012 7:01 PM
  • i checked in management studio. the ssis packages arent deployed in msdb so no data for ssis packages in msdb
    Tuesday, March 20, 2012 7:03 PM
  • 2005
    Tuesday, March 20, 2012 7:04 PM
  • 2005

    Then try to use:

    Select * From MSDB.dbo.sysdtspackages90

    http://technet.microsoft.com/en-us/library/ms181582(v=sql.90).aspx



    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    Tuesday, March 20, 2012 7:08 PM
  • 2005

    Then try to use:

    Select * From MSDB.dbo.sysdtspackages90

    http://technet.microsoft.com/en-us/library/ms181582(v=sql.90).aspx



    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    That is not going to work when the SSIS packages are in the file system. Again - SSIS is just an client application in the same way that a website that accesses a database is a client application. The DB server has no idea what any given client may request from the DB
    Tuesday, March 20, 2012 7:14 PM
  • Just like to add that this will work only if the SSIS packages are stored within msdb. if your enviornment has SSIS package sitting in the file system as .dtsx files, they will not show up in this query.

    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer.

    Tuesday, March 20, 2012 8:07 PM
  • Below step might help you out :

    1. Export MSDB package as dtsx file .

    2. Open SQl Server BI .

    3. ADD exported dtsx file into it .

    Here in BI design mode   you will be able to see the source and destination details .

    Monday, April 15, 2013 6:26 AM