/*
Author: Divya Agrawal
Original link: http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/128862/
*/
ALTER PROCEDURE [dbo].[usp_GetPackageDetails] @Path VARCHAR(8000) = 'C:\Packages1'
AS
/*
exec [usp_GetPackageDetails] 'C:\Packages1'
exec sp_configure 'show advanced options',1
reconfigure with override
go
exec sp_configure 'xp_cmdshell',1
reconfigure with override
*/
BEGIN
SET NOCOUNT ON;
--------------------drop all temp tables------------------------------------
IF OBJECT_id('pkgStats') IS NOT NULL
DROP TABLE pkgStats;
IF OBJECT_id('ConnectionDetails') IS NOT NULL
DROP TABLE ConnectionDetails;
IF OBJECT_id('PackageAnalysis') IS NOT NULL
DROP TABLE PackageAnalysis;
IF OBJECT_id('TblPrecedenceConstraint') IS NOT NULL
DROP TABLE TblPrecedenceConstraint;
IF OBJECT_id('TblControlFlowDetails') IS NOT NULL
DROP TABLE TblControlFlowDetails;
IF OBJECT_id('TblScriptTaskdetails') IS NOT NULL
DROP TABLE TblScriptTaskdetails;
IF OBJECT_id('TblExecutePackageTaskDetails') IS NOT NULL
DROP TABLE TblExecutePackageTaskDetails;
IF OBJECT_id('TblSQLTaskDetails') IS NOT NULL
DROP TABLE TblSQLTaskDetails;
IF OBJECT_id('TblDFTTaskDetails') IS NOT NULL
DROP TABLE TblDFTTaskDetails;
IF OBJECT_id('TblSrcDestDetails') IS NOT NULL
DROP TABLE TblSrcDestDetails;
IF OBJECT_id('TblDerivedTaskDetails') IS NOT NULL
DROP TABLE TblDerivedTaskDetails;
IF OBJECT_id('TblLookupTaskDetails') IS NOT NULL
DROP TABLE TblLookupTaskDetails;
IF OBJECT_id('TblVariableDetails') IS NOT NULL
DROP TABLE TblVariableDetails;
IF OBJECT_id('TblParameterDetails') IS NOT NULL
DROP TABLE TblParameterDetails;
IF OBJECT_id('tempdb.dbo.#tempprecedence1') IS NOT NULL
DROP TABLE #tempprecedence1;
IF OBJECT_id('tempdb.dbo.#tempprecedence2') IS NOT NULL
DROP TABLE #tempprecedence2;
IF OBJECT_id('tempdb.dbo.#tempprecedence3') IS NOT NULL
DROP TABLE #tempprecedence3;
IF OBJECT_id('tempdb.dbo.#tempprecedence5') IS NOT NULL
DROP TABLE #tempprecedence5;
IF OBJECT_id('tempdb.dbo.#tempprecedence5_Hierarchy') IS NOT NULL
DROP TABLE #tempprecedence5_Hierarchy;
IF OBJECT_id('tempdb.dbo.#tempprecedence4') IS NOT NULL
DROP TABLE #tempprecedence4;
IF OBJECT_id('tempdb.dbo.#tempprecedence6') IS NOT NULL
DROP TABLE #tempprecedence6;
-------------------drop all temp tables------------------------------------
------------------create all tables-------------------------------------------
CREATE TABLE pkgStats (
RowID INT identity(1, 1) NOT NULL PRIMARY KEY
,PackagePath VARCHAR(8000) NOT NULL
,PackageXML XML NOT NULL
,PackageCreatorName VARCHAR(1000) NULL
,PackageProtectionLevel varchar(100) null
);
CREATE TABLE ConnectionDetails (
ConnectionManager VARCHAR(5000)
,ConnectionManagerID VARCHAR(100)
);
CREATE TABLE PackageAnalysis (
TableRowID INT identity(1, 1) NOT NULL
,PackageSequenceNo INT
,SequenceNo INT
,RowID INT
,PackagePath VARCHAR(5000)
,PackageName VARCHAR(max)
,Category VARCHAR(1000)
,PackageCreatorName VARCHAR(1000)
,PackageProtectionLevel Varchar(100)
,TaskPath VARCHAR(max)
,ObjectName VARCHAR(500)
,ObjectType VARCHAR(max)
,ObjectTypeDescription VARCHAR(max)
,DelayValidationPropertyValue VARCHAR(10)
,ObjectValue VARCHAR(max)
,ExpressionValue VARCHAR(5000)
,ConnectionManager VARCHAR(5000)
,SourceColumn VARCHAR(5000)
,DestinationColumn VARCHAR(5000)
,SortKeyPosition varchar(10)
,DerivedValue VARCHAR(5000)
,IsDisabled VARCHAR(10)
,ResultSetParameterName VARCHAR(1000)
,ParameterBindingSequence VARCHAR(1000)
,ParameterBindingParameterName VARCHAR(1000)
--,ObjectPathFrom VARCHAR(8000)
--,ObjectPathTo VARCHAR(8000)
,ExecutePackageExpression VARCHAR(8000)
,ExecutedPackageName VARCHAR(8000)
,ExecutePackageConnection VARCHAR(8000)
,Script VARCHAR(max)
,Variable VARCHAR(1000)
,ConnectionManagerID VARCHAR(100)
,LookupJoins VARCHAR(8000)
,IsSortedProperty varchar(10)
,MultihashcolumnSortPosition varchar(max)
,RetainSameConnectionProperty varchar(100)
);
CREATE CLUSTERED INDEX CX_ControlFlow ON PackageAnalysis (TableRowID);
CREATE TABLE TblPrecedenceConstraint (
RowID INT
,PackagePath VARCHAR(2000)
,TaskFrom VARCHAR(1000)
,Taskto VARCHAR(1000)
,PrecOrder INT
,PrecOrder1 INT
,TaskType VARCHAR(1000)
);
CREATE TABLE TblVariableDetails (
VariableRowID INT identity(1, 1) NOT NULL
,RowID INT
,PackagePath VARCHAR(5000)
,PackageName VARCHAR(max)
,Category VARCHAR(1000)
,PackageCreatorName VARCHAR(1000)
,TaskPath VARCHAR(max)
,ObjectName VARCHAR(500)
,ObjectType VARCHAR(max)
,ObjectValue VARCHAR(max)
,ExpressionValue VARCHAR(5000)
,VariableHexValue VARCHAR(1000)
);
CREATE CLUSTERED INDEX CX_Variable ON TblVariableDetails (VariableRowID);
CREATE TABLE TblParameterDetails (
ParameterRowID INT identity(1, 1) NOT NULL
,RowID INT
,PackagePath VARCHAR(5000)
,PackageName VARCHAR(max)
,Category VARCHAR(1000)
,PackageCreatorName VARCHAR(1000)
,TaskPath VARCHAR(max)
,ObjectName VARCHAR(500)
,ObjectType VARCHAR(max)
,ObjectValue VARCHAR(max)
,ExpressionValue VARCHAR(5000)
,ParameterHexValue VARCHAR(1000)
);
CREATE CLUSTERED INDEX CX_Variable ON TblParameterDetails (ParameterRowID);
CREATE TABLE TblControlFlowDetails (
ControlFlowDetailsRowID INT identity(1, 1) NOT NULL
,RowID INT
,TaskPath VARCHAR(8000)
,TaskName VARCHAR(2000)
,TaskTypeDescription VARCHAR(MAX)
,TaskType VARCHAR(5000)
,DelayValidationPropertyValue VARCHAR(100)
,DFTQuery XML
,SqlTaskQry XML
,ExecPkgTaskQry XML
,ScriptTaskQry XML
,IsDisabled VARCHAR(10)
,ExpressionValue VARCHAR(5000)
);
CREATE CLUSTERED INDEX CX_ControlFlow ON TblControlFlowDetails (
ControlFlowDetailsRowID
,RowID
);
CREATE NONCLUSTERED INDEX NCX_ControlFlow ON TblControlFlowDetails (RowID);
CREATE TABLE TblScriptTaskdetails (
RowID INT
,ControlFlowDetailsRowID INT
,Script VARCHAR(max)
);
CREATE CLUSTERED INDEX CX_TblScriptTaskdetails ON TblScriptTaskdetails (
ControlFlowDetailsRowID
,RowID
);
CREATE TABLE TblExecutePackageTaskDetails (
RowID INT
,ControlFlowDetailsRowID INT
,ExecutePackageExpression VARCHAR(1000)
,ExecutedPackageName VARCHAR(1000)
,ExecutePackageConnection VARCHAR(1000)
);
CREATE CLUSTERED INDEX CX_TblExecutePackageTaskDetails ON TblExecutePackageTaskDetails (
ControlFlowDetailsRowID
,RowID
);
CREATE TABLE TblSQLTaskDetails (
RowID INT
,ControlFlowDetailsRowID INT
,ResultSetParameterName VARCHAR(1000)
,ParameterBindingSequence VARCHAR(1000)
,ParameterBindingParameterName VARCHAR(1000)
);
CREATE CLUSTERED INDEX CX_TblSQLTaskDetails ON TblSQLTaskDetails (
ControlFlowDetailsRowID
,RowID
);
CREATE TABLE TblDFTTaskDetails (
DataFlowDetailsRowID INT identity(1, 1) NOT NULL
,RowID INT
,DFTTaskName VARCHAR(max)
,DFTTasksPath VARCHAR(max)
,DFTTaskType VARCHAR(max)
,DFTTaskType1 VARCHAR(max)
,DFTRowSet VARCHAR(max)
,ParameterBindingParameterName VARCHAR(2000)
,DFTSQLCommand VARCHAR(max)
,DFTConnectionManager VARCHAR(max)
,Variable VARCHAR(max)
,InputQry XML
,OutputQry XML
,IsSortedProperty VARCHAR(10)
,MultihashcolumnSortPosition varchar(max)
);
CREATE CLUSTERED INDEX CX_TblDFTTaskDetails ON TblDFTTaskDetails (
DataFlowDetailsRowID
,RowID
);
CREATE NONCLUSTERED INDEX NCX_TblDFTTaskDetails ON TblDFTTaskDetails (RowID);
CREATE TABLE TblSrcDestDetails (
DataFlowDetailsRowID INT
,RowID INT
,SourceColumn VARCHAR(8000)
,DestinationColumn VARCHAR(8000)
,SortKeyPosition varchar(10)
);
CREATE CLUSTERED INDEX CX_TblSrcDestDetails ON TblSrcDestDetails (
DataFlowDetailsRowID
,RowID
);
CREATE TABLE TblDerivedTaskDetails (
DataFlowDetailsRowID INT
,RowID INT
,DerivedValue VARCHAR(8000)
,SourceColumn VARCHAR(8000)
,DestinationColumn VARCHAR(8000)
);
CREATE CLUSTERED INDEX CX_TblDerivedTaskDetails ON TblDerivedTaskDetails (
DataFlowDetailsRowID
,RowID
);
CREATE TABLE TblLookupTaskDetails (
DataFlowDetailsRowID INT
,RowID INT
,LookupJoins VARCHAR(8000)
,SourceColumn VARCHAR(8000)
,DestinationColumn VARCHAR(8000)
);
CREATE CLUSTERED INDEX CX_TblLookupTaskDetails ON TblLookupTaskDetails (
DataFlowDetailsRowID
,RowID
);
--------------------------create all tables-------------------------------------------
-------------------Iterate over all dtsx files from folder------------------------------
SET @Path = @Path + '\*.dtsx';
DECLARE @MyFiles TABLE (
MyID INT IDENTITY(1, 1) PRIMARY KEY
,FullPath VARCHAR(8000)
);
DECLARE @CommandLine VARCHAR(8000);
SELECT @CommandLine = LEFT('dir "' + @Path + '" /A-D /B /S ', 8000);
INSERT INTO @MyFiles (FullPath)
EXECUTE xp_cmdshell @CommandLine;
DELETE
FROM @MyFiles
WHERE FullPath IS NULL
OR FullPath = 'File Not Found'
OR FullPath = 'The system cannot find the path specified.'
OR FullPath = 'The system cannot find the file specified.';
--select * from @MyFiles
DECLARE @FullPath VARCHAR(2000);
DECLARE @counter INT = 0
,@totalpkgcount INT;
SELECT @totalpkgcount = COUNT(*)
FROM @MyFiles;
WHILE @counter <= @totalpkgcount
BEGIN
SELECT @FullPath = FullPath
FROM @MyFiles
WHERE MyID = @counter;
DECLARE @sql NVARCHAR(max);
SET @sql = '
INSERT INTO pkgStats (PackagePath,PackageXML)
select ''@FullPath'' as PackagePath
, cast(BulkColumn as XML) as PackageXML
from openrowset(bulk ''@FullPath'',
single_blob) as pkgColumn';
SELECT @sql = REPLACE(@sql, '@FullPath', @FullPath);
EXEC sp_executesql @sql;
SET @counter = @counter + 1;
END
-------------------Iterate over all dtsx files from folder------------------------------
--select * from pkgStats
------------add package creator name---------------------------
;
WITH CTE_PkgLevel
AS (
SELECT RowID
,PackagePath
,PackageXML.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
/DTS:Executable[1]/@DTS:CreatorName', 'nvarchar(500)') AS PackageCreatorName
,PackageXML.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
/DTS:Executable[1]/@DTS:ProtectionLevel', 'nvarchar(500)') AS PackageProtectionLevel
FROM pkgStats pkg
)
UPDATE P
SET PackageCreatorName = C.PackageCreatorName,
PackageProtectionLevel = C.PackageProtectionLevel
FROM pkgStats P
INNER JOIN CTE_PkgLevel C ON P.RowID = C.RowID;
------------add package creator name---------------------------
PRINT '---------------Get all connections-----------------------------------';
WITH CTE_PkgLevel
AS (
SELECT pkg.RowID
,pkg.PackagePath
,cfnodes.x.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:ObjectName[1]', 'varchar(max)') AS ConnectionManagerName
,cfnodes.x.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:CreationName[1]', 'varchar(max)') AS ConnectionManagerType
--, cfnodes1.y.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:ConnectionString[1]', 'varchar(max)')
,cfnodes.x.query('.').query('declare namespace p1="www.microsoft.com/SqlServer/Dts";./p1:ConnectionManager/p1:ObjectData/p1:ConnectionManager') AS ConnectionStringQry
,cfnodes.x.query('.').query('declare namespace p1="www.microsoft.com/SqlServer/Dts";./p1:ConnectionManager/p1:PropertyExpression') AS ExpressionQry
,cfnodes.x.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:DTSID[1]', 'varchar(100)') AS ConnectionManagerID
,pkg.PackageCreatorName
FROM pkgStats pkg
CROSS APPLY pkg.PackageXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";(//DTS:Executable/DTS:ConnectionManagers/*)') AS cfnodes(x)
)
,CTE_PkgConLevel
AS (
SELECT pkg.RowID
,pkg.PackagePath
,pkg.ConnectionManagerName
,pkg.ConnectionManagerType
,ConnectionManagerID
--,pkg.ConnectionStringQry
,(
SELECT TOP 1 cfnodes.x.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";@p1:ConnectionString[1]', 'nvarchar(max)') AS ConnectionString
FROM pkg.ConnectionStringQry.nodes('./*') AS cfnodes(x)
) AS ConnectionString
,(
SELECT TOP 1 cfnodes.x.value('.', 'nvarchar(max)') AS ExpressionValue
FROM pkg.ExpressionQry.nodes('./*') AS cfnodes(x)
) AS ExpressionValue
,PackageCreatorName
,(
SELECT TOP 1 cfnodes.x.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";@p1:Retain[1]', 'nvarchar(max)') AS RetainSameConnectionProperty
FROM pkg.ConnectionStringQry.nodes('./*') AS cfnodes(x)
) AS RetainSameConnectionProperty
FROM CTE_PkgLevel pkg
)
INSERT INTO PackageAnalysis (
RowID
,PackagePath
,PackageName
,Category
,PackageCreatorName
,ObjectName
,ObjectType
,DelayValidationPropertyValue
,ObjectValue
,ExpressionValue
,ConnectionManagerID
,RetainSameConnectionProperty
)
SELECT DISTINCT pkg.RowID
,pkg.PackagePath
,REPLACE(pkg.PackagePath, REPLACE(@Path, '*.dtsx', ''), '') PackageName
,'Connection' AS CategoryC
,pkg.PackageCreatorName
,cast(pkg.ConnectionManagerName AS VARCHAR(max)) AS ObjectName
,cast(pkg.ConnectionManagerType AS VARCHAR(max)) AS ObjectType
,'NA' AS DelayValidationPropertyValue
,cast(pkg.ConnectionString AS VARCHAR(max)) AS ObjectValue
,cast(ExpressionValue AS VARCHAR(max)) AS ExpressionValue
,ConnectionManagerID
,RetainSameConnectionProperty
FROM CTE_PkgConLevel pkg;
PRINT '---------------Get all connections-----------------------------------';
PRINT '------------------------Get all variable details-------------------------------------';
WITH CTE_PkgLevel
AS (
SELECT pkg.RowID
,pkg.PackagePath
,cfnodes.x.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:ObjectName[1]', 'varchar(max)') AS VariableName
,cfnodes.x.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:DTSID[1]', 'varchar(max)') AS VariableHexValue
,cfnodes.x.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:Expression[1]', 'varchar(max)') AS ExpressionValue
,cfnodes.x.query('.').query('declare namespace p1="www.microsoft.com/SqlServer/Dts";./p1:Variable/p1:VariableValue') AS VariableQry
,PackageCreatorName
FROM pkgStats pkg
CROSS APPLY pkg.PackageXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";(//DTS:Executable/DTS:Variables/*)') AS cfnodes(x)
)
--select * from CTE_PkgLevel
,CTE_PkgVarLevel
AS (
SELECT pkg.RowID
,pkg.PackagePath
,pkg.VariableName
,pkg.ExpressionValue
,VariableHexValue
,(
SELECT TOP 1 cfnodes.x.value('.', 'nvarchar(max)') AS VariableValue
FROM pkg.VariableQry.nodes('./*') AS cfnodes(x)
) AS VariableValue
--,pkg.ConnectionStringQry
,(
SELECT TOP 1 cfnodes.x.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";@p1:DataType[1]', 'nvarchar(max)') AS DataType
FROM pkg.VariableQry.nodes('./*') AS cfnodes(x)
) AS VariableDataType
,PackageCreatorName
FROM CTE_PkgLevel pkg
)
INSERT INTO TblVariableDetails (
RowID
,PackagePath
,PackageName
,Category
,PackageCreatorName
,ObjectName
,ObjectType
,ObjectValue
,ExpressionValue
,VariableHexValue
)
SELECT DISTINCT Tblvar.RowID
,Tblvar.PackagePath
,REPLACE(Tblvar.PackagePath, REPLACE(@Path, '*.dtsx', ''), '') PackageName
,'Variable' AS Category
,Tblvar.PackageCreatorName
,cast(Tblvar.VariableName AS VARCHAR(max)) AS TaskName
,cast(Tblvar.VariableDataType AS VARCHAR(max)) AS TaskType
,isnull(cast(Tblvar.VariableValue AS VARCHAR(max)), '') AS VariableValue
,isnull(cast(tblvar.ExpressionValue AS VARCHAR(max)), '') ExpressionValue
,VariableHexValue
FROM CTE_PkgVarLevel Tblvar;
INSERT INTO PackageAnalysis (
RowID
,PackagePath
,PackageName
,Category
,PackageCreatorName
,ObjectName
,ObjectType
,DelayValidationPropertyValue
,ObjectValue
,ExpressionValue
)
SELECT DISTINCT Tblvar.RowID
,Tblvar.PackagePath
,PackageName
,'Variable' AS Category
,Tblvar.PackageCreatorName
,ObjectName
,ObjectType
,'NA' AS DelayValidationPropertyValue
,ObjectValue
,ExpressionValue
FROM TblVariableDetails Tblvar;
PRINT '------------------------Get all variable details-------------------------------------';
PRINT '------------------------Get all parameter details-------------------------------------';
WITH CTE_PkgLevel
AS (
SELECT pkg.RowID
,pkg.PackagePath
,cfnodes.x.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:ObjectName[1]', 'varchar(max)') AS ParameterName
,cfnodes.x.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:DTSID[1]', 'varchar(max)') AS ParameterHexValue
,cfnodes.x.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:Expression[1]', 'varchar(max)') AS ExpressionValue
,cfnodes.x.query('.').query('declare namespace p1="www.microsoft.com/SqlServer/Dts";./p1:PackageParameter/p1:Property') AS ParameterQry
,PackageCreatorName
FROM pkgStats pkg
CROSS APPLY pkg.PackageXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";(//DTS:Executable/DTS:PackageParameters/*)') AS cfnodes(x)
)
--select * from CTE_PkgLevel
,CTE_PkgVarLevel
AS (
SELECT pkg.RowID
,pkg.PackagePath
,pkg.ParameterName
,pkg.ExpressionValue
,ParameterHexValue
,(
SELECT TOP 1 cfnodes.x.value('.', 'nvarchar(max)') AS ParameterValue
FROM pkg.ParameterQry.nodes('./*') AS cfnodes(x)
) AS ParameterValue
--,pkg.ConnectionStringQry
,(
SELECT TOP 1 cfnodes.x.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";@p1:DataType[1]', 'nvarchar(max)') AS DataType
FROM pkg.ParameterQry.nodes('./*') AS cfnodes(x)
) AS ParameterDataType
,PackageCreatorName
FROM CTE_PkgLevel pkg
)
INSERT INTO tblParameterDetails(
RowID
,PackagePath
,PackageName
,Category
,PackageCreatorName
,ObjectName
,ObjectType
,ObjectValue
,ExpressionValue
,ParameterHexValue
)
SELECT DISTINCT Tblvar.RowID
,Tblvar.PackagePath
,REPLACE(Tblvar.PackagePath, REPLACE(@Path, '*.dtsx', ''), '') PackageName
,'Variable' AS Category
,Tblvar.PackageCreatorName
,cast(Tblvar.ParameterName AS VARCHAR(max)) AS TaskName
,cast(Tblvar.ParameterDataType AS VARCHAR(max)) AS TaskType
,isnull(cast(Tblvar.ParameterValue AS VARCHAR(max)), '') AS ParameterValue
,isnull(cast(tblvar.ExpressionValue AS VARCHAR(max)), '') ExpressionValue
,ParameterHexValue
FROM CTE_PkgVarLevel Tblvar;
INSERT INTO PackageAnalysis (
RowID
,PackagePath
,PackageName
,Category
,PackageCreatorName
,ObjectName
,ObjectType
,DelayValidationPropertyValue
,ObjectValue
,ExpressionValue
)
SELECT DISTINCT Tblvar.RowID
,Tblvar.PackagePath
,PackageName
,'Parameter' AS Category
,Tblvar.PackageCreatorName
,ObjectName
,ObjectType
,'NA' AS DelayValidationPropertyValue
,ObjectValue
,ExpressionValue
FROM tblParameterDetails Tblvar;
PRINT '------------------------Get all parameter details-------------------------------------';
PRINT '----------------Get precedence constraint details------------------------------';
WITH CTE_CFPrec
AS (
SELECT pkg.RowID
,pkg.PackagePath
,cfnodes.x.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:From[1]', 'varchar(max)') AS TaskFrom
,cfnodes.x.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:To[1]', 'nvarchar(max)') AS TaskTo
--,dft.x.value('@refId[1]','varchar(max)')
FROM pkgStats pkg
CROSS APPLY pkg.PackageXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";//DTS:Executable/DTS:PrecedenceConstraints/*') AS cfnodes(x)
)
,CTE_DFTPrec
AS (
SELECT pkg.RowID
,pkg.PackagePath
,cfnodes.x.value('./@startId[1]', 'varchar(max)') AS TaskFrom
,cfnodes.x.value('./@endId[1]', 'nvarchar(max)') AS TaskTo
--,dft.x.value('@refId[1]','varchar(max)')
FROM pkgStats pkg
CROSS APPLY pkg.PackageXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";//DTS:Executable/DTS:ObjectData/pipeline/paths/*') AS cfnodes(x)
)
INSERT INTO TblPrecedenceConstraint (
RowID
,PackagePath
,TaskFrom
,Taskto
,TaskType
)
SELECT RowID
,PackagePath
,TaskFrom
,TaskTo
,TaskType
FROM (
SELECT RowID
,PackagePath
,TaskFrom
,TaskTo
,'Control Flow' AS TaskType
FROM CTE_CFPrec
UNION ALL
SELECT RowID
,PackagePath
,substring(TaskFrom, 1, charindex('.', TaskFrom) - 1) AS TaskFrom
,substring(TaskTo, 1, CHARINDEX('.', TaskTo) - 1)
,'Data Flow'
FROM CTE_DFTPrec
) t;
CREATE CLUSTERED INDEX CIX_TblPrecedenceConstraint ON TblPrecedenceConstraint (RowID);
CREATE NONCLUSTERED INDEX NCIX_TblPrecedenceConstraint ON TblPrecedenceConstraint (
TaskFrom
,TaskTo
);
SELECT DISTINCT RowID,TaskFrom,IsParentLevel,Row_number() OVER (
PARTITION BY rowid ORDER BY level) level INTO #tempprecedence1
FROM
(
SELECT distinct t1.Rowid
,t1.TaskFrom
,1 AS isparentlevel
, len(t1.TaskFrom) - len(replace(t1.TaskFrom, '\', '')) AS level
FROM TblPrecedenceConstraint t1
LEFT JOIN TblPrecedenceConstraint t2 ON t1.rowid = t2.RowID
AND t1.TaskFrom = t2.taskTo
WHERE t2.taskto IS NULL
)T
CREATE CLUSTERED INDEX CIX_TblPrecedenceConstraint1 ON #tempprecedence1 (RowID);
CREATE NONCLUSTERED INDEX NCIX_TblPrecedenceConstraint1 ON #tempprecedence1 (level);
CREATE TABLE #tempprecedence2 (
ROWID INT
,TaskFrom VARCHAR(1000)
,task VARCHAR(1000)
,parentlevel INT
,LEVEL INT
);
DECLARE @cntlevel INT
,@cnt INT = 1;
SELECT @cntlevel = count(*)
FROM #tempprecedence1;
WHILE @cnt <= @cntlevel
BEGIN
;
WITH cte
AS (
SELECT rowid
,taskfrom
,taskfrom AS task
,LEVEL
,1 AS cnt
FROM #tempprecedence1
WHERE LEVEL = @cnt
UNION ALL
SELECT c.rowid
,c.taskfrom
,t.taskto
,LEVEL
,cnt + 1
FROM TblPrecedenceConstraint t
INNER JOIN cte c ON t.RowID = c.RowID
AND c.task = t.taskfrom
)
INSERT INTO #tempprecedence2 (
rowid
,taskfrom
,task
,parentlevel
,LEVEL
)
SELECT rowid
,taskfrom
,task
,@cnt
,cnt
FROM cte;
SELECT @cnt = @cnt + 1;
END
SELECT rowid
,task
,parentlevel
,LEVEL
,row_number() OVER (
PARTITION BY rowid ORDER BY parentlevel
,LEVEL
) trank
INTO #tempprecedence3
FROM #tempprecedence2;
CREATE CLUSTERED INDEX CIX_TblPrecedenceConstraint1 ON #tempprecedence3 (RowID);
CREATE NONCLUSTERED INDEX NCIX_TblPrecedenceConstraint1 ON #tempprecedence3 (task);
SELECT t.rowid
,isnull(t1.task,t.task) as task
,t.LEVEL tlevel
,isnull(max(t1.LEVEL),max(t.level)) level
,isnull(max(t1.trank),max(t.trank)) trank
INTO #tempprecedence4
FROM #tempprecedence3 t
LEFT JOIN #tempprecedence3 t1 ON t.rowid = t1.rowid
AND t1.task LIKE '%' + t.task + '\%'
GROUP BY t.rowid
,t1.task
,t.task
,t.LEVEL
--,t1.LEVEL
ORDER BY LEVEL
,trank;
--select t.rowid,t1.task,t.level tlevel,t1.level, (t1.trank) trank into #tempprecedence4
--from #tempprecedence3 t
--join #tempprecedence3 t1
--on t.rowid = t1.rowid
--and t1.task like '%'+t.task+'%'
-- order by trank
SELECT t.rowid
,t.task
,replace(replace(replace((
SELECT tlevel
FROM #tempprecedence4 t1
WHERE t1.rowid = t.rowid
AND t1.task = t.task
FOR XML path('')
), '</tlevel><tlevel>', '/'), '</tlevel>', '/'), '<tlevel>', '') sequenceno,trank
INTO #tempprecedence5
FROM #tempprecedence4 t
GROUP BY t.rowid
,t.task
,t.trank
ORDER BY rowid
,trank;
SELECT rowid,task, sequenceno, cast('/1/'+sequenceno+ cast(trank as varchar)+'/' as hierarchyid)rid into #tempprecedence5_Hierarchy FROM #tempprecedence5
SELECT rowid
,task
,row_number() OVER (
PARTITION BY rowid ORDER BY rid
) sequenceno
INTO #tempprecedence6
FROM #tempprecedence5_Hierarchy;
CREATE CLUSTERED INDEX CIX_TblPrecedenceConstraint1 ON #tempprecedence6 (RowID);
CREATE NONCLUSTERED INDEX NCIX_TblPrecedenceConstraint1 ON #tempprecedence6 (task);
/*
select t1.task,t1.parentlevel,t.task as t1,row_number() over(partition by t1.rowid order by t.level) level,t.rowid into #tempprecedence3
from #tempprecedence2 t
join #tempprecedence2 t1
on charindex(t.task, t1.taskFrom)>0
and t1.parentlevel >1 and t.parentlevel =1
and t.rowid =t1.rowid
order by t.rowid, t1.parentlevel ,t.level
select rowid, task,row_number() over(partition by rowid order by max(sequenceno)) sequenceno into #tempprecedence4
from(
select distinct t1.rowid,isnull(t2.task,t1.task) Task,row_number() over(partition by t1.rowid order by t1.parentlevel,t1.level,isnull(t2.level,1)) sequenceno
from #tempprecedence2 t1
left join #tempprecedence3 t2
on t1.rowid = t2.rowid
and t1.task = t2.t1
where t1.parentlevel =1 )t
group by rowid,task
*/
PRINT '----------------Get precedence constraint details------------------------------';
PRINT '----------------Get all Control flow tasks details------------------------------';
PRINT '----------------Get all data flow tasks details------------------------------';
WITH CTE_CFLevel
AS (
SELECT pkg.RowID
,cfnodes.x.query('.').query('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
./DTS:Executable[@DTS:ExecutableType=''SSIS.Pipeline.3'']/DTS:ObjectData/pipeline/components/component') DFTQuery
,cfnodes.x.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:refId[1]', 'varchar(max)') AS TaskPath
,cfnodes.x.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:ObjectName[1]', 'nvarchar(max)') AS TaskName
,cfnodes.x.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:Description', 'nvarchar(max)') AS TaskTypeDescription
,cfnodes.x.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:ExecutableType', 'nvarchar(max)') AS TaskType
,cfnodes.x.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:DelayValidation', 'nvarchar(max)') AS DelayValidationPropertyValue
,ISNULL(cfnodes.x.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:Disabled', 'nvarchar(max)'), 'False') AS IsDisabled
,'EvalExpression = ' + cfnodes.x.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";./@p1:EvalExpression', 'nvarchar(max)') AS ForloopEvalExpression
,cfnodes.x.query('.').query('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
./DTS:Executable[@DTS:ExecutableType=''Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'']/DTS:ObjectData/*') AS SqlTaskQry
,cfnodes.x.query('.').query('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
./DTS:Executable[@DTS:ExecutableType=''SSIS.ExecutePackageTask.3'']/*') ExecPkgTaskQry
,cfnodes.x.query('.').query('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
./DTS:Executable[@DTS:ExecutableType=''Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'']/DTS:ObjectData/ScriptProject/*') ScriptTaskQry
FROM pkgStats pkg
CROSS APPLY pkg.PackageXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";//DTS:Executable/DTS:Executables/*') AS cfnodes(x)
)
INSERT INTO TblControlFlowDetails (
RowID
,TaskPath
,TaskName
,TaskTypeDescription
,TaskType
,DelayValidationPropertyValue
,DFTQuery
,SqlTaskQry
,ExecPkgTaskQry
,ScriptTaskQry
,IsDisabled
,ExpressionValue
)
SELECT RowID
,TaskPath
,TaskName
,TaskTypeDescription
,TaskType
,DelayValidationPropertyValue
,DFTQuery
,SqlTaskQry
,ExecPkgTaskQry
,ScriptTaskQry
,IsDisabled
,ForloopEvalExpression
FROM CTE_CFLevel;
PRINT '---------- Insert Script task details-----------------------';
INSERT INTO TblScriptTaskdetails (
RowID
,ControlFlowDetailsRowID
,Script
)
SELECT RowID
,CF.ControlFlowDetailsRowID
,cfnodes1.x.value('./ProjectItem[@Name=''ScriptMain.cs''][1]', 'varchar(max)') Script
FROM TblControlFlowDetails cf
CROSS APPLY Cf.ScriptTaskQry.nodes('.') AS cfnodes1(x);
PRINT '---------- Insert Execute package task details-----------------------';
INSERT INTO TblExecutePackageTaskDetails (
RowID
,ControlFlowDetailsRowID
,ExecutePackageExpression
,ExecutedPackageName
,ExecutePackageConnection
)
SELECT RowID
,cf.ControlFlowDetailsRowID
,cfnodes1.x.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
./DTS:PropertyExpression[1]', 'varchar(1000)') ExecutePackageExpression
,cfnodes1.x.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
(./DTS:ObjectData/ExecutePackageTask/PackageName)[1]', 'varchar(1000)') ExecutedPackageName
,cfnodes1.x.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
(./DTS:ObjectData/ExecutePackageTask/Connection)[1]', 'varchar(1000)') ExecutePackageConnection
FROM TblControlFlowDetails cf
CROSS APPLY Cf.ExecPkgTaskQry.nodes('.') AS cfnodes1(x);
UPDATE execpkg
SET ExecutePackageConnection = pkg.ObjectValue
FROM TblExecutePackageTaskDetails execpkg
INNER JOIN packageanalysis pkg ON execpkg.RowID = pkg.RowID
AND pkg.category = 'Connection'
AND pkg.ConnectionManagerID = execpkg.ExecutePackageConnection;
PRINT '---------- Insert Execute SQL task details-----------------------';
INSERT INTO TblSQLTaskDetails (
RowID
,ControlFlowDetailsRowID
,ParameterBindingSequence
,ParameterBindingParameterName
)
SELECT DISTINCT cf.RowID
,cf.ControlFlowDetailsRowID
--,cfnodes1.x.value('declare namespace SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask"; (./@SQLTask:DtsVariableName)','varchar(1000)') as ResultSetParameterName
,cfnodes.x.value('declare namespace SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask"; (./@SQLTask:ParameterName)', 'varchar(1000)') AS ParameterBindingSequence
,cfnodes.x.value('declare namespace SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask"; (./@SQLTask:DtsVariableName)', 'varchar(1000)') AS ParameterBindingParameterName
FROM TblControlFlowDetails cf
OUTER APPLY Cf.SqlTaskQry.nodes('declare namespace SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask";./SQLTask:SqlTaskData/SQLTask:ParameterBinding') AS cfnodes(x)
ORDER BY 1
,2
,3;
UPDATE sqltsk
SET ResultSetParameterName = CF.ResultSetParameterName
FROM TblSQLTaskDetails sqltsk
INNER JOIN (
SELECT DISTINCT cf.RowID
,cf.ControlFlowDetailsRowID
,cast(ROW_NUMBER() OVER (
PARTITION BY cf.ControlFlowDetailsRowID ORDER BY (
SELECT 1
)
) - 1 AS VARCHAR) rid
,cfnodes1.x.value('declare namespace SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask"; (./@SQLTask:DtsVariableName)', 'varchar(1000)') AS ResultSetParameterName
FROM TblControlFlowDetails cf
OUTER APPLY Cf.SqlTaskQry.nodes('declare namespace SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask";./SQLTask:SqlTaskData/SQLTask:ResultBinding') AS cfnodes1(x)
) CF ON sqltsk.ControlFlowDetailsRowID = CF.ControlFlowDetailsRowID
AND cf.rid = ISNULL(sqltsk.ParameterBindingSequence, 0)
PRINT '---------- Insert DFT details-----------------------';
INSERT INTO TblDFTTaskDetails (
RowID
,DFTTasksPath
,DFTTaskName
,DFTTaskType
,DFTTaskType1
,DFTRowSet
,ParameterBindingParameterName
,DFTSQLCommand
,DFTConnectionManager
,Variable
,IsSortedProperty
,InputQry
,OutputQry
,MultihashcolumnSortPosition
)
SELECT RowID
,dftnodes.x.value('@refId[1]', 'varchar(max)') AS DFTTasksPath
,dftnodes.x.value('@name[1]', 'varchar(max)') AS DFTTaskName
,dftnodes.x.value('@description[1]', 'varchar(max)') AS DFTTaskType
,dftnodes.x.value('@contactInfo[1]', 'varchar(max)') AS DFTTaskType1
,dftnodes.x.value('data(./properties/property[@name=''OpenRowset''] )[1]', 'varchar(max)') DFTRowSet
,dftnodes.x.value('data(./properties/property[@name=''ParameterMapping''] )[1]', 'varchar(max)') ParameterBindingParameterName
,CASE
WHEN isnull(dftnodes.x.value('data(./properties/property[@name=''SqlCommand''])[1]', 'varchar(max)'), '') = ''
THEN isnull(dftnodes.x.value('data(./properties/property[@name=''SqlCommandVariable''])[1]', 'varchar(max)'), '')
ELSE isnull(dftnodes.x.value('data(./properties/property[@name=''SqlCommand''])[1]', 'varchar(max)'), '')
END DFTSQLCommand
,dftnodes.x.value('data(./connections/connection/@connectionManagerID)[1]', 'varchar(max)') DFTConnectionManager
,dftnodes.x.value('data(./properties/property[@name=''VariableName''] )[1]', 'varchar(max)') Variable
,dftnodes.x.value('data(./outputs/output/@isSorted)[1]', 'varchar(10)') IsSortedProperty
,lineage.x.query('.') InputQry
,outputvalue.x.query('.') OutputQry
,outputvalue.x.value('data(./properties/property[@name=''InputColumnLineageIDs''] )[1]', 'varchar(max)') as MultihashcolumnSortPosition
FROM TblControlFlowDetails pkglvl
CROSS APPLY pkglvl.DFTQuery.nodes('./*') AS dftnodes(x)
OUTER APPLY dftnodes.x.nodes('./inputs/input/inputColumns/*') AS lineage(x)
OUTER APPLY dftnodes.x.nodes('./outputs/output/outputColumns/*') AS outputvalue(x);
PRINT '---------- Insert DFT details-----------------------';
PRINT '---------- Insert DFT Source and destination details-----------------------';
INSERT INTO TblSrcDestDetails (
RowID
,DataFlowDetailsRowID
,SourceColumn
,DestinationColumn
,SortKeyPosition
)
SELECT RowID
,DataFlowDetailsRowID
,lineage.x.value('./@lineageId[1]', 'varchar(max)') AS SourceColumn
,COALESCE(lineage.x.value('./@name[1]', 'varchar(max)'),lineage.x.value('./@externalMetadataColumnId[1]', 'varchar(max)'), lineage.x.value('./@refId[1]', 'varchar(max)')) DestinationColumn
,lineage.x.value('./@sortKeyPosition[1]', 'varchar(200)') SortKeyPosition
FROM TblDFTTaskDetails pkglvl
CROSS APPLY pkglvl.InputQry.nodes('./*') AS lineage(x);
INSERT INTO TblSrcDestDetails (
RowID
,DataFlowDetailsRowID
,SourceColumn
,DestinationColumn
,SortKeyPosition
)
SELECT RowID
,DataFlowDetailsRowID
,lineage.x.value('./@lineageId[1]', 'varchar(max)') SourceColumn
,COALESCE(lineage.x.value('./@name[1]', 'varchar(max)'),lineage.x.value('./@externalMetadataColumnId[1]', 'varchar(max)'), lineage.x.value('./@refId[1]', 'varchar(max)')) DestinationColumn
,lineage.x.value('./@sortKeyPosition[1]', 'varchar(200)') SortKeyPosition
FROM TblDFTTaskDetails pkglvl
CROSS APPLY pkglvl.OutputQry.nodes('./*') AS lineage(x);
PRINT '---------- Insert DFT Source and destination details-----------------------';
PRINT '---------- Insert DFT Derived task details-----------------------';
INSERT INTO TblDerivedTaskDetails (
RowID
,DataFlowDetailsRowID
,DerivedValue
,SourceColumn
,DestinationColumn
)
SELECT DISTINCT RowID
,DataFlowDetailsRowID
,ISNULL(inputvalue.x.value('data(./properties/property[@description =''Derived Column Friendly Expression''])[1]', 'varchar(max)'), inputvalue.x.value('data(./properties/property[@name =''FriendlyExpression''])[1]', 'varchar(max)')) DerivedValue
,inputvalue.x.value('./@lineageId[1]', 'varchar(max)') SourceColumn
,inputvalue.x.value('./@refId[1]', 'varchar(max)') DestinationColumn
FROM TblDFTTaskDetails pkglvl
CROSS APPLY pkglvl.InputQry.nodes('./*') AS inputvalue(x)
UNION ALL
SELECT DISTINCT RowID
,DataFlowDetailsRowID
,ISNULL(outputvalue.x1.value('data(./properties/property[@description =''Derived Column Friendly Expression''])[1]', 'varchar(max)'), outputvalue.x1.value('data(./properties/property[@name =''FriendlyExpression''])[1]', 'varchar(max)')) DerivedValue
,outputvalue.x1.value('./@lineageId[1]', 'varchar(max)') SourceColumn
,outputvalue.x1.value('./@refId[1]', 'varchar(max)') DestinationColumn
FROM TblDFTTaskDetails pkglvl
CROSS APPLY pkglvl.OutputQry.nodes('./*') AS outputvalue(x1)
WHERE DFTTASKTYPE1 LIKE '%DERIVED%';
PRINT '---------- Insert DFT Derived task details-----------------------';
PRINT '---------- Insert DFT lookup task details-----------------------';
INSERT INTO TblLookupTaskDetails (
RowID
,DataFlowDetailsRowID
,LookupJoins
,SourceColumn
,DestinationColumn
)
SELECT RowID
,DataFlowDetailsRowID
,outputvalue.x.value('./@lineageId[1]', 'varchar(max)') + '----->' + outputvalue.x.value('data(./properties/property[@name =''JoinToReferenceColumn''])[1]', 'varchar(max)') LookupJoins
,outputvalue1.x.value('data(./properties/property[@name =''CopyFromReferenceColumn''])[1]', 'varchar(max)') SourceColumn
,outputvalue1.x.value('./@refId[1]', 'varchar(max)') DestinationColumn
FROM TblDFTTaskDetails pkglvl
CROSS APPLY pkglvl.InputQry.nodes('./*') AS outputvalue(x)
CROSS APPLY pkglvl.OutputQry.nodes('./*') AS outputvalue1(x)
WHERE DFTTaskType = 'Lookup'
OR DFTTaskType1 LIKE '%Lookup%';
DELETE
FROM TblLookupTaskDetails
WHERE DestinationColumn LIKE '%Lookup Error Output%';
PRINT '---------- Insert DFT lookup task details-----------------------';
PRINT '---------- Insert DFT details in final table-----------------------';
INSERT INTO PackageAnalysis (
RowID
,PackagePath
,PackageName
,Category
,PackageCreatorName
,TaskPath
,ObjectName
,ObjectType
,ObjectTypeDescription
,DelayValidationPropertyValue
,ObjectValue
,ExpressionValue
,ConnectionManager
,SourceColumn
,DestinationColumn
,DerivedValue
,Variable
,LookupJoins
,ParameterBindingParameterName
,SortKeyPosition
,IsSortedProperty
,MultihashcolumnSortPosition
)
SELECT DISTINCT dftlvl.RowID
,Pkg.PackagePath
,REPLACE(pkg.PackagePath, REPLACE(@Path, '*.dtsx', ''), '') PackageName
,cast('Data Flow Task' AS VARCHAR(max)) AS Category
,pkg.PackageCreatorName
,DFTLVL.DFTTasksPath AS TaskPath
,DFTTaskName AS TaskName
,CASE
WHEN cast(CHARINDEX(';', LEFT(DFTTaskType1, 8000)) AS VARCHAR(1000)) BETWEEN 4
AND 100
THEN LEFT(cast(DFTTaskType1 AS VARCHAR(8000)), CHARINDEX(';', LEFT(DFTTaskType1, 8000)) - 1)
ELSE DFTTaskType1
END AS TaskType
,cast('' AS VARCHAR(max)) AS ObjectTypeDescription
,'NA' AS DelayValidationPropertyValue
,CASE
WHEN ISNULL(DFTlvl.DFTSQLCommand, '') <> ''
THEN ISNULL(DFTlvl.DFTSQLCommand, '')
ELSE ISNULL(DFTlvl.DFTRowSet, '')
END AS ConnectionString
,cast('' AS VARCHAR(max)) AS ExpressionValue
,DFTConnectionManager AS ConnectionManager
,ISNULL(Der.SourceColumn, SrcDest.SourceColumn)
,ISNULL(Der.DestinationColumn, SrcDest.DestinationColumn)
,Der.DerivedValue
,Variable
,Lkup.LookupJoins
,DFTlvl.ParameterBindingParameterName
,SrcDest.SortKeyPosition
,DFTlvl.IsSortedProperty
,DFTlvl.MultihashcolumnSortPosition
FROM TblDFTTaskDetails DFTlvl
INNER JOIN pkgStats PKG ON DFTlvl.RowID = Pkg.RowID
LEFT JOIN TblSrcDestDetails SrcDest ON DFTlvl.RowID = SrcDest.RowID
AND DFTlvl.DataFlowDetailsRowID = SrcDest.DataFlowDetailsRowID
LEFT JOIN TblDerivedTaskDetails Der ON DFTlvl.RowID = Der.RowID
AND DFTlvl.DataFlowDetailsRowID = Der.DataFlowDetailsRowID
LEFT JOIN TblLookupTaskDetails Lkup ON DFTlvl.RowID = Lkup.RowID
AND DFTlvl.DataFlowDetailsRowID = Lkup.DataFlowDetailsRowID;
PRINT '---------- Insert DFT details in final table-----------------------';
PRINT '-------------Insert control flow details in final table----------';
INSERT INTO PackageAnalysis (
RowID
,PackagePath
,PackageName
,Category
,PackageCreatorName
,TaskPath
,ObjectName
,ObjectType
,ObjectTypeDescription
,DelayValidationPropertyValue
,ObjectValue
,ResultSetParameterName
,ParameterBindingSequence
,ParameterBindingParameterName
,ExecutePackageExpression
,ExecutedPackageName
,ExecutePackageConnection
,Script
,ConnectionManagerID
,IsDisabled
,ExpressionValue
)
SELECT cflvl.RowID
,Pkg.PackagePath
,REPLACE(Pkg.PackagePath, REPLACE(@Path, '*.dtsx', ''), '') PackageName
,cast('Control Flow Task' AS VARCHAR(max)) AS Category
,Pkg.PackageCreatorName
,cflvl.TaskPath
,CAST(cflvl.TaskName AS VARCHAR(max)) AS TaskName
,CAST(TaskType AS VARCHAR(max)) AS TaskType
,cast(cflvl.TaskTypeDescription AS VARCHAR(max)) AS ObjectTypeDescription
,ISNULL(NULLIF(DelayValidationPropertyValue, ''), 'False') DelayValidationPropertyValue
,(
SELECT TOP 1 cfnodes.x.value('declare namespace SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask";@SQLTask:SqlStatementSource[1]', 'nvarchar(max)') AS ConnectionString
FROM cflvl.SqlTaskQry.nodes('./*') AS cfnodes(x)
) AS ConnectionString
,SQLTask.ResultSetParameterName
,SQLTask.ParameterBindingSequence
,SQLTask.ParameterBindingParameterName
,ExecPkgTask.ExecutePackageExpression
,ExecPkgTask.ExecutedPackageName
,ExecPkgTask.ExecutePackageConnection
,ScriptTsk.Script
,(
SELECT TOP 1 cfnodes.x.value('declare namespace SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask";@SQLTask:Connection[1]', 'nvarchar(max)') AS ConnectionString
FROM cflvl.SqlTaskQry.nodes('./*') AS cfnodes(x)
) AS ConnectionString
,ISNULL(cflvl.IsDisabled, 'False')
,cflvl.ExpressionValue
FROM TblControlFlowDetails cflvl
INNER JOIN pkgStats PKG ON cflvl.RowID = Pkg.RowID
LEFT JOIN TblSQLTaskDetails SQLTask ON cflvl.RowID = SQLTask.RowID
AND cflvl.ControlFlowDetailsRowID = SQLTask.ControlFlowDetailsRowID
LEFT JOIN TblExecutePackageTaskDetails ExecPkgTask ON cflvl.RowID = ExecPkgTask.RowID
AND cflvl.ControlFlowDetailsRowID = ExecPkgTask.ControlFlowDetailsRowID
LEFT JOIN TblScriptTaskdetails ScriptTsk ON cflvl.RowID = ScriptTsk.RowID
AND cflvl.ControlFlowDetailsRowID = ScriptTsk.ControlFlowDetailsRowID;
PRINT '-------------Insert control flow details in final table----------';
PRINT '-------------Insert Connection details in table----------';
INSERT INTO ConnectionDetails (
ConnectionManager
,ConnectionManagerID
)
SELECT DISTINCT ObjectName
,ConnectionManagerID
FROM PackageAnalysis
WHERE Category = 'Connection';
PRINT '-------------Insert Connection details in table----------';
PRINT '-------------Update sequence of task----------';
UPDATE PA
SET SequenceNo = TblPrecCon.sequenceno
FROM PackageAnalysis PA
INNER JOIN #tempprecedence6 TblPrecCon ON PA.RowID = TblPrecCon.RowID
AND PA.TaskPath = TblPrecCon.Task;
--LEFT JOIN @TblPrecedenceConstraint1 TblPrec
--ON Final.RowID = TblPrec.RowID
--AND Final.PackagePath = TblPrec.PackagePath
--AND Final.TaskPath = TblPrec.TaskName
--ORDER BY TblPrec.RowID,TblPrec.PrecOrder
PRINT '--------------------update objecttype-------------------';
UPDATE PackageAnalysis
SET ObjectType = 'Sequence Container'
WHERE ObjectType LIKE '%STOCK:SEQUENCE%'
OR ObjectType LIKE '%(Sequence Container)%'
OR ObjectType LIKE '%(Container)%';
UPDATE PackageAnalysis
SET ObjectType = 'Data Flow Task'
WHERE ObjectType LIKE 'SSIS.Pipeline.3'
OR ObjectType LIKE '%(Data Flow Task)%';
UPDATE PackageAnalysis
SET ObjectType = 'For Loop Container'
WHERE ObjectType LIKE '%(For Loop Container)%'
OR ObjectType LIKE 'STOCK:FORLOOP';
UPDATE PackageAnalysis
SET ObjectType = 'Script Task'
WHERE ObjectType LIKE '%(Script Task)%';
UPDATE PackageAnalysis
SET ObjectType = 'Execute SQL Task'
WHERE ObjectType LIKE '%(Execute SQL Task)%'
OR ObjectType LIKE '%ExecuteSQLTask%';
UPDATE PackageAnalysis
SET ObjectType = 'Execute Package Task'
WHERE ObjectType LIKE '%(Execute Package Task)%'
OR ObjectType LIKE '%ExecutePackageTask%';
UPDATE PackageAnalysis
SET ObjectType = 'Foreach Loop Container'
WHERE ObjectType LIKE '%(Foreach Loop Container)%'
OR ObjectType LIKE 'STOCK:FOREACHLOOP';
UPDATE PackageAnalysis
SET ObjectType = 'File System Task'
WHERE ObjectType LIKE '%FileSystemTask%';
UPDATE PackageAnalysis
SET ObjectType = 'Script Task'
WHERE ObjectType LIKE '%ScriptTask%';
UPDATE PackageAnalysis
SET ObjectType = 'Send Mail Task'
WHERE ObjectType LIKE '%SendMailTask%';
UPDATE PackageAnalysis
SET ObjectType = 'ADO.NET:System.Data.SqlClient.SqlConnection'
WHERE objecttype = 'ADO.NET:System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089';
UPDATE PackageAnalysis
SET ObjectType = 'ADO.NET:System.Data.Odbc.OdbcConnection'
WHERE objecttype = 'ADO.NET:System.Data.Odbc.OdbcConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089';
UPDATE PackageAnalysis
SET ObjectType = 'Script Component'
WHERE ObjectType LIKE 'Hosts user-defined data transformations, source and destination adapters'
OR ObjectType = 'Executes a custom script.';
UPDATE PackageAnalysis
SET ObjectType = 'INT16'
WHERE objecttype = '2' --OR objecttype = '0'
;
UPDATE PackageAnalysis
SET ObjectType = 'INT32'
WHERE objecttype = '3';
UPDATE PackageAnalysis
SET ObjectType = 'BYTE'
WHERE objecttype = '17';
UPDATE PackageAnalysis
SET ObjectType = 'BOOLEAN'
WHERE objecttype = '11';
UPDATE PackageAnalysis
SET ObjectType = 'OBJECT'
WHERE objecttype = '13';
UPDATE PackageAnalysis
SET ObjectType = 'STRING'
WHERE objecttype = '8';
UPDATE PackageAnalysis
SET ObjectType = 'DOUBLE'
WHERE objecttype = '5';
UPDATE PackageAnalysis
SET ObjectType = 'DATETIME'
WHERE objecttype = '7';
UPDATE PackageAnalysis
SET ObjectType = 'INT64'
WHERE objecttype = '20';
UPDATE PackageAnalysis
SET ObjectType = 'ADO NET Source'
WHERE objecttype = 'Extracts data from a relational database by using a .NET provider.';
UPDATE PackageAnalysis
SET ObjectValue = Variable
WHERE objecttype = 'Row count';
UPDATE PackageAnalysis
SET PackageAnalysis.ConnectionManager = ConnectionDetails.ConnectionManager
FROM PackageAnalysis
INNER JOIN ConnectionDetails ON PackageAnalysis.ConnectionManagerID = ConnectionDetails.ConnectionManagerID;
UPDATE PackageAnalysis
SET PackageAnalysis.IsDisabled = ISNULL(IsDisabled, 'False');
DELETE
FROM PackageAnalysis
WHERE SourceColumn LIKE '%OLE DB Source Error Output%'
AND (ObjectType LIKE '%Source%' OR ObjectType LIKE '%Destination%');
DELETE
FROM PackageAnalysis
WHERE SourceColumn LIKE '%Derived Column Error Output%';
DECLARE @tblparameters TABLE (
rowid INT
,ParameterBindingParameterName VARCHAR(1000)
,taskpath VARCHAR(2000)
,SplitParameterBindingParameterName VARCHAR(1000)
,NewParameterBindingParameterName VARCHAR(1000)
);
INSERT INTO @tblparameters (
rowid
,ParameterBindingParameterName
,taskpath
,SplitParameterBindingParameterName
)
SELECT DISTINCT rowid
,ParameterBindingParameterName
,taskpath
,O.ParameterBindingParameterNameSplit
FROM (
SELECT rowid
,ParameterBindingParameterName
,taskpath
,cast('<X>' + replace(ParameterBindingParameterName, ';', '</X><X>') + '</X>' AS XML) AS ParameterBindingParameterNameSep
FROM PackageAnalysis
WHERE Category = 'Data Flow Task'
AND ParameterBindingParameterName IS NOT NULL
) F1
CROSS APPLY (
SELECT fdata.D.value('.', 'varchar(1000)') AS ParameterBindingParameterNameSplit
FROM f1.ParameterBindingParameterNameSep.nodes('X') AS fdata(D)
) O
WHERE O.ParameterBindingParameterNameSplit <> '';
UPDATE P
SET P.NewParameterBindingParameterName = REPLACE(SplitParameterBindingParameterName, V.VariableHexValue, 'VariableName = ' + V.ObjectName) + ',Value = ' + v.ObjectValue
FROM @tblparameters P
INNER JOIN TblVariableDetails v ON P.Rowid = v.Rowid
AND CHARINDEX(V.VariableHexValue, SplitParameterBindingParameterName) > 0;
UPDATE p
SET ParameterBindingParameterName = t.NewParameterBindingParameterName
FROM packageanalysis p
INNER JOIN (
SELECT rowid
,taskpath
,ParameterBindingParameterName
,stuff((
SELECT ';' + NewParameterBindingParameterName
FROM @tblparameters
WHERE rowid = t.rowid
AND taskpath = t.taskpath
FOR XML PATH('')
,TYPE
,ROOT
).value('root[1]', 'nvarchar(max)'), 1, 1, '') AS NewParameterBindingParameterName
FROM @tblparameters t
) t ON p.rowid = t.rowid
AND p.taskpath = t.taskpath
AND p.ParameterBindingParameterName = t.ParameterBindingParameterName
CREATE NONCLUSTERED INDEX NCIX ON PackageAnalysis (
Rowid
,SequenceNo
);
UPDATE pkg
SET PackageSequenceNo = NewTableRowID
FROM PackageAnalysis pkg
JOIN
(SELECT ROW_number() OVER(PARTITION BY RowID ORDER BY ISNULL(SequenceNo,-1)) as NewTableRowID,
SequenceNo ,
RowID,
TableRowID
FROM PackageAnalysis ) newpkg
ON pkg.RowID = newpkg.RowID
and pkg.TableRowID = newpkg.TableRowID
UPDATE pkg
SET PackageProtectionLevel = CASE WHEN Stats.PackageProtectionLevel IS Null THEN 'EncryptSensitiveWithUserKey'
WHEN Stats.PackageProtectionLevel = '0' THEN 'DontSaveSensitive'
WHEN Stats.PackageProtectionLevel = '2' THEN 'EncryptSensitiveWithPassword'
ELSE Stats.PackageProtectionLevel END
FROM PackageAnalysis pkg
JOIN pkgStats Stats
ON pkg.RowID = Stats.RowID
SELECT
RowID ,
PackageSequenceNo ,
SequenceNo ,
'"'+ PackagePath +'"' PackagePath,
'"'+ PackageName +'"' PackageName,
'"'+ ISNULL(IsDisabled,'') +'"' IsDisabled,
'"'+ ISNULL(Category ,'') +'"' Category,
'"'+ ISNULL(PackageCreatorName ,'') +'"' PackageCreatorName,
'"'+ ISNULL(PackageProtectionLevel ,'') +'"' PackageProtectionLevel,
'"'+ ISNULL(TaskPath ,'') +'"' TaskPath,
'"'+ ISNULL(ObjectName ,'') +'"' ObjectName,
'"'+ ISNULL(ObjectType ,'') +'"' ObjectType,
'"'+ ISNULL(ObjectTypeDescription ,'') +'"' ObjectTypeDescription,
'"'+ ISNULL(DelayValidationPropertyValue ,'') +'"' DelayValidationPropertyValue,
'"'+ ISNULL(ObjectValue ,'') +'"' ObjectValue,
'"'+ ISNULL(RetainSameConnectionProperty ,'') +'"' RetainSameConnectionProperty,
'"'+ ISNULL(IsSortedProperty ,'') +'"' IsSortedProperty,
'"'+ ISNULL(ExpressionValue ,'') +'"' ExpressionValue,
'"'+ ISNULL(ConnectionManager ,'') +'"' ConnectionManager,
'"'+ ISNULL(SourceColumn ,'') +'"' SourceColumn,
'"'+ ISNULL(DestinationColumn ,'') +'"' DestinationColumn,
'"'+ ISNULL(SortKeyPosition ,'') +'"' SortKeyPosition,
'"'+ ISNULL(MultihashcolumnSortPosition ,'') +'"' MultihashcolumnSortPosition,
'"'+ ISNULL(DerivedValue ,'') +'"' DerivedValue,
'"'+ ISNULL(ResultSetParameterName ,'') +'"' ResultSetParameterName,
'"'+ ISNULL(ParameterBindingSequence ,'') +'"' ParameterBindingSequence,
'"'+ ISNULL(ParameterBindingParameterName ,'') +'"' ParameterBindingParameterName,
'"'+ ISNULL(ExecutePackageExpression ,'') +'"' ExecutePackageExpression,
'"'+ ISNULL(ExecutedPackageName ,'') +'"' ExecutedPackageName,
'"'+ ISNULL(ExecutePackageConnection ,'') +'"' ExecutePackageConnection,
'"'+ ISNULL(Script ,'') +'"' Script,
'"'+ ISNULL(Variable ,'') +'"' Variable,
'"'+ ISNULL(LookupJoins ,'') +'"' LookupJoins
FROM PackageAnalysis
ORDER BY 3
,1;
END;
GO