使用 C# 逆向工程 SSIS 包 [英] Reverse engineering SSIS package using C#
问题描述
需要提取source和destination的source、destination和column名称.我为什么要这样做是因为我有数千个包裹，打开每个包裹平均有 60 到 75 列并列出所有必需的信息将花费大量时间，而不是一个时间要求，此任务目前在我的组织中每两个月手动完成一次.
我正在寻找一些方法来反向工程将所有包保存在一个文件夹中，然后浏览每个包并获取信息并将其放入某个电子表格中.
我想到了在 xml 中打开包并获取感兴趣节点的信息并放入电子表格中，这有点麻烦.请建议可以从哪些库开始.
SQL server 提供程序集以编程方式操作包.
要进行逆向工程(反序列化 dtsx 包)，您必须通过循环包并以编程方式读取它们来完成此操作，只需按照此详细链接进行操作
- <小时>
更新 1 - C# 脚本 @ 2019-07-08
如果您正在寻找列出所有包对象的脚本，您可以使用类似的脚本:
使用系统；使用 DtsRuntime = Microsoft.SqlServer.Dts.Runtime;使用 DtsWrapper = Microsoft.SqlServer.Dts.Pipeline.Wrapper;公共无效主(){字符串 pkgLocation;DtsRuntime.Package pkg;DtsRuntime.Application 应用程序；Dts运行时.DTSExecResult pkgResults;pkgLocation =@"D:TestPackage 1.dtsx";app = new DtsRuntime.Application();pkg = app.LoadPackage(pkgLocation, null);//列出可执行文件(任务)foreach(dtsRuntime.Executable tsk in pkg.Executables){DtsRuntime.TaskHost TH = (DtsRuntime.TaskHost)tsk;MessageBox.Show(TH.Name + " " + TH.HostType.ToString());//数据流任务组件if (TH.InnerObject.ToString() == "System.__ComObject"){尝试{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());}}抓住 {//如果不是数据流任务则继续foreach循环}}}//事件处理程序foreach(DtsRuntime.DtsEventHandler eh in pkg.EventHandlers){MessageBox.Show(eh.Name + " - " + CM.HostType);}//连接管理器foreach(pkg.Connections 中的 DtsRuntime.ConnectionManager CM){MessageBox.Show(CM.Name + " - " + CM.HostType);}//参数foreach(pkg.Parameters 中的 DtsRuntime.Parameter Param){MessageBox.Show(Param.Name + " - " + Param.DataType.ToString());}//变量foreach(pkg.Variables 中的 DtsRuntime.Variable Var){MessageBox.Show(Var.Name + " - " + Var.DataType.ToString());}//优先约束foreach(pkg.PrecedenceConstraints 中的 DtsRuntime.PrecedenceConstraint PC){MessageBox.Show(PC.Name);}}参考资料
There is a requirement to extract
source,destinationandcolumnnames ofsourceanddestination. Why am I trying to do this is because I have thousands of packages and opening each package has on an average60 to 75of 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
xmland 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.解决方案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 explorerTab (nearcontrol flowanddata flowtabs) 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:TestPackage 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 + " " + 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
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:
这篇关于使用 C# 逆向工程 SSIS 包的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
