使用C＃反向工程SSIS包 [英] Reverse engineering SSIS package using C#
问题描述
需要提取源，目的地和列来源和目的地的$ c>名称。我之所以尝试这样做，是因为我有成千上万个软件包，打开每个软件包平均要有 60到75 列，列出所有必需的信息将需要大量时间，而不是一个单一的时间要求，该任务目前在我的组织中每两个月手动完成一次。
我正在寻找一些反向工程方法，将所有软件包都保存在一个文件夹中，然后遍历每个软件包并获取信息，然后将其放入电子表格中。
我想在 xml 中打开包，并获取感兴趣的节点的信息并放入电子表格中，这有点麻烦。请提出一些可用的库。
SQL Server提供了程序集来以编程方式操作程序包。 / em>
要进行逆向工程（反序列化dtsx包），您必须通过遍历包并以编程方式读取它们来执行此操作，只需按照此详细链接
-
更新1-C＃脚本@ 2019-07-08
如果寻找列出所有包对象的脚本，您可以使用类似的脚本：
using System;
使用DtsRuntime = Microsoft.SqlServer.Dts.Runtime;
使用DtsWrapper = Microsoft.SqlServer.Dts.Pipeline.Wrapper;
public void Main（）
{
string pkgLocation;
DtsRuntime.Package pkg;
DtsRuntime.Application应用程序；
DtsRuntime。 DTSExecResult pkgResults;
pkgLocation =
@ D：\Test\Package 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 + \t + TH.HostType.ToString（））;
//数据流任务组件
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 {
// //如果不是数据流任务，则继续foreach循环
}
}
}
//事件处理程序
foreach（pkg.EventHandlers中的DtsRuntime.DtsEventHandler eh）
{
MessageBox.Show（eh.Name +- + CM.HostType）;
}
//连接管理器
foreach（DtsRuntime.ConnectionManager CM in pkg.Connections）
{
MessageBox.Show（CM.Name +- + CM.HostType）;
}
//参数
foreach（DtsRuntime.Parameter param in pkg.Parameters）
{
MessageBox.Show（Param.Name +- + Param.DataType.ToString（））;
}
//变量
foreach（DtsRuntime.Variable Var in pkg.Variables）
{
MessageBox.Show（Var.Name +- + Var.DataType.ToString（））;
}
//优先约束
foreach（DtsRuntime.PrecedenceConstraint PC in pkg.PrecedenceConstraints）
{
MessageBox.Show（PC.Name）;
}
}
参考文献
更新2-SSISPackageExplorer项目@ 2019-07-10
我在Git-Hub上启动了一个名为SSISPackageExplorer的小项目，该项目允许用户读取TreeView中的包对象，这是非常基本的，但是我会在一段时间内尝试对其进行改进：
< img src = https://i.stack.imgur.com/AT603.png alt =在此处输入图片描述>
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:\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
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屋！
![< img src = https://i.stack.imgur.com/AT603.png alt =在此处输入图片描述>](\"https://i.stack.imgur.com/AT603.png\"){kind=link}