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.
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
Sharp
ReplyDeleteAdvan
Metro
Lampung
Panasonic
pulsa
lampung
Lampung
Lampung
I think this is actually the best post on SSIS and its related operations and is actually a very useful post for those who want to study about practical aspects of SSIS.
ReplyDeleteSSIS PostgreSql Write
Took me time to read all the comments, but I really enjoyed the article. It proved to be Very helpful to me and I am sure to all the commenters here! It’s always nice when you can not only be informed, but also entertained! 신용카드 현금화
ReplyDeletethanks for the post, could you please let me know how is the ADO.Net connection configured ?
ReplyDeleteI like viewing web sites which comprehend the price of delivering the excellent useful resource free of charge. I truly adored reading your posting. Thank you! Troll
ReplyDeleteGreat article. Not sure why but the XML parsing does not work for SQL Server 2012 forward or at least not for me. Maybe the .dtsx xml is different? I tried everything to get this to work with the code above but to no avail. Had to re-write the xml parsing routine. Not a big issue as I was customizing to meet our environment. Thank you for the contribution!
ReplyDeleteFurther to my comment, unless anyone else is able to point me in the ways of my error, the issue may be when there are script tasks in the dtsx as they are also classified with the tag of "Executable". But I believe the main cause is "Executable" node is also the root node.
DeleteWould you please consisder posting the re-written XML parsing?
DeleteThis is such a great resource that you are providing and you give it away for free. I love seeing blog that understand the value of providing a quality resource for free. حمله سایبری
ReplyDeleteAivivu - đại lý chuyên vé máy bay trong nước và quốc tế
ReplyDeletesăn vé máy bay giá rẻ đi Mỹ
mua vé máy bay từ mỹ về việt nam hãng eva
bay từ nhật về việt nam
vé máy bay khứ hồi từ đức về việt nam
đăng ký bay từ canada về Việt Nam
vé máy bay từ hàn quốc về việt nam bao nhiêu tiền
khách sạn cách ly ở sài gòn
Super-Duper site! I am Loving it!! Will come back again, Im taking your feed also, Thanks. Direct Response Copywriter
ReplyDeleteGreat article with excellent idea!Thank you for such a valuable article. I really appreciate for this great information.. Direct Response Copywriting
ReplyDeleteCAN WE USE CREATE CONIGURATOION TYPE OF SQL SERVER TABLE FOR THIS AND STORE VARIABLES, CONEECTION MANAGERS USED IN PACKAGE.
ReplyDeleteThank you for another great article. Where else could anyone get that kind of information in such a perfect way of writing? I have a presentation next week, and I am on the look for such information. Direct Response Copywriter
ReplyDeleteMeghalaya Board 6th, 7th, 8th, 9th, 10th Class Exam date Sheet 2023 available at Official Website, Meghalaya Board Conducts All 6th, 7th, 8th, 9th, 10th Public Exams in Meghalaya State, Meghalaya 10th Class Syllabus This Kerala High School Level Exam Attended Every Year More than 30 Laks of Students, Students Studying Meghalaya Board 6th, 7th, 8th, 9th, 10th Class Revised Syllabus 2023 helps Students to Learn Logic and order and hence,
ReplyDeleteLovely post..! I got well knowledge of this blog. Thank you!
ReplyDeleteFamily Law Retainer Fee
Experienced Family Lawyers
Im really impressed your work
ReplyDeleteAbogados Divorcio Ashburn VA
If You are Find Atencion a Clientes Volaris So you are in right place
ReplyDeleteTrusted Abogado De Trafico en Virginia will provide a legal advice, represent in court.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteI can't make this work. Step 6 parameter is missing, I tried both variables. In all cases only PackageFormatVersion and 8 are populated in the dbo.Package table. What am I missing?
ReplyDeleteI'm on SQL 2019, though I had to downlevel to 2012 to get C# script to compile.