Strategy to determine what Stored Procedures, if any, are being called within an SSIS package

  • Hi,

    The subject says it all. There are expensive tools out there which reverse engineer SSIS packages. In fact, I've demo-ed a tool from Embarcadero Tech., which is 25k. We have 2008r2 and only about 30 packages, some on the files system some in MSDB.

    Can a TSQL query tell me what I need to know? Would you put everything in a directory and txt search the files? Looking for ideas or others who have been charged with such a task.

    Best,

    John

    SQL 2012 Standard VPS Windows 2012 Server Standard

  • I found this query taken from here which should get you pretty close:

    https://social.msdn.microsoft.com/forums/sqlserver/en-US/7d9513e3-ea0a-45fc-be24-a7d2a56cf0dc/querying-msdbdbosysssispackages

    WITH XMLNAMESPACES (

    'www.microsoft.com/SqlServer/Dts' AS DTS

    ,'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask

    )

    SELECT

    syspackages.folderid

    ,syspackages.id

    ,c.value('../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') ObjectName

    ,c.value('(@SQLTask:SqlStatementSource)[1]', 'NVARCHAR(MAX)') AS SqlStatement

    FROM ( SELECT folderid, id, name, CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) Package_XML FROM msdb.dbo.sysssispackages) syspackages

    CROSS APPLY syspackages.Package_XML.nodes('//DTS:ObjectData//SQLTask:SqlTaskData') s(c)

    MCITP SQL 2005, MCSA SQL 2012

  • This looks cool, thanks. Any idea how to map the returned folderid and id to something meaningful?

    SQL 2012 Standard VPS Windows 2012 Server Standard

  • This any better:

    WITH XMLNAMESPACES (

    'www.microsoft.com/SqlServer/Dts' AS DTS

    ,'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask

    )

    SELECT

    folders.foldername

    ,syspackages.PackageName

    ,c.value('../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') ObjectName

    ,c.value('(@SQLTask:SqlStatementSource)[1]', 'NVARCHAR(MAX)') AS SqlStatement

    FROM (SELECT folderid, name as PackageName, CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) Package_XML

    FROM msdb.dbo.sysssispackages

    ) syspackages

    CROSS APPLY syspackages.Package_XML.nodes('//DTS:ObjectData//SQLTask:SqlTaskData') s(c)

    JOIN msdb..sysssispackagefolders folders on syspackages.folderid = folders.folderid

    MCITP SQL 2005, MCSA SQL 2012

  • I know SSIS packages (2008 R2) can be stored in the files system and the MSDB database. I know I have SSIS packages installed which are not returned by this query. Is this because of how I am storing them? I need to do some research and make sure I get an all inclusive list.

    This a a great start.

    SQL 2012 Standard VPS Windows 2012 Server Standard

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply