如何从SSIS脚本任务将值传递给Web服务？

I am following this blog to write values to a web service. So far, I have been successful in reading my dataset and storing it within an object variable and then looping over them to display it one after the other.

我正在关注此博客，以便为Web服务编写值。到目前为止，我已经成功地读取了我的数据集并将其存储在一个对象变量中，然后循环遍历它们以一个接一个地显示它。

    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Runtime
    Imports System.Xml
    Imports System.Data.OleDb

    Public Class ScriptMain

     Public Sub Main()

      Dim oleDA As New OleDbDataAdapter
      Dim dt As New DataTable
      Dim col As DataColumn
      Dim row As DataRow
      Dim sMsg As String

      oleDA.Fill(dt, Dts.Variables("dsVar").Value)

      For Each row In dt.Rows
       For Each col In dt.Columns
        sMsg = sMsg & col.ColumnName & ": " & _
               row(col.Ordinal).ToString & vbCrLf
       Next
       MsgBox(sMsg)   //These 2 lines need to be changed
       sMsg = ""      //So that the Results can be sent to the web service.
      Next

      Dts.TaskResult = Dts.Results.Success

     End Sub

    End Class

This is all fine.

这一切都很好。

Since I dont have any experience with .net programming, I was hoping to find some help in changing the code to write the same values to a web service call. I need to send a few rows and columns over to the web service which in turn would write the data to another database (Its DMZ and stuff and I dont know much about that). Can someone point me in the right direction. I dont intend on using the Web Service Task as my boss told me that he already had issues using the same.

由于我没有任何.net编程经验，我希望在更改代码时能找到一些帮助，将相同的值写入Web服务调用。我需要向Web服务发送一些行和列，然后将数据写入另一个数据库（其DMZ和东西，我不太了解）。有人能指出我正确的方向。我不打算使用Web服务任务，因为我的老板告诉我他已经有使用相同的问题了。

Any help in this regard is greatly appreciated.

非常感谢在这方面的任何帮助。

1 个解决方案

#1

What you can do is add a script task to call the web service, passing in the values in your dataset as a request variable - you can add a request and response variable in the SSIS package by clicking package explorer then variables. In the value of your response variable, for example, for the value property you could have:

您可以做的是添加一个脚本任务来调用Web服务，将数据集中的值作为请求变量传递 - 您可以通过单击package explorer then变量在SSIS包中添加请求和响应变量。例如，在响应变量的值中，您可以使用value属性：

<?xml version="1.0" encoding="utf-8"?> <GetUpdatedResponse 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">   <yourDataset/></GetUpdatedResponse>

which is the XML of your response.

这是您的回复的XML。

Once you have your variables set up, you can specify the variable to pass into the script task e.g. user::yourReqVar

设置变量后，可以指定要传递给脚本任务的变量，例如用户:: yourReqVar

Then you can create a script task with something like as follows:

然后，您可以使用如下内容创建脚本任务：

/// <summary>
/// This method is called when this script task executes in the control flow.
/// Before returning from this method, set the value of Dts.TaskResult to indicate success or
/// failure.
/// </summary>
    public void Main()
    {
        if (Dts.Variables.Contains("yourReqVar") == true)
        {
            try
            {
                object nativeObject = Dts.Connections["YourWebservice"].AcquireConnection(null);
                HttpClientConnection conn = new HttpClientConnection(nativeObject);

                YourService ws = new YourService(conn.ServerURL);
                GetUpdatedRequest req = new GetUpdatedRequest();
                req.username = conn.ServerUserName;
                req.password = "A123232";
                req.dateRange = new dateRange();
                req.dateRange.from = DateTime.Now.AddDays((dayIncrement * -1));
                req.dateRange.to = DateTime.Now;
                req.dateRange.fromSpecified = true;
                req.dateRange.toSpecified = true;
                GetUpdatedResponse response = ws.GetUpdated(req);

                System.Xml.Serialization.XmlSerializer x
                  = new System.Xml.Serialization.XmlSerializer(response.GetType());
                StringWriterWithEncoding responseToXml
                  = new StringWriterWithEncoding(new StringBuilder(), Encoding.UTF8);

                x.Serialize(responseToXml, response);
                Dts.Variables["User::GetUpdated"].Value = responseToXml.ToString();
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception) {
                Dts.Events.FireWarning(0, "Skip", "Failed to retrieve updated.", String.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Success;
            }
        }
        else
        {
            Dts.TaskResult = (int)ScriptResults.Failure;
        }
    }