Reverse engineering SSIS package using C#

SQL server provide assemblies to manipulate packages programmatically.

To do a reverse engineering (deserialize a dtsx package), You have to do this by looping over packages and read them programmatically, just follow this detailed link

• Reading DTS and SSIS packages programmatically

There is another way (harder way and not recommended) to achieve this , by reading dtsx as text file and parse the xml content. check my answer at the following question to get an example:

• Automate Version number Retrieval from .Dtsx files

Hint:

just open the package in visual studio. go to the package explorer Tab (near control flow and data flow tabs) you will find a treeview. it will leads you the way you have to search for the component you need

Update 1 - C# Script @ 2019-07-08

If you are looking for a script that list all package objects you can use a similar script:

using System;using DtsRuntime = Microsoft.SqlServer.Dts.Runtime;using DtsWrapper = Microsoft.SqlServer.Dts.Pipeline.Wrapper;public void Main(){    string pkgLocation;    DtsRuntime.Package pkg;    DtsRuntime.Application app;    DtsRuntime. DTSExecResult pkgResults;    pkgLocation =      @"D:\Test\Package 1.dtsx";    app = new DtsRuntime.Application();    pkg = app.LoadPackage(pkgLocation, null);    //List Executables (Tasks)    foreach(DtsRuntime.Executable tsk in pkg.Executables)    {        DtsRuntime.TaskHost TH = (DtsRuntime.TaskHost)tsk;        MessageBox.Show(TH.Name + "\t" + TH.HostType.ToString());        //Data Flow Task components        if (TH.InnerObject.ToString() == "System.__ComObject")        {            try            {                DtsWrapper.MainPipe m = (DtsWrapper.MainPipe)TH.InnerObject;                DtsWrapper.IDTSComponentMetaDataCollection100 mdc = m.ComponentMetaDataCollection;                foreach (DtsWrapper.IDTSComponentMetaData100 md in mdc)                {                    MessageBox.Show(TH.Name.ToString() + " - " + md.Name.ToString());                }            }            catch {            // If it is not a data flow task then continue foreach loop            }        }    }    //Event Handlers    foreach(DtsRuntime.DtsEventHandler eh in pkg.EventHandlers)    {        MessageBox.Show(eh.Name + " - " + CM.HostType);    }    //Connection Manager    foreach(DtsRuntime.ConnectionManager CM in pkg.Connections)    {        MessageBox.Show(CM.Name + " - " + CM.HostType);    }    //Parameters    foreach (DtsRuntime.Parameter Param in pkg.Parameters)    {        MessageBox.Show(Param.Name + " - " + Param.DataType.ToString());    }    //Variables    foreach (DtsRuntime.Variable Var in pkg.Variables)    {        MessageBox.Show(Var.Name + " - " + Var.DataType.ToString());    }    //Precedence Constraints    foreach (DtsRuntime.PrecedenceConstraint PC in pkg.PrecedenceConstraints)    {        MessageBox.Show(PC.Name);    }}

References

• Loading and Running a Local Package Programmatically

Update 2 - SSISPackageExplorer Project @ 2019-07-10

I started a small project called SSISPackageExplorer on Git-Hub which allow the user to read the package objects in a TreeView, It is very basic right now but i will try to improve it in a while:

• GitHub - SSISPackageExplorer

Some of the properties in dtsx Microsoft.SqlServer.Dts.Pipeline are not CLS-compliant.

• ColumnInformation Constructors

ColumnInformation Class DefinitionNamespace:Microsoft.SqlServer.Dts.Pipeline Assembly:Microsoft.SqlServer.PipelineHost.dllImportantThis API is not CLS-compliant.C++Copypublic ref class ColumnInformation

otherwise try this.

Just open your dtsx package in notepad++. Find table name then do the same search on the property name in all packages( find ion all files). I think that even if you search for the column in dtsx opened in a text editor it will give you everything. It's manual but can be updated with Regex and c#. I never did it with regex. I just did notepad++ and one package once.