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

Question

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

Johnny B

SSCrazy

Points: 2965
More actions
August 31, 2015 at 4:59 pm

#398971

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

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

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

RTaylor2208 SSChampion Points: 13298 More actions · Answer 1

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

Johnny B SSCrazy Points: 2965 More actions · Answer 2

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

RTaylor2208 SSChampion Points: 13298 More actions · Answer 3

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

Johnny B SSCrazy Points: 2965 More actions · Answer 4

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