SSIS - Read SSIS Package File(.dtsx) for Information [Connection Managers,Transformations,Variables,Configuration,Expressions,Log Providers]

Scenario: 

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.


Solution:

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:)

Step 1: 
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 Database Sandbox
go

Step 2: 
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] 

Step 3:
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 4: 
You have to create two connections to your Database , Create One OLEDB and second ADO.Net. We will use Conn_SQL(OLE DB) in our Execute SQL Task/s and we will use MyADOConn (ADO.NET)  in script task. In Script task we will read Package File ( XML) and load into dbo.Test by using ADO Connection.

Step 5: 
Configure For-each Loop Container as shown below. The purpose of Foreach loop container is to read one package file at a time so we can get metadata information from that file and load into our tables.




Step 6: 
After configuring Foreach Loop, Bring Execute SQL Task inside foreach loop and configure as shown below. We are Truncating dbo.Test Table so we can load XML data( Package File) on each of loop iteration. If you have not create test table yet, use this script

Create table dbo.Test(Col NVARCHAR(MAX))


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