There is a requirement to extract source
,destination
and column
names of source
and destination
. Why am I trying to do this is because I have thousands of packages and opening each package has on an average 60 to 75
of columns and listing all required info will take huge amount of time and its not a single time requirement and this task is done manually every two months in my organization currently.
I'm looking for some ways to reverse engineer keeping all packages in a single folder and then go through each package and get the info and put it in some spreadsheet.
I thought of opening package in xml
and get the info of interested node and put in spreadsheet which is little cumbersome. Please suggest what are the available libraries to start with it.
Some of the properties in dtsx Microsoft.SqlServer.Dts.Pipeline are not CLS-compliant.
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.
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
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:
Hint:
just open the package in visual studio. go to the
package explorer
Tab (nearcontrol flow
anddata flow
tabs) you will find a treeview. it will leads you the way you have to search for the component you needUpdate 1 - C# Script @ 2019-07-08
If you are looking for a script that list all package objects you can use a similar script:
References
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: