Sometime we create many SSIS Packages and then we do not have any central Database where we can go and find out what Connection Managers are used in my Package X, What and how many variables are used in my Package, Were any expressions written on those variables, Was configuration used for the Package. To find out the answer of these questions and so many other questions; Developers always have to open the package and then find out the information. At one point on one of my assignment we had more than 200 SSIS Packages.I wanted to build a Database where I can save all the information (Metadata) about Package components and with single query bring the information instead of to find answer of any question/s I have to open the package and dig into it.
To find the answer of questions, lets say how many connection managers are used in Package X Or were any expressions written on variables in Package X. We need to save different information from Package file(.dtsx).
Here is step by step approach to create complete solution.Sorry at few points I did not keep best practices in mind but headed towards just to complete the code. But I am sure it will work for you to provide Metadata information about your SSIS packages and make investigation quick:)
Create a database name Sandbox. You are more than welcome to create with any name you like but you have to make small changes in Scripts accordingly.
Create all given tables , I created Table called dbo.Packages with Primarykey PkgID that I used in all other tables as Foreign key to make the relationship So we can find any information related to package.
--Drop and Create All Tables
USE Sandbox
GO
IF EXISTS (SELECT 1
FROM sys.objects
WHERE name = 'FK_Packages_PkgVariable')
ALTER TABLE [dbo].[PkgVariable]
DROP CONSTRAINT FK_Packages_PkgVariable
IF EXISTS (SELECT 1
FROM sys.objects
WHERE name = 'FK_Packages_PkgConfiguration')
ALTER TABLE [dbo].[PkgConfiguration]
DROP CONSTRAINT FK_Packages_PkgConfiguration
IF EXISTS (SELECT 1
FROM sys.objects
WHERE name = 'FK_Packages_PkgConnectionManager')
ALTER TABLE [dbo].[PkgConnectionManager]
DROP CONSTRAINT FK_Packages_PkgConnectionManager
IF EXISTS (SELECT 1
FROM sys.objects
WHERE name = 'FK_Packages_PkgLogProvider')
ALTER TABLE [dbo].[PkgLogProvider]
DROP CONSTRAINT FK_Packages_PkgLogProvider
IF EXISTS (SELECT 1
FROM sys.objects
WHERE name = 'FK_Packages_PkgEventHandler')
ALTER TABLE [dbo].[PkgEventHandler]
DROP CONSTRAINT FK_Packages_PkgEventHandler
IF EXISTS (SELECT 1
FROM sys.objects
WHERE name = 'FK_Packages_PkgTransformation')
ALTER TABLE [dbo].[PkgTransformation]
DROP CONSTRAINT FK_Packages_PkgTransformation
IF EXISTS (SELECT 1
FROM sys.objects
WHERE name = 'Pk_Package')
ALTER TABLE dbo.Package
DROP CONSTRAINT Pk_Package
IF EXISTS(SELECT 1
FROM sys.objects
WHERE name = 'Package')
DROP TABLE dbo.Package
IF EXISTS(SELECT 1
FROM sys.objects
WHERE name = 'PkgConnectionManager')
DROP TABLE dbo.PkgConnectionManager
IF EXISTS(SELECT 1
FROM sys.objects
WHERE name = 'PkgVariable')
DROP TABLE dbo.PkgVariable
IF EXISTS(SELECT 1
FROM sys.objects
WHERE name = 'PkgConfiguration')
DROP TABLE dbo.PkgConfiguration
IF EXISTS(SELECT 1
FROM sys.objects
WHERE name = 'PkgLogProvider')
DROP TABLE dbo.PkgLogProvider
IF EXISTS(SELECT 1
FROM sys.objects
WHERE name = 'PkgEventHandler')
DROP TABLE dbo.PkgEventHandler
GO
IF EXISTS(SELECT 1
FROM sys.objects
WHERE name = 'PkgTransformation')
DROP TABLE dbo.PkgTransformation
GO
CREATE TABLE dbo.Package
(
PkgID INT IDENTITY(1, 1),
PackageName VARCHAR(1000),
PackagePath VARCHAR(1000),
PackageFormatVersion VARCHAR(1000),
CreatorName VARCHAR(1000),
CreationDate VARCHAR(1000),
VersionMajor INT,
VersionMinore INT,
CreatorComputerName VARCHAR(1000),
ProtectionLevel VARCHAR(1000),
EnableConfig INT,
MaxConcurrentExecutables INT,
LoadDateTime DATETIME DEFAULT Getdate(),
CONSTRAINT [Pk_Package] PRIMARY KEY CLUSTERED (PkgID)
)
GO
CREATE TABLE dbo.PkgConnectionManager
(
ID INT IDENTITY(1, 1),
ConnectionManagerName VARCHAR(1000),
DelayValidation INT,
DTSID UNIQUEIDENTIFIER,
DESCRIPTION VARCHAR(1000),
ConnectionType VARCHAR(1000),
PkgID INT
)
GO
CREATE TABLE dbo.PkgVariable
(
ID INT IDENTITY(1, 1),
VariableName VARCHAR(1000),
Expression VARCHAR(MAX),
EvaluateAsExpression INT,
Namespace VARCHAR(1000),
ReadOnly INT,
RaiseChangedEvent INT,
IncludeInDebugDump INT,
DTSID UNIQUEIDENTIFIER,
DESCRIPTION VARCHAR(1000),
CreationName VARCHAR(1000),
PkgID INT
)
GO
CREATE TABLE Dbo.PkgConfiguration
(
ID INT IDENTITY(1, 1),
ConfigurationName VARCHAR(1000),
ConfigurationType VARCHAR(1000),
ConfigurationString VARCHAR(1000),
ConfigurationVariable VARCHAR(1000),
DTSID UNIQUEIDENTIFIER,
DESCRIPTION VARCHAR(1000),
CreationName VARCHAR(1000),
PkgID INT
)
GO
CREATE TABLE dbo.PkgLogProvider
(
ID INT IDENTITY(1, 1),
LogProviderName VARCHAR(1000),
ConfigString VARCHAR(1000),
DelayValidation VARCHAR(1000),
DTSID UNIQUEIDENTIFIER,
DESCRIPTION VARCHAR(1000),
CreationName VARCHAR(1000),
PkgID INT
)
GO
CREATE TABLE dbo.PkgEventHandler
(
ID INT IDENTITY(1, 1),
EventName VARCHAR(1000),
ForceExecValue VARCHAR(1000),
ExecValue VARCHAR(1000),
ForceExecutionResult VARCHAR(1000),
Disabled VARCHAR(1000),
FailPackageOnFailure VARCHAR(1000),
FailParentOnFailure VARCHAR(1000),
MaxErrorCount INT,
ISOLevel VARCHAR(1000),
LocaleID INT,
TransactionOption INT,
DelayValidation INT,
DTSID UNIQUEIDENTIFIER,
DESCRIPTION VARCHAR(1000),
CreationName VARCHAR(1000),
PkgID INT
)
CREATE TABLE dbo.PkgTransformation
(
ID INT IDENTITY(1, 1),
TaskName VARCHAR(1000),
TaskType VARCHAR(1000),
PkgID INT
)
ALTER TABLE dbo.PkgVariable
WITH NOCHECK ADD CONSTRAINT [FK_Packages_PkgVariable] FOREIGN KEY(PkgID) REFERENCES [dbo].[Package] (PkgID)
ALTER TABLE dbo.PkgConfiguration
WITH NOCHECK ADD CONSTRAINT [FK_Packages_PkgConfiguration] FOREIGN KEY(PkgID) REFERENCES [dbo].[Package] (PkgID)
ALTER TABLE dbo.PkgConnectionManager
WITH NOCHECK ADD CONSTRAINT [FK_Packages_PkgConnectionManager] FOREIGN KEY(PkgID) REFERENCES [dbo].[Package] (PkgID)
ALTER TABLE dbo.PkgLogProvider
WITH NOCHECK ADD CONSTRAINT [FK_Packages_PkgLogProvider] FOREIGN KEY(PkgID) REFERENCES [dbo].[Package] (PkgID)
ALTER TABLE dbo.PkgEventHandler
WITH NOCHECK ADD CONSTRAINT [FK_Packages_PkgEventHandler] FOREIGN KEY(PkgID) REFERENCES [dbo].[Package] (PkgID)
ALTER TABLE dbo.PkgTransformation
WITH NOCHECK ADD CONSTRAINT [FK_Packages_PkgTransformation] FOREIGN KEY(PkgID) REFERENCES [dbo].[Package] (PkgID)
ALTER TABLE dbo.PkgVariable
CHECK CONSTRAINT [FK_Packages_PkgVariable]
ALTER TABLE dbo.PkgConfiguration
CHECK CONSTRAINT [FK_Packages_PkgConfiguration]
ALTER TABLE dbo.PkgConnectionManager
CHECK CONSTRAINT [FK_Packages_PkgConnectionManager]
ALTER TABLE dbo.PkgLogProvider
CHECK CONSTRAINT[FK_Packages_PkgLogProvider]
ALTER TABLE dbo.PkgEventHandler
CHECK CONSTRAINT[FK_Packages_PkgEventHandler]
ALTER TABLE dbo.PkgTransformation
CHECK CONSTRAINT[FK_Packages_PkgTransformation]
Now lets create SSIS Package to load information in these tables by reading our ssis packages(.dtsx) files.
Create a variable for the folder path where your all SSIS Packages are sitting. The Package is going to read each of package file (XML) and load into dbo.Test table and then parse the xml information and load required information into related tables those we have already created above.
Step 7:
Bring Script Task inside your Foreach loop and configure as shown below.
Step 8:
As you have clicked Edit Script. Provide below script to Script task. Script task is going to read the file and load into dbo.Test table.
/*
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Xml;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
namespace ST_ec5075ad59b94e3c8c58d4e3fa56755d.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
/*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.
To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
To open Help, press F1.
*/
public void Main()
{
string filepath;
filepath=Dts.Variables["User::VarFileName"].Value.ToString();
SqlConnection myADONETConnection = new SqlConnection();
myADONETConnection = (SqlConnection)(Dts.Connections["MyADOConn"].AcquireConnection(Dts.Transaction) as SqlConnection);
StreamReader reader = File.OpenText(filepath);
string input = null;
string abc = null;
string Col;
myADONETConnection.Close();
while ((input = reader.ReadLine()) != null)
{
abc =abc+ input;
}
Col = abc.ToString();
myADONETConnection.Open();
string queryString = "insert into sandbox.dbo.Test(Col) Values(@field1)";
SqlCommand command = new SqlCommand(queryString, myADONETConnection);
command.Parameters.AddWithValue("@field1",Col.ToString());
command.ExecuteReader();
myADONETConnection.Close();
}
}
}
Step 9:
Bring Execute SQL Task inside Foreach Loop and Configure as shown below. The Code is provided right after snapshot that you need to paste in SQLStatement
SQLStatment Code :
--======================================
--INSERT RECORD IN ALL THE TABLES
--======================================
--Insert into dbo.package
DECLARE @pkgID INT
INSERT INTO dbo.package
(PackageName,
PackagePath,
PackageFormatVersion,
CreatorName,
CreationDate,
VersionMajor,
VersionMinore,
CreatorComputerName,
ProtectionLevel,
EnableConfig)
SELECT ObjectName AS PackageName,
?,
PackageFormatVersion,
CreatorName,
CreationDate,
VersionMajor,
VersionMinore,
CreatorComputerName,
ProtectionLevel,
EnableConfig
FROM (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 (SELECT Cast(COL AS XML) AS pkgXML
FROM dbo.Test) t
CROSS APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
/DTS:Executable/DTS:Property') Props(Prop)) D
PIVOT (Min(propValue)
FOR PropName IN (ObjectName,
PackageFormatVersion,
CreatorName,
CreationDate,
VersionMajor,
VersionMinore,
CreatorComputerName,
ProtectionLevel,
EnableConfig) ) AS PV
SET @PkgID=Scope_identity()
--print @pkgID
-----------------------------------------------------------------------------------------------------
--Connection Managers
IF Object_id('tempdb..#T') IS NOT NULL
BEGIN
DROP TABLE #T
END
IF Object_id('tempdb..#TG') IS NOT NULL
BEGIN
DROP TABLE #TG
END
SELECT 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
INTO #T
FROM (SELECT Cast(COL AS XML) AS pkgXML
FROM dbo.Test) t
CROSS APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
/DTS:Executable/DTS:ConnectionManager/DTS:Property') Props(Prop)
CREATE TABLE #TG
(
PropName VARCHAR(300),
propValue VARCHAR(300),
id INT
)
DECLARE @propName VARCHAR(300)
DECLARE @propValue VARCHAR(300)
DECLARE db_cursor CURSOR FOR
SELECT PropName,
PropValue
FROM #T
DECLARE @id INT=0
DECLARE @cnt INT=0
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @propName, @propValue
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #TG
VALUES (@propName,
@propValue,
@id)
FETCH NEXT FROM db_cursor INTO @propName, @propValue
SET @cnt=@cnt + 1
IF ( @cnt%5 = 0 )
BEGIN
SET @id=@id + 1
END
END
INSERT INTO dbo.PkgConnectionManager
SELECT ObjectName,
DelayValidation,
DTSID,
Description,
CreationName,
@PkgID
FROM (SELECT *
FROM #TG)d
PIVOT (Min(propValue)
FOR PropName IN (ObjectName,
DelayValidation,
DTSID,
Description,
CreationName) ) AS PV
CLOSE db_cursor
DEALLOCATE db_cursor
-----------------------------------------------------------------------------
--Insert Tasks Information
INSERT INTO dbo.PkgTransformation
SELECT TaskName,
TaskType,
@pkgID
FROM (SELECT Pkg.props.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
./p1:Property[@p1:Name=''ObjectName''][1]', 'nvarchar(max)') AS TaskName,
Pkg.props.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
./@p1:ExecutableType', 'nvarchar(max)') AS TaskType
FROM (SELECT Cast(COL AS XML) AS pkgXML
FROM dbo.Test) t
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 not(contains(@DTS:ExecutableType,''.Package.''))]') Pkg(props)) D
------------------------------------------------------------------------------------------------
--Insert into dbo.PkgVariable
IF Object_id('tempdb..#T1') IS NOT NULL
BEGIN
DROP TABLE #T1
END
IF Object_id('tempdb..#TG1') IS NOT NULL
BEGIN
DROP TABLE #TG1
END
SELECT 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
INTO #T1
FROM (SELECT Cast(COL AS XML) AS pkgXML
FROM dbo.Test) t
CROSS APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
/DTS:Executable/DTS:Variable/DTS:Property') Props(Prop)
CREATE TABLE #TG1
(
PropName NVARCHAR(MAX),
propValue NVARCHAR(MAX),
id INT
)
DECLARE db_cursor CURSOR FOR
SELECT PropName,
PropValue
FROM #T1
SET @id =0
SET @cnt=0
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @propName, @propValue
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #TG1
VALUES (@propName,
@propValue,
@id)
FETCH NEXT FROM db_cursor INTO @propName, @propValue
SET @cnt=@cnt + 1
IF ( @cnt%10 = 0 )
BEGIN
SET @id=@id + 1
END
END
INSERT INTO dbo.PkgVariable
SELECT ObjectName,
Expression,
EvaluateAsExpression,
Namespace,
ReadOnly,
RaiseChangedEvent,
IncludeInDebugDump,
DTSID,
Description,
CreationName,
@pkgID
FROM (SELECT *
FROM #TG1)d
PIVOT (Min(propValue)
FOR PropName IN (Expression,
EvaluateAsExpression,
Namespace,
ReadOnly,
RaiseChangedEvent,
IncludeInDebugDump,
ObjectName,
DTSID,
Description,
CreationName) ) AS PV
--SELECT * From ##TP
CLOSE db_cursor
DEALLOCATE db_cursor
---------------------------------------------------------------------------------------------
--Insert configuration
IF Object_id('tempdb..#TC') IS NOT NULL
BEGIN
DROP TABLE #TC
END
IF Object_id('tempdb..#TCG') IS NOT NULL
BEGIN
DROP TABLE #TCG
END
SELECT 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
INTO #TC
FROM (SELECT Cast(COL AS XML) AS pkgXML
FROM dbo.Test) t
CROSS APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
/DTS:Executable/DTS:Configuration/DTS:Property') Props(Prop)
CREATE TABLE #TCG
(
PropName NVARCHAR(MAX),
propValue NVARCHAR(MAX),
id INT
)
DECLARE db_cursor CURSOR FOR
SELECT PropName,
PropValue
FROM #TC
SET @id =0
SET @cnt=0
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @propName, @propValue
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #TCG
VALUES (@propName,
@propValue,
@id)
FETCH NEXT FROM db_cursor INTO @propName, @propValue
SET @cnt=@cnt + 1
IF ( @cnt%7 = 0 )
BEGIN
SET @id=@id + 1
END
END
INSERT INTO dbo.PkgConfiguration
SELECT ObjectName,
ConfigurationType,
ConfigurationString,
ConfigurationVariable,
DTSID,
Description,
CreationName,
@pkgID
FROM (SELECT *
FROM #TCG)d
PIVOT (Min(propValue)
FOR PropName IN (ConfigurationType,
ConfigurationString,
ConfigurationVariable,
ObjectName,
DTSID,
Description,
CreationName) ) AS PV
CLOSE db_cursor
DEALLOCATE db_cursor
--------------------------------------------------------------------------------------------------------------------------
--Insert EventHandler
IF Object_id('tempdb..#TE') IS NOT NULL
BEGIN
DROP TABLE #TE
END
IF Object_id('tempdb..#TEG') IS NOT NULL
BEGIN
DROP TABLE #TEG
END
SELECT 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
INTO #TE
FROM (SELECT Cast(COL AS XML) AS pkgXML
FROM dbo.Test) t
CROSS APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
/DTS:Executable/DTS:EventHandler/DTS:Property') Props(Prop)
CREATE TABLE #TEG
(
PropName NVARCHAR(MAX),
propValue NVARCHAR(MAX),
id INT
)
DECLARE db_cursor CURSOR FOR
SELECT PropName,
PropValue
FROM #TE
SET @id =0
SET @cnt =0
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @propName, @propValue
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #TEG
VALUES (@propName,
@propValue,
@id)
FETCH NEXT FROM db_cursor INTO @propName, @propValue
SET @cnt=@cnt + 1
IF ( @cnt%16 = 0 )
BEGIN
SET @id=@id + 1
END
END
INSERT INTO dbo.PkgEventHandler
SELECT EventName,
ForceExecValue,
ExecValue,
ForceExecutionResult,
Disabled,
FailPackageOnFailure,
FailParentOnFailure,
MaxErrorCount,
ISOLevel,
LocaleID,
TransactionOption,
DelayValidation,
DTSID,
Description,
CreationName,
@pkgID
FROM (SELECT *
FROM #TEG)d
PIVOT (Min(propValue)
FOR PropName IN (EventName,
ForceExecValue,
ExecValue,
ForceExecutionResult,
Disabled,
FailPackageOnFailure,
FailParentOnFailure,
MaxErrorCount,
ISOLevel,
LocaleID,
TransactionOption,
DelayValidation,
DTSID,
Description,
CreationName) ) AS PV
CLOSE db_cursor
DEALLOCATE db_cursor
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Insert LogProvid
IF Object_id('tempdb..#TL') IS NOT NULL
BEGIN
DROP TABLE #TL
END
IF Object_id('tempdb..#TLG') IS NOT NULL
BEGIN
DROP TABLE #TLG
END
SELECT 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
INTO #TL
FROM (SELECT Cast(COL AS XML) AS pkgXML
FROM dbo.Test) t
CROSS APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
/DTS:Executable/DTS:LogProvider/DTS:Property') Props(Prop)
CREATE TABLE #TLG
(
PropName NVARCHAR(MAX),
propValue NVARCHAR(MAX),
id INT
)
DECLARE db_cursor CURSOR FOR
SELECT PropName,
PropValue
FROM #TL
SET @id =0
SET @cnt =0
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @propName, @propValue
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #TLG
VALUES (@propName,
@propValue,
@id)
FETCH NEXT FROM db_cursor INTO @propName, @propValue
SET @cnt=@cnt + 1
IF ( @cnt%6 = 0 )
BEGIN
SET @id=@id + 1
END
END
INSERT INTO dbo.PkgLogProvider
SELECT ObjectName,
ConfigString,
DelayValidation,
DTSID,
Description,
CreationName,
@pkgID
FROM (SELECT *
FROM #TLG)d
PIVOT (Min(propValue)
FOR PropName IN (ConfigString,
DelayValidation,
ObjectName,
DTSID,
Description,
CreationName) ) AS PV
CLOSE db_cursor
DEALLOCATE db_cursor
Step 10:
You are done with package creation. The Package should look like below snapshot
Final Output:
Run your package, Package is going to read the SSIS Package Files(.dtsx) from given folder in VarFilePath and then information for each of the package is going to be loaded to related table. The Package name will be loaded into dbo.Package. PkgID is used in all other tables for relationship. I have done a quick test and it works(SSIS 2008,SSIS 2008 R2). There could be things I might missed;)
Here are select queries for tables. You can join on PkgID all these tables
SELECT * FROM Sandbox.dbo.package
SELECT * FROM Sandbox.dbo.PkgConnectionManager
SELECT * FROM Sandbox.dbo.PkgTransformation
SELECT * FROM Sandbox.dbo.pkgVariable
SELECT * From Sandbox.dbo.PkgConfiguration
SELECT * FROM Sandbox.dbo.PkgEventHandler
SELECT * FROM Sandbox.dbo.PkgLogProvider
