August 24, 2017 at 12:27 am
Hi Team I need to fetch SSIS MetaData information from sql server database .
Please provide query which will give all below details.
o Package Name
o Sources (DB/File//etc)
o Targets (Table/File/File Location)
o Stored Procs used in package
o Source Tables/Columns
o Package Connections
o Target file name(s)/File Type etc.
August 24, 2017 at 5:03 am
If your packages are deployed to SSISDB, this is not possible because the packages are stored in an encrypted format.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 24, 2017 at 6:39 am
Hi Phil,
Can you get the required data using [msdb].[dbo].[sysssispackages] .
Please help to get the required o/p
SELECT
Name,
CAST(CAST(CAST([packagedata] as varbinary(max)) as varchar(max)) as XML) pkgXML
FROM [msdb].[dbo].[sysssispackages]
August 24, 2017 at 7:13 am
subratnayak09 - Thursday, August 24, 2017 6:39 AMHi Phil,Can you get the required data using [msdb].[dbo].[sysssispackages] .
Please help to get the required o/pSELECT
Name,
CAST(CAST(CAST([packagedata] as varbinary(max)) as varchar(max)) as XML) pkgXML
FROM [msdb].[dbo].[sysssispackages]
I don't know. I've been using SSISDB for the last five years. Maybe someone else can help you.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 24, 2017 at 9:56 am
subratnayak09 - Thursday, August 24, 2017 12:27 AMHi Team I need to fetch SSIS MetaData information from sql server database .
Please provide query which will give all below details.
o Package Name
o Sources (DB/File//etc)
o Targets (Table/File/File Location)
o Stored Procs used in package
o Source Tables/Columns
o Package Connections
o Target file name(s)/File Type etc.
First for some context.
SSIS packages are XML files, dtsx files specifically. All the information you are looking for is there; you just need to have a basic understanding of SSIS, XML and the T-SQL APPLY operator to extract the info you need. These dtsx files (SSIS packages) can be stored on the file system and executed from there or they can be stored and executed from SQL server. Pre-2012 they were most commonly pushed to msdb.dbo.sysssispackages (which subratnayak09 mentioned) and, on SQL 2012+, they can also be pushed to the SSIS Catalog (the SSISDB Phil mentioned). The SSIS Catalog is my preference but that's another topic.
There are many good examples out there on how to pull whatever you need from .dtsx packages from any source. A google search for "querying msdb.dbo.sysssispackages", "extract metadata from .dtsx packages", "querying the SSIS Catalog", etc - will return some useful examples. I started to put something together for you but am out of time this is an example of how to pull metedata from from SSIS packages deployed to msdb.dbo.sysssispackages:
WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' as DTS )
SELECT top (200)
PackageName = ISNULL([Name], '<unknown>'),
Creator = ssis.n.value('(DTS: Property[@DTS:Name="CreatorName"]/text())[1]', 'varchar(max)'), -- !!! REMOVE THE SPACE BETWEEN "DTS:" and "Property
exe = exe.n.value('(@DTS:ExecutableType)[1]', 'varchar(max)')
FROM msdb.dbo.sysssispackages
CROSS APPLY (VALUES (CAST(CAST(CAST(packagedata as varbinary(max)) as varchar(max)) as XML))) p(x)
CROSS APPLY p.x.nodes('/DTS:Executable') ssis(n)
CROSS APPLY ssis.n.nodes('DTS:Executable') exe(n)
P.s. Note my comment about removing the space between "DTS:" and "Property - leaving it as is displays this emojicon: 😛
-- Itzik Ben-Gan 2001
August 29, 2017 at 7:46 am
Alan.B - Thursday, August 24, 2017 9:56 AMsubratnayak09 - Thursday, August 24, 2017 12:27 AMHi Team I need to fetch SSIS MetaData information from sql server database .
Please provide query which will give all below details.
o Package Name
o Sources (DB/File//etc)
o Targets (Table/File/File Location)
o Stored Procs used in package
o Source Tables/Columns
o Package Connections
o Target file name(s)/File Type etc.First for some context.
SSIS packages are XML files, dtsx files specifically. All the information you are looking for is there; you just need to have a basic understanding of SSIS, XML and the T-SQL APPLY operator to extract the info you need. These dtsx files (SSIS packages) can be stored on the file system and executed from there or they can be stored and executed from SQL server. Pre-2012 they were most commonly pushed to msdb.dbo.sysssispackages (which subratnayak09 mentioned) and, on SQL 2012+, they can also be pushed to the SSIS Catalog (the SSISDB Phil mentioned). The SSIS Catalog is my preference but that's another topic.There are many good examples out there on how to pull whatever you need from .dtsx packages from any source. A google search for "querying msdb.dbo.sysssispackages", "extract metadata from .dtsx packages", "querying the SSIS Catalog", etc - will return some useful examples. I started to put something together for you but am out of time this is an example of how to pull metedata from from SSIS packages deployed to msdb.dbo.sysssispackages:
WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' as DTS )
SELECT top (200)
PackageName = ISNULL([Name], '<unknown>'),
Creator = ssis.n.value('(DTS: Property[@DTS:Name="CreatorName"]/text())[1]', 'varchar(max)'), -- !!! REMOVE THE SPACE BETWEEN "DTS:" and "Property
exe = exe.n.value('(@DTS:ExecutableType)[1]', 'varchar(max)')
FROM msdb.dbo.sysssispackages
CROSS APPLY (VALUES (CAST(CAST(CAST(packagedata as varbinary(max)) as varchar(max)) as XML))) p(x)
CROSS APPLY p.x.nodes('/DTS:Executable') ssis(n)
CROSS APPLY ssis.n.nodes('DTS:Executable') exe(n)P.s. Note my comment about removing the space between "DTS:" and "Property - leaving it as is displays this emojicon: 😛
Hi All,
Thanks for your valuable input. I am able to trace some extend and need in below format .Please help on this regards.
o Package Name
o Sources (DB/File//etc)
o Targets (Table/File/File Location)
o Stored Procs used in package
o Source Tables/Columns
o Package Connections
o Target file name(s)/File Type etc.
Script developed as of now.
--USE Your SSIS DB
GO
IF OBJECT_ID('tempdb..#tblMSDBdata', 'U') IS NOT NULL
DROP TABLE #tblMSDBdata;
IF OBJECT_ID('tempdb..#tblProcname', 'U') IS NOT NULL
DROP TABLE #tblProcname;
IF OBJECT_ID('tempdb..#tblConnMGR', 'U') IS NOT NULL
DROP TABLE #tblConnMGR;
GO
SET NOCOUNT ON;
WITH Tblpackages AS (
SELECT cast(foldername as varchar(8000)) as folderpath, folderid
FROM msdb..sysssispackagefolders
WHERE parentfolderid = '00000000-0000-0000-0000-000000000000'
union all
SELECT cast(foldername as varchar(8000)) as folderpath, folderid
FROM msdb..sysssispackagefolders
WHERE parentfolderid is null
UNION ALL
SELECT cast(c.folderpath + '\' + f.foldername as varchar(8000)), f.folderid
FROM msdb..sysssispackagefolders f
INNER JOIN Tblpackages c ON c.folderid = f.parentfolderid
)
SELECT nullif(c.folderpath,'') as Folder,p.name as package ,
CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) as pkg,
ROW_NUMBER() OVER (ORDER BY folderpath) AS ROWID
into #tblMSDBdata FROM Tblpackages c
INNER JOIN msdb..sysssispackages p ON c.folderid = p.folderid
WHERE c.folderpath NOT LIKE '%Data Collector%' and c.folderpath NOT LIKE '%Maintenance%'
and (left(c.folderpath,1)='\' or c.folderpath ='')
GO
-- Getting PackageConnections
declare @PackageData_CM nvarchar(max)
Declare @TblProcOccur_CM table (pos int, pkgdata nvarchar(max))
Declare @pos_CM int
Declare @oldpos_CM int
DECLARE @MAXID_CM INT, @Counter_CM INT
SET @Counter_CM = 1
SELECT @MAXID_CM = COUNT(*) FROM #tblMSDBdata
WHILE (@Counter_CM <= @MAXID_CM)
BEGIN
SELECT @pos_CM =0
SELECT @oldpos_CM=0
select @PackageData_CM =pkg from #tblMSDBdata where ROWID =@Counter_CM
select @pos_CM=patindex('%<DTS:Property DTS:Name="ConnectionString">%',@PackageData_CM)
while @pos_CM > 0 and @oldpos_CM<>@pos_CM
begin
insert into @TblProcOccur_CM Values (@Counter_CM, substring(@PackageData_CM,@pos_CM+42, charindex('</DTS',substring(@PackageData_CM,@pos_CM+43,500)) ))
Select @oldpos_CM=@pos_CM
select @pos_CM=patindex('%<DTS:Property DTS:Name="ConnectionString">%',Substring(@PackageData_CM,@pos_CM + 1,len(@PackageData_CM))) + @pos_CM
end
SET @Counter_CM = @Counter_CM + 1
END
SELECT b.ROWID,b.Folder,b.package,a.pkgdata into #tblConnMGR from @TblProcOccur_CM a inner join #tblMSDBdata b on b.ROWID =a.pos
GO
SELECT DISTINCT @@SERVERNAME AS SERVERNAME, 'MSDB' AS DATABASENAME,FOLDER,PACKAGE AS PACKAGENAME,
CASE
WHEN left(pkgdata,4) ='DATA' AND PATINDEX('%Initial%',pkgdata) >0 THEN 'SQL SERVER'
WHEN PATINDEX('%provider%',pkgdata) >0 AND PATINDEX('%Excel%',pkgdata) >0 THEN 'EXCEL'
WHEN left(pkgdata,4) ='DATA' AND PATINDEX('%TCP%',pkgdata) >0 THEN 'ORACLE'
WHEN right(pkgdata,4) ='.lis' THEN 'LIST FILE'
WHEN RIGHT(PKGDATA,4) ='.TXT' THEN 'TEXT FILE'
WHEN RIGHT(PKGDATA,4) ='.log' THEN 'LOG FILE'
ELSE '' END AS SOURCEDATA ,
CASE WHEN left(pkgdata,4) ='DATA' AND PATINDEX('%Initial%',pkgdata) > 0 THEN SUBSTRING(pkgdata,13, CHARINDEX(';',pkgdata,1)-13) else '' END AS CMNAME,
CASE WHEN left(pkgdata,4) ='DATA' AND PATINDEX('%Initial%',pkgdata) > 0 THEN
SUBSTRING(pkgdata,PATINDEX('%Initial%',pkgdata)+16, CHARINDEX(';',pkgdata,PATINDEX('%Initial%',pkgdata))-(PATINDEX('%Initial%',pkgdata))-16) else '' END
AS DB_NAMEUSEDINSSIS,pkgdata AS PKGCONNECTIONMGR
FROM #tblConnMGR ORDER BY PACKAGENAME
GO
August 30, 2017 at 7:58 am
subratnayak09 - Tuesday, August 29, 2017 7:46 AMAlan.B - Thursday, August 24, 2017 9:56 AMsubratnayak09 - Thursday, August 24, 2017 12:27 AMHi Team I need to fetch SSIS MetaData information from sql server database .
Please provide query which will give all below details.
o Package Name
o Sources (DB/File//etc)
o Targets (Table/File/File Location)
o Stored Procs used in package
o Source Tables/Columns
o Package Connections
o Target file name(s)/File Type etc.First for some context.
SSIS packages are XML files, dtsx files specifically. All the information you are looking for is there; you just need to have a basic understanding of SSIS, XML and the T-SQL APPLY operator to extract the info you need. These dtsx files (SSIS packages) can be stored on the file system and executed from there or they can be stored and executed from SQL server. Pre-2012 they were most commonly pushed to msdb.dbo.sysssispackages (which subratnayak09 mentioned) and, on SQL 2012+, they can also be pushed to the SSIS Catalog (the SSISDB Phil mentioned). The SSIS Catalog is my preference but that's another topic.There are many good examples out there on how to pull whatever you need from .dtsx packages from any source. A google search for "querying msdb.dbo.sysssispackages", "extract metadata from .dtsx packages", "querying the SSIS Catalog", etc - will return some useful examples. I started to put something together for you but am out of time this is an example of how to pull metedata from from SSIS packages deployed to msdb.dbo.sysssispackages:
WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' as DTS )
SELECT top (200)
PackageName = ISNULL([Name], '<unknown>'),
Creator = ssis.n.value('(DTS: Property[@DTS:Name="CreatorName"]/text())[1]', 'varchar(max)'), -- !!! REMOVE THE SPACE BETWEEN "DTS:" and "Property
exe = exe.n.value('(@DTS:ExecutableType)[1]', 'varchar(max)')
FROM msdb.dbo.sysssispackages
CROSS APPLY (VALUES (CAST(CAST(CAST(packagedata as varbinary(max)) as varchar(max)) as XML))) p(x)
CROSS APPLY p.x.nodes('/DTS:Executable') ssis(n)
CROSS APPLY ssis.n.nodes('DTS:Executable') exe(n)P.s. Note my comment about removing the space between "DTS:" and "Property - leaving it as is displays this emojicon: 😛
Hi All,
Thanks for your valuable input. I am able to trace some extend and need in below format .Please help on this regards.
o Package Name
o Sources (DB/File//etc)
o Targets (Table/File/File Location)
o Stored Procs used in package
o Source Tables/Columns
o Package Connections
o Target file name(s)/File Type etc.Script developed as of now.
--USE Your SSIS DB
GOIF OBJECT_ID('tempdb..#tblMSDBdata', 'U') IS NOT NULL
DROP TABLE #tblMSDBdata;IF OBJECT_ID('tempdb..#tblProcname', 'U') IS NOT NULL
DROP TABLE #tblProcname;IF OBJECT_ID('tempdb..#tblConnMGR', 'U') IS NOT NULL
DROP TABLE #tblConnMGR;
GO
SET NOCOUNT ON;WITH Tblpackages AS (
SELECT cast(foldername as varchar(8000)) as folderpath, folderid
FROM msdb..sysssispackagefolders
WHERE parentfolderid = '00000000-0000-0000-0000-000000000000'
union all
SELECT cast(foldername as varchar(8000)) as folderpath, folderid
FROM msdb..sysssispackagefolders
WHERE parentfolderid is null
UNION ALL
SELECT cast(c.folderpath + '\' + f.foldername as varchar(8000)), f.folderid
FROM msdb..sysssispackagefolders f
INNER JOIN Tblpackages c ON c.folderid = f.parentfolderid
)SELECT nullif(c.folderpath,'') as Folder,p.name as package ,
CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) as pkg,
ROW_NUMBER() OVER (ORDER BY folderpath) AS ROWID
into #tblMSDBdata FROM Tblpackages c
INNER JOIN msdb..sysssispackages p ON c.folderid = p.folderid
WHERE c.folderpath NOT LIKE '%Data Collector%' and c.folderpath NOT LIKE '%Maintenance%'
and (left(c.folderpath,1)='\' or c.folderpath ='')
GO
-- Getting PackageConnections
declare @PackageData_CM nvarchar(max)
Declare @TblProcOccur_CM table (pos int, pkgdata nvarchar(max))
Declare @pos_CM int
Declare @oldpos_CM int
DECLARE @MAXID_CM INT, @Counter_CM INT
SET @Counter_CM = 1
SELECT @MAXID_CM = COUNT(*) FROM #tblMSDBdata
WHILE (@Counter_CM <= @MAXID_CM)
BEGIN
SELECT @pos_CM =0
SELECT @oldpos_CM=0
select @PackageData_CM =pkg from #tblMSDBdata where ROWID =@Counter_CM
select @pos_CM=patindex('%<DTS:Property DTS:Name="ConnectionString">%',@PackageData_CM)
while @pos_CM > 0 and @oldpos_CM<>@pos_CM
begin
insert into @TblProcOccur_CM Values (@Counter_CM, substring(@PackageData_CM,@pos_CM+42, charindex('</DTS',substring(@PackageData_CM,@pos_CM+43,500)) ))
Select @oldpos_CM=@pos_CM
select @pos_CM=patindex('%<DTS:Property DTS:Name="ConnectionString">%',Substring(@PackageData_CM,@pos_CM + 1,len(@PackageData_CM))) + @pos_CM
end
SET @Counter_CM = @Counter_CM + 1
ENDSELECT b.ROWID,b.Folder,b.package,a.pkgdata into #tblConnMGR from @TblProcOccur_CM a inner join #tblMSDBdata b on b.ROWID =a.pos
GOSELECT DISTINCT @@SERVERNAME AS SERVERNAME, 'MSDB' AS DATABASENAME,FOLDER,PACKAGE AS PACKAGENAME,
CASE
WHEN left(pkgdata,4) ='DATA' AND PATINDEX('%Initial%',pkgdata) >0 THEN 'SQL SERVER'
WHEN PATINDEX('%provider%',pkgdata) >0 AND PATINDEX('%Excel%',pkgdata) >0 THEN 'EXCEL'
WHEN left(pkgdata,4) ='DATA' AND PATINDEX('%TCP%',pkgdata) >0 THEN 'ORACLE'
WHEN right(pkgdata,4) ='.lis' THEN 'LIST FILE'
WHEN RIGHT(PKGDATA,4) ='.TXT' THEN 'TEXT FILE'
WHEN RIGHT(PKGDATA,4) ='.log' THEN 'LOG FILE'
ELSE '' END AS SOURCEDATA ,
CASE WHEN left(pkgdata,4) ='DATA' AND PATINDEX('%Initial%',pkgdata) > 0 THEN SUBSTRING(pkgdata,13, CHARINDEX(';',pkgdata,1)-13) else '' END AS CMNAME,
CASE WHEN left(pkgdata,4) ='DATA' AND PATINDEX('%Initial%',pkgdata) > 0 THEN
SUBSTRING(pkgdata,PATINDEX('%Initial%',pkgdata)+16, CHARINDEX(';',pkgdata,PATINDEX('%Initial%',pkgdata))-(PATINDEX('%Initial%',pkgdata))-16) else '' END
AS DB_NAMEUSEDINSSIS,pkgdata AS PKGCONNECTIONMGR
FROM #tblConnMGR ORDER BY PACKAGENAMEGO
Hi Phil,
Can you please help on this as I need this o/p urgently.
August 30, 2017 at 8:30 am
subratnayak09 - Wednesday, August 30, 2017 7:58 AMHi Phil,Can you please help on this as I need this o/p urgently.
Sorry, but I've never tried to do this. If you work it out, please post the solution.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 30, 2017 at 1:50 pm
subratnayak09 - Wednesday, August 30, 2017 7:58 AMsubratnayak09 - Tuesday, August 29, 2017 7:46 AMAlan.B - Thursday, August 24, 2017 9:56 AMsubratnayak09 - Thursday, August 24, 2017 12:27 AMHi Team I need to fetch SSIS MetaData information from sql server database .
Please provide query which will give all below details.
o Package Name
o Sources (DB/File//etc)
o Targets (Table/File/File Location)
o Stored Procs used in package
o Source Tables/Columns
o Package Connections
o Target file name(s)/File Type etc.First for some context.
SSIS packages are XML files, dtsx files specifically. All the information you are looking for is there; you just need to have a basic understanding of SSIS, XML and the T-SQL APPLY operator to extract the info you need. These dtsx files (SSIS packages) can be stored on the file system and executed from there or they can be stored and executed from SQL server. Pre-2012 they were most commonly pushed to msdb.dbo.sysssispackages (which subratnayak09 mentioned) and, on SQL 2012+, they can also be pushed to the SSIS Catalog (the SSISDB Phil mentioned). The SSIS Catalog is my preference but that's another topic.There are many good examples out there on how to pull whatever you need from .dtsx packages from any source. A google search for "querying msdb.dbo.sysssispackages", "extract metadata from .dtsx packages", "querying the SSIS Catalog", etc - will return some useful examples. I started to put something together for you but am out of time this is an example of how to pull metedata from from SSIS packages deployed to msdb.dbo.sysssispackages:
WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' as DTS )
SELECT top (200)
PackageName = ISNULL([Name], '<unknown>'),
Creator = ssis.n.value('(DTS: Property[@DTS:Name="CreatorName"]/text())[1]', 'varchar(max)'), -- !!! REMOVE THE SPACE BETWEEN "DTS:" and "Property
exe = exe.n.value('(@DTS:ExecutableType)[1]', 'varchar(max)')
FROM msdb.dbo.sysssispackages
CROSS APPLY (VALUES (CAST(CAST(CAST(packagedata as varbinary(max)) as varchar(max)) as XML))) p(x)
CROSS APPLY p.x.nodes('/DTS:Executable') ssis(n)
CROSS APPLY ssis.n.nodes('DTS:Executable') exe(n)P.s. Note my comment about removing the space between "DTS:" and "Property - leaving it as is displays this emojicon: 😛
Hi All,
Thanks for your valuable input. I am able to trace some extend and need in below format .Please help on this regards.
o Package Name
o Sources (DB/File//etc)
o Targets (Table/File/File Location)
o Stored Procs used in package
o Source Tables/Columns
o Package Connections
o Target file name(s)/File Type etc.Script developed as of now.
--USE Your SSIS DB
GOIF OBJECT_ID('tempdb..#tblMSDBdata', 'U') IS NOT NULL
DROP TABLE #tblMSDBdata;IF OBJECT_ID('tempdb..#tblProcname', 'U') IS NOT NULL
DROP TABLE #tblProcname;IF OBJECT_ID('tempdb..#tblConnMGR', 'U') IS NOT NULL
DROP TABLE #tblConnMGR;
GO
SET NOCOUNT ON;WITH Tblpackages AS (
SELECT cast(foldername as varchar(8000)) as folderpath, folderid
FROM msdb..sysssispackagefolders
WHERE parentfolderid = '00000000-0000-0000-0000-000000000000'
union all
SELECT cast(foldername as varchar(8000)) as folderpath, folderid
FROM msdb..sysssispackagefolders
WHERE parentfolderid is null
UNION ALL
SELECT cast(c.folderpath + '\' + f.foldername as varchar(8000)), f.folderid
FROM msdb..sysssispackagefolders f
INNER JOIN Tblpackages c ON c.folderid = f.parentfolderid
)SELECT nullif(c.folderpath,'') as Folder,p.name as package ,
CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) as pkg,
ROW_NUMBER() OVER (ORDER BY folderpath) AS ROWID
into #tblMSDBdata FROM Tblpackages c
INNER JOIN msdb..sysssispackages p ON c.folderid = p.folderid
WHERE c.folderpath NOT LIKE '%Data Collector%' and c.folderpath NOT LIKE '%Maintenance%'
and (left(c.folderpath,1)='\' or c.folderpath ='')
GO
-- Getting PackageConnections
declare @PackageData_CM nvarchar(max)
Declare @TblProcOccur_CM table (pos int, pkgdata nvarchar(max))
Declare @pos_CM int
Declare @oldpos_CM int
DECLARE @MAXID_CM INT, @Counter_CM INT
SET @Counter_CM = 1
SELECT @MAXID_CM = COUNT(*) FROM #tblMSDBdata
WHILE (@Counter_CM <= @MAXID_CM)
BEGIN
SELECT @pos_CM =0
SELECT @oldpos_CM=0
select @PackageData_CM =pkg from #tblMSDBdata where ROWID =@Counter_CM
select @pos_CM=patindex('%<DTS:Property DTS:Name="ConnectionString">%',@PackageData_CM)
while @pos_CM > 0 and @oldpos_CM<>@pos_CM
begin
insert into @TblProcOccur_CM Values (@Counter_CM, substring(@PackageData_CM,@pos_CM+42, charindex('</DTS',substring(@PackageData_CM,@pos_CM+43,500)) ))
Select @oldpos_CM=@pos_CM
select @pos_CM=patindex('%<DTS:Property DTS:Name="ConnectionString">%',Substring(@PackageData_CM,@pos_CM + 1,len(@PackageData_CM))) + @pos_CM
end
SET @Counter_CM = @Counter_CM + 1
ENDSELECT b.ROWID,b.Folder,b.package,a.pkgdata into #tblConnMGR from @TblProcOccur_CM a inner join #tblMSDBdata b on b.ROWID =a.pos
GOSELECT DISTINCT @@SERVERNAME AS SERVERNAME, 'MSDB' AS DATABASENAME,FOLDER,PACKAGE AS PACKAGENAME,
CASE
WHEN left(pkgdata,4) ='DATA' AND PATINDEX('%Initial%',pkgdata) >0 THEN 'SQL SERVER'
WHEN PATINDEX('%provider%',pkgdata) >0 AND PATINDEX('%Excel%',pkgdata) >0 THEN 'EXCEL'
WHEN left(pkgdata,4) ='DATA' AND PATINDEX('%TCP%',pkgdata) >0 THEN 'ORACLE'
WHEN right(pkgdata,4) ='.lis' THEN 'LIST FILE'
WHEN RIGHT(PKGDATA,4) ='.TXT' THEN 'TEXT FILE'
WHEN RIGHT(PKGDATA,4) ='.log' THEN 'LOG FILE'
ELSE '' END AS SOURCEDATA ,
CASE WHEN left(pkgdata,4) ='DATA' AND PATINDEX('%Initial%',pkgdata) > 0 THEN SUBSTRING(pkgdata,13, CHARINDEX(';',pkgdata,1)-13) else '' END AS CMNAME,
CASE WHEN left(pkgdata,4) ='DATA' AND PATINDEX('%Initial%',pkgdata) > 0 THEN
SUBSTRING(pkgdata,PATINDEX('%Initial%',pkgdata)+16, CHARINDEX(';',pkgdata,PATINDEX('%Initial%',pkgdata))-(PATINDEX('%Initial%',pkgdata))-16) else '' END
AS DB_NAMEUSEDINSSIS,pkgdata AS PKGCONNECTIONMGR
FROM #tblConnMGR ORDER BY PACKAGENAMEGO
Hi Phil,
Can you please help on this as I need this o/p urgently.
What you are asking is a non-trivial task and it's hard to imagine anyone is going to write a query that does this for you. I would populate #tblMSDBdata but with xml data like this: CAST(CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) AS xml) as pkg
(not as varchar(max) as you are doing). Then click on one of the XML records that are returned which will open the XML in another window. What you are looking for is all in there. With an understanding of XML (specifically XPATH), SSIS, and T-SQL XML functions/methods such as NODES you can do this.
Here's an example of how to extract SQL Task info (e.g. is it ad hoc SQL? A stored proc? What's the query?):
WITH XMLNAMESPACES
(
'www.microsoft.com/SqlServer/Dts' as DTS,
'www.microsoft.com/sqlserver/dts/tasks/sqltask' as SQLTask
)
select
t.package,
--exeContext = ex.value('local-name(.)', 'varchar(1000)'),
--taskContext = task.value('local-name(.)', 'varchar(1000)'),
SqlStmtSourceType = task.value('(@SQLTask:SqlStmtSourceType)[1]', 'varchar(100)'),
IsStoredProc = task.value('(@SQLTask:IsStoredProc)[1]', 'varchar(10)'),
TSQL_StoredProcCall = case when task.value('(@SQLTask:SqlStatementSource)[1]','varchar(90)')
like 'exec _%' then 1 else 0 end,
SqlStatementSource = task.value('(@SQLTask:SqlStatementSource)[1]', 'nvarchar(max)')
from #tblMSDBdata t
cross apply t.pkg.nodes('//DTS:Executable') exe(ex)
cross apply ex.nodes('DTS:ObjectData/SQLTask:SqlTaskData') sqlTask(task)
-- Itzik Ben-Gan 2001
August 30, 2017 at 2:01 pm
Hi Alan
Not related to this thread at all, but I just noticed that you have misspelt Itzik Ben-Gan's name in your signature & thought you'd like to know.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 30, 2017 at 2:55 pm
Phil Parkin - Wednesday, August 30, 2017 2:01 PMHi Alan
Not related to this thread at all, but I just noticed that you have misspelt Itzik Ben-Gan's name in your signature & thought you'd like to know.
Thanks Phil.
-- Itzik Ben-Gan 2001
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply