I've been asked to take over ownership of an SSIS package that was written a while ago. It certainly seems possible to go in and modify, edit, and upgrade this package, but it also seems extremely cumbersome for the amount of work the package does.
我被要求接管一段时间前写的SSIS包的所有权。它当然可以进入并修改,编辑和升级这个软件包,但对于软件包的工作量来说,它似乎也非常麻烦。
My task is roughly this: Sift through the package, find any usage of some specific columns in specific tables, change them to match the latest database changeset, and write some additional logging data out to help the team diagnose any particular problems.
我的任务大致如下:筛选包,找到特定表中某些特定列的使用情况,更改它们以匹配最新的数据库更改集,并写出一些额外的日志记录数据,以帮助团队诊断任何特定问题。
This appears daunting to me because the SSIS package contains about 3 megabytes of text, SQL statements, and extremely simple (but lengthy) code pathways. It's already taken me a while to double click on each SSIS object and skim through its settings to see if I can spot the columns I've been asked to maintain, and SSIS keeps throwing connection errors (rightly so, because my dev environment is blocked off from the production database).
这对我来说似乎令人生畏,因为SSIS包中包含大约3兆字节的文本,SQL语句和极其简单(但很冗长)的代码路径。我已经花了一段时间双击每个SSIS对象并浏览其设置以查看我是否能够发现我被要求维护的列,并且SSIS不断抛出连接错误(正确的是,因为我的开发环境被阻止了从生产数据库中删除)。
I'd really like to consider converting this SSIS package to a .NET application, however I haven't been able to locate any tools that could help me do so.
我真的很想考虑将这个SSIS包转换为.NET应用程序,但是我找不到任何可以帮助我这样做的工具。
Does anyone have suggestions for maintaining this SSIS package or converting it to a proper application?
有没有人建议维护这个SSIS包或将其转换为适当的应用程序?
3 个解决方案
#1
4
Replying to those who prefer .NET to SSIS
回复那些喜欢.NET到SSIS的人
SSIS is not meant to transfer small amount of data . It can do , but when the data transfer is huge around 100 to 500 GB and involves a pretty complex business logic then SSIS is more preferred than .NET.If there are multiple data files with multiple data sources then SSIS is way to go . .Net is preferred if you have small import or export with very little business logic for enhancement or transformation of data
SSIS并不意味着传输少量数据。它可以做到,但是当数据传输量大约为100到500 GB并且涉及相当复杂的业务逻辑时,SSIS比.NET更受欢迎。如果有多个数据文件包含多个数据源,那么SSIS就可以了。如果您使用很少的业务逻辑进行增强或转换数据,那么.Net是首选
Unfortunately there is no tool out there which can transform you ssis package to .NET app but there are free tools to help you give more information about your package without opening it .
不幸的是,没有工具可以将您的ssis包转换为.NET应用程序,但有一些免费工具可以帮助您提供有关包的更多信息而无需打开它。
BI Documentor is a tool which gives you the complete schema of your SSIS package .You just need to point the application to your dtx pkg .If you have numerous sql codes and expressions,variable ,connection manager which you need to modify ,you can do this with the help of this tool .Sometimes Opening a complex ssis package in BIDS takes a lot of time so this may be useful in this case .
BI Documentor是一个工具,它为您提供SSIS包的完整模式。您只需要将应用程序指向您的dtx pkg。如果您有许多sql代码和表达式,变量,您需要修改的连接管理器,您可以执行在这个工具的帮助下。有时在BIDS中打开一个复杂的ssis包需要花费很多时间,因此在这种情况下这可能很有用。
There is one more tool called SSIS Log Analyser which helps you to debug your package without opening BIDS
还有一个名为SSIS Log Analyzer的工具可以帮助您调试程序包而无需打开BIDS
#2
6
Just for fun I wrote an SSIS decompiler. After a bit of looking at the UI of Visual Studio running Integration Services, and comparing that to the raw XML data in the .DTSX file, I was able to put together a program that converts the DTSX file into a C# / .NET project.
为了好玩,我写了一个SSIS反编译器。在查看运行Integration Services的Visual Studio的UI并将其与.DTSX文件中的原始XML数据进行比较之后,我能够将一个将DTSX文件转换为C#/ .NET项目的程序组合在一起。
I combined this with a tiny bit of profiling data, and I was able to get a 40% speed improvement in my SSIS package by tracking down the components that had problems.
我将它与一小部分分析数据相结合,通过跟踪出现问题的组件,我能够在SSIS包中获得40%的速度提升。
Seems to work pretty well for the SSIS packages I use. Maybe this will help someone else.
对我使用的SSIS包似乎工作得很好。也许这会帮助别人。
#3
0
I don't think that there is any specific tool for this. It might be the best to just take some text editor and go through the code and identify the changed columns you have to deal with.
我认为没有任何具体的工具。最好只需要一些文本编辑器并浏览代码并确定您必须处理的已更改列。
Personally, I don't like SSIS because it is too much "visual tool", and would probably go with .NET app, but since you already have all the job done, and you probably don't want to dedicate your career to rebuilding that data transformation tool you've been given, just make those changes in SSIS, make it work, and then continue with your joyful programming life...or even better, find some SQL Server administrator to do this for your, they like SSIS.
就个人而言,我不喜欢SSIS,因为它太过“可视化工具”了,可能会使用.NET应用程序,但是因为你已经完成了所有的工作,而且你可能不想把你的职业生涯奉献给重建您已经获得了数据转换工具,只需在SSIS中进行更改,使其工作,然后继续您快乐的编程生活......或者甚至更好,找一些SQL Server管理员为您执行此操作,他们喜欢SSIS 。
#1
4
Replying to those who prefer .NET to SSIS
回复那些喜欢.NET到SSIS的人
SSIS is not meant to transfer small amount of data . It can do , but when the data transfer is huge around 100 to 500 GB and involves a pretty complex business logic then SSIS is more preferred than .NET.If there are multiple data files with multiple data sources then SSIS is way to go . .Net is preferred if you have small import or export with very little business logic for enhancement or transformation of data
SSIS并不意味着传输少量数据。它可以做到,但是当数据传输量大约为100到500 GB并且涉及相当复杂的业务逻辑时,SSIS比.NET更受欢迎。如果有多个数据文件包含多个数据源,那么SSIS就可以了。如果您使用很少的业务逻辑进行增强或转换数据,那么.Net是首选
Unfortunately there is no tool out there which can transform you ssis package to .NET app but there are free tools to help you give more information about your package without opening it .
不幸的是,没有工具可以将您的ssis包转换为.NET应用程序,但有一些免费工具可以帮助您提供有关包的更多信息而无需打开它。
BI Documentor is a tool which gives you the complete schema of your SSIS package .You just need to point the application to your dtx pkg .If you have numerous sql codes and expressions,variable ,connection manager which you need to modify ,you can do this with the help of this tool .Sometimes Opening a complex ssis package in BIDS takes a lot of time so this may be useful in this case .
BI Documentor是一个工具,它为您提供SSIS包的完整模式。您只需要将应用程序指向您的dtx pkg。如果您有许多sql代码和表达式,变量,您需要修改的连接管理器,您可以执行在这个工具的帮助下。有时在BIDS中打开一个复杂的ssis包需要花费很多时间,因此在这种情况下这可能很有用。
There is one more tool called SSIS Log Analyser which helps you to debug your package without opening BIDS
还有一个名为SSIS Log Analyzer的工具可以帮助您调试程序包而无需打开BIDS
#2
6
Just for fun I wrote an SSIS decompiler. After a bit of looking at the UI of Visual Studio running Integration Services, and comparing that to the raw XML data in the .DTSX file, I was able to put together a program that converts the DTSX file into a C# / .NET project.
为了好玩,我写了一个SSIS反编译器。在查看运行Integration Services的Visual Studio的UI并将其与.DTSX文件中的原始XML数据进行比较之后,我能够将一个将DTSX文件转换为C#/ .NET项目的程序组合在一起。
I combined this with a tiny bit of profiling data, and I was able to get a 40% speed improvement in my SSIS package by tracking down the components that had problems.
我将它与一小部分分析数据相结合,通过跟踪出现问题的组件,我能够在SSIS包中获得40%的速度提升。
Seems to work pretty well for the SSIS packages I use. Maybe this will help someone else.
对我使用的SSIS包似乎工作得很好。也许这会帮助别人。
#3
0
I don't think that there is any specific tool for this. It might be the best to just take some text editor and go through the code and identify the changed columns you have to deal with.
我认为没有任何具体的工具。最好只需要一些文本编辑器并浏览代码并确定您必须处理的已更改列。
Personally, I don't like SSIS because it is too much "visual tool", and would probably go with .NET app, but since you already have all the job done, and you probably don't want to dedicate your career to rebuilding that data transformation tool you've been given, just make those changes in SSIS, make it work, and then continue with your joyful programming life...or even better, find some SQL Server administrator to do this for your, they like SSIS.
就个人而言,我不喜欢SSIS,因为它太过“可视化工具”了,可能会使用.NET应用程序,但是因为你已经完成了所有的工作,而且你可能不想把你的职业生涯奉献给重建您已经获得了数据转换工具,只需在SSIS中进行更改,使其工作,然后继续您快乐的编程生活......或者甚至更好,找一些SQL Server管理员为您执行此操作,他们喜欢SSIS 。