--#region CREATE ##SSISPackages
IF OBJECT_ID('tempdb..##SSISPackages') IS NOT NULL
DROP TABLE ##SSISPackages
GO
IF OBJECT_ID('tempdb..##SSISPackages') IS NULL
CREATE TABLE ##SSISPackages ([PackageId] INT IDENTITY
NOT NULL
PRIMARY KEY CLUSTERED,
FileSystem BIT NOT NULL,
[name] SYSNAME NOT NULL,
[id] UNIQUEIDENTIFIER NULL,
[description] NVARCHAR(1024) NULL,
[createdate] DATETIME NOT NULL,
[FolderName] NVARCHAR(257) NULL,
[packagelength] INT NULL,
[packageformat] INT NOT NULL,
[packagetype] INT NULL,
[packagetypedesc] VARCHAR(35) NULL,
[vermajor] INT NULL,
[verminor] INT NULL,
[verbuild] INT NULL,
[vercomments] NVARCHAR(1024) NULL,
[verid] UNIQUEIDENTIFIER NULL,
[isencrypted] BIT NULL,
[pkgXML] XML NOT NULL)
--#endregion
GO
DECLARE @IncludeFiles BIT = 1,
@IncludeMSDB BIT = 1
--#region Import MSDB
IF @IncludeMSDB = 1
BEGIN
;
WITH folders
AS (SELECT ISNULL(fo.FolderName + '\' + fl.foldername, fl.foldername) AS foldername,
fl.folderid
FROM [msdb].dbo.sysssispackagefolders fl (NOLOCK)
LEFT OUTER JOIN [msdb].dbo.sysssispackagefolders fo (NOLOCK) ON fo.folderid = fl.parentfolderid)
INSERT ##SSISPackages WITH (TABLOCK)
(FileSystem,
[name],
[id],
[description],
[createdate],
[FolderName],
[packagelength],
[packageformat],
[packagetype],
[packagetypedesc],
[vermajor],
[verminor],
[verbuild],
[vercomments],
[verid],
[isencrypted],
[pkgXML])
SELECT 0 AS FileSystem,
[name],
[id],
[description],
[createdate],
fl.FolderName,
DATALENGTH(packagedata) AS packagelength,
[packageformat],
packagetype,
CASE [packagetype]
WHEN 0 THEN 'default'
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 packagetypedesc,
[vermajor],
[verminor],
[verbuild],
[vercomments],
[verid],
[isencrypted],
CAST(CAST(CAST([packagedata] AS VARBINARY(MAX)) AS VARCHAR(MAX)) AS XML) pkgXML
FROM [msdb].[dbo].[sysssispackages] sp (NOLOCK)
LEFT OUTER JOIN folders fl ON fl.folderid = sp.folderid
END
--#endregion
--#region Import from File system
IF @IncludeFiles = 1
BEGIN
DECLARE @FileList TABLE (id INT IDENTITY,
CONTENT VARCHAR(4000))
INSERT @FileList
(CONTENT)
EXEC ('EXEC xp_cmdshell ''dir c:\apps\*.dtsx /s''')
INSERT ##SSISPackages WITH (TABLOCK)
(FileSystem,
[name],
[id],
[description],
[createdate],
[FolderName],
[packagelength],
[packageformat],
[packagetype],
[packagetypedesc],
[vermajor],
[verminor],
[verbuild],
[vercomments],
[verid],
[isencrypted],
[pkgXML])
SELECT 1 AS FileSystem,
FILENAME AS [name],
NULL [id],
Directory + '\' + FILENAME AS [description],
FileDate AS [createdate],
Directory AS [FolderName],
SIZE AS [packagelength],
0 AS [packageformat],
NULL AS [packagetype],
'File System' AS [packagetypedesc],
0 AS [vermajor],
0 AS [verminor],
0 AS [verbuild],
'' AS [vercomments],
NULL AS [verid],
0 AS [isencrypted],
CAST(Util.fs.ReadAllTextFromFile(Directory + '\' + FileName) AS XML) pkgXML
FROM @FileList t
OUTER APPLY (SELECT TOP 1
REPLACE(CONTENT, ' Directory of ', '') AS Directory
FROM @FileList b
WHERE CONTENT LIKE ' Directory of%'
AND b.id < t.id
ORDER BY id DESC) AS dr
CROSS APPLY (SELECT CAST(SUBSTRING(CONTENT, 1, 20) AS DATETIME) AS FileDate,
CAST(REPLACE(SUBSTRING(CONTENT, 21, 18), ',', '') AS INT) AS SIZE,
SUBSTRING(CONTENT, 40, 256) AS FILENAME) ss
WHERE CONTENT LIKE '%.dtsx'
END
--#endregion
CREATE PRIMARY XML INDEX [PK_XML_Data_AppSSISPackages] ON ##SSISPackages (pkgXML)
GO
/*
-- All properties of a package
SELECT Props.Prop.query('.') AS PropXml ,
Props.Prop.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
string(./@p1:Name)', 'nvarchar(max)') AS PropName ,
Props.Prop.value('.', 'nvarchar(max)') AS PropValue
FROM ##SSISPackages P
CROSS APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
/DTS:Executable/DTS:Property') Props ( Prop )
WHERE p.Name = 'MergeTransaction'
*/
-- Name and type of every task in a package
-- DROP TABLE ##SSISPackagesQL
IF OBJECT_ID('tempdb..##SSISPackagesQL') IS NULL
SELECT p.NAME AS PackageName,
Pkg.props.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
./p1:Property[@p1:Name=''ObjectName''][1]', 'varchar(256)') AS TaskName
--,Pkg.props.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
-- ./@p1:ExecutableType', 'varchar(1000)') AS TaskType
,
Pkg.props.value('declare namespace p1="www.microsoft.com/SqlServer/Dts"; declare namespace p2="www.microsoft.com/sqlserver/dts/tasks/sqltask";
./p1:ObjectData[1]/p2:SqlTaskData[1]/@p2:SqlStatementSource', 'varchar(max)') AS SqlStatementSource,
Pkg.props.value('declare namespace p1="www.microsoft.com/SqlServer/Dts"; declare namespace p2="www.microsoft.com/sqlserver/dts/tasks/sqltask";
./p1:PropertyExpression[@p1:Name="SqlStatementSource"][1]', 'varchar(max)') AS Expression
INTO ##SSISPackagesQL
FROM ##SSISPackages P
CROSS APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
//DTS:Executable[@DTS:ExecutableType!=''STOCK:SEQUENCE''
and @DTS:ExecutableType!=''STOCK:FORLOOP''
and @DTS:ExecutableType!=''STOCK:FOREACHLOOP''
and contains(@DTS:ExecutableType, ''ExecuteSQLTask'')
and not(contains(@DTS:ExecutableType,''.Package.''))]') Pkg (props)
WHERE p.FolderName NOT IN ('\Data Collector', '\Maintenance Plans')
--WHERE p.Name = 'MergeTransaction'
GO
DECLARE @SQL VARCHAR(MAX),
@FilePath VARCHAR(256) = 'c:\temp\SSISExecuteSQL.sql',
@PrintSQL BIT = 0
SELECT @SQL = Util.dbo.StringConcat('-- -- PACKAGE : ' + PackageName + ', TASK NAME : ' + TaskName + ', SQL STATEMENT SOURCE -- --
' + SqlStatementSource + ISNULL('
-- -- PACKAGE : ' + PackageName + ', TASK NAME : ' + TaskName + ', SQL STATEMENT SOURCE EXPRESSION -- --
' + NULLIF(Expression, ''), ''), '
')
FROM ##SSISPackagesQL
--WHERE PackageName = 'TransformProperty'
IF @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
IF @FilePath <> ''
SELECT ReturnVal,
MESSAGE,
@FilePath AS FilePath,
'Saved to ' + @FilePath AS Info
FROM Util.FS.AppendAllTextToFile(@FilePath, @SQL, 1)
GO