通过脚本任务在 SSIS 中调用安全 Web 服务

问题描述

我正在开发一个 SSIS 包，我们必须通过脚本任务在 SSIS 中调用或使用 Web 服务.我浏览了很多链接，但找不到确切的解决方案.我收到了很多参考资料，但我无法破解.

I am working on a SSIS package where we have to call or consume a web service in SSIS through Script task. I have gone through so many links but I am not able to find the exact solution. I am getting so many references, though I am unable to crack it.

我的要求是我需要通过具有客户端证书的脚本任务调用 Web 服务 URL.调用此 URL 后，我们将从 Web 服务中获取 WSDL 文件.我们需要使用那个 WSDL 文件，我们需要识别这个 WSDL 中的方法，并且需要将这个 WSDL 中可用的数据写入数据库表.我不知道如何通过脚本 tas 调用该 Web 服务 URL(带证书)，如何读取 WSDL 文件以及如何将数据加载到数据库表中.

My requirement is I need to call a web service URL through script task which is having a client certificate. After calling this URL we will get a WSDL file from the web service. We need to consume that WSDL file and we need to identify the methods inside this WSDL and need to write the data available in this WSDL to the data base tables. I am not having an idea how can we call that web service URL (with certificate) through script tas, how can we read the WSDL file and how we can load the data into DB table.

推荐答案

在 Main 方法中，使用以下脚本(高级)开始...

In the Main method, use the following script (high level) to get start with...

 var endPointAddress = new EndpointAddress('http://Server/ServiceName.svc');
 //Put your end point address
 var basicBinding = new BasicHttpBinding();
 basicBinding.Name = "BasicHttpBinding_IService";
 //this is the port name, you can find it in the WSDL
 ClassServiceClient pay = new ClassServiceClient (basicBinding, endPointAddress);
 //this is the class in which the method exists you want to make a service call
 IService = pay.YourMethodName();
 XMLDocument xmlOut = new XmlDocument();
 //This is to store return value from your method
 xmlOut.LoadXml(IService);
 //Load the xmlOut with the return value
 XmlNode xmlNode = xmlOut.SelectSingleNode("ParentElement/ChildElement");
 //Search for your element name where you want to get the value
 string strValue = xmlNode.InnerText;
 //this gives the element value

接下来，使用 DataTable 类，通过创建新行来加载 strValue

Next, using DataTable class, load the strValue by creating new rows

DataTable dt = new DataTable();
DataRow dr = dt.NewRow();
dr["ValueToInsertIntoDb"] = strValue;
dr.Rows.Add(dr);

然后将 dt 分配给对象变量.

After that assign the dt to an Object Variable.

Dts.Variables["User::Values"].Value = dt;

接下来，使用另一个数据流任务，在其中使用脚本组件并选择 ReadOnlyVariables 中的变量.在脚本组件中，您必须循环遍历 DataTable 数据集.这是应该看起来像的代码

Next, use another Data flow task, inside that use a Script component and select the variable in ReadOnlyVariables. Inside the script component, you have to loop through the DataTable dataset. Here's the code that should look like

 DataTable dt = (DataTable)Variables.Values
   foreach (DataRow dr in dt.Rows)
   {
     ScriptComponentOutputBuffer.AddRow()
     ScriptComponentOutputBuffer.Column1 = dr["ValueToInsertIntoDb"].ToString();
   }
   //ScriptComponentOutputBuffer.Column1 --You need to manually add this column on output columns of your scriptcomponent

接下来，将脚本组件连接到 OLEDB 命令或 OLE DB 目标并将值插入到数据库中.

Next, connect the script component to a OLEDB Command or OLE DB Destination and insert the values into the database.

这篇关于通过脚本任务在 SSIS 中调用安全 Web 服务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！