SSIS包或CLR功能 [英] SSIS Package or CLR Function
问题描述
我们有一个表格，其中包含客户的基本数据（例如，名字，姓氏，身份代码等等）。该表大约有100,000行。
We have a table which contains the basic data of our customers (e.g., First Name, Last Name, Identity Code and so fourth). The table has approximately 100,000 rows.
现在，我们需要调用多个Web服务来为每个客户获取一些数据，然后从Web服务中插入接收到的数据进入数据库中的其他一些表。
Now, we need to call several web services to get some data for each customer and then insert the received data from web services into some other tables in the database.
我正在考虑两种方法来完成这项任务：
I'm considering two approaches to accomplish this task:
-
声明几个表值CLR函数并通过一些.Net托管代码调用Web服务，然后在几个存储过程中使用这些CLR函数将接收到的数据插入到几个表中。
-
Declaring several table-valued CLR functions and call web services through some .Net managed code, and then use these CLR functions in several stored procedures to insert the received data into several tables.
我正在考虑的第二种方法是使用SSIS包。例如，我可以在SSIS包中创建一些脚本任务并调用Web服务，然后将接收到的数据插入到某些表中通过ADO.Net或其他一些提供商。
The second approach which I'm considering is using an SSIS Package. For example, I can create some Script Tasks inside the SSIS Package and call web services, and then insert the received data into some tables through ADO.Net or some other providers.
你认为哪种方法最好？为什么？
Which approach is the best from your opinion? and why?
推荐答案
你会发现它更容易遵循SSIS方法.CLR功能有一个问题：默认情况下，您将无法调用Web服务。这不仅仅是授予"外部访问"权限的问题，您还必须引入
WCF库，默认情况下它无法访问。我记得做网络搜索试图找到让它工作的技巧，而且我记得它比应该更难。
You will find it easier to follow the SSIS approach. The CLR functions have a problem: by default you will not be able to call the web services. It's not a mere problem of granting the "external access" permission, you also have to bring-in the WCF libraries which by default it cannot access. I remember doing a web search in an attempt to find the trick to make it work, and from what I recall it was more difficult than it should be.
另一方面，使用SSIS应该是直截了当的;所有必需的任务都是可用的，或者如果你需要做一些标准任务无法完成的事情，你可以添加一个脚本任务。它可以做这样的事情例如，如果出现错误（如果启用了检查点），则从
点重新启动它，或者创建详细的日志，这将占用你一些努力与CLR方法有关。
On the other hand, using SSIS should be straightforward; all the required tasks are available, or you may add a Script task if you need to do something that the standard tasks cannot accomplish. And it can do things such as for instance restarting from the point where it was interrupted in case of an error (if you enable the checkpoints) or creating detailed logs, which would take you some effort to do with the CLR approach.
这篇关于SSIS包或CLR功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
