August 31, 2015 at 4:59 pm
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
September 1, 2015 at 4:52 am
I found this query taken from here which should get you pretty close:
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
September 1, 2015 at 1:53 pm
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
September 2, 2015 at 3:03 am
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
September 2, 2015 at 1:30 pm
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