如何传递值从SSIS脚本任务Web服务？ [英] How to pass values to a Web Service from SSIS Script task?

问题描述

我按照这样的博客写值的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.

    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

这是所有罚款。

由于我没有使用.NET的经验编程，我希望能找到改变的代码相同的值写入到一个Web服务调用一些帮助。我要到这反过来将数据写入到另一个数据库Web服务发送了几行和列（DMZ它和东西，我不知道很多关于这一点）。有人能指出我在正确的方向。我不打算使用Web服务的任务，因为我的老板告诉我，他已经使用了同样的问题。

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.

在这方面的任何帮助是极大的赞赏。

Any help in this regard is greatly appreciated.

推荐答案

什么你能做的就是添加一个脚本任务来调用Web服务，传入值数据集作为一个请求变量 - 你可以在SSIS包通过点击包浏览器然后变量添加请求和响应变量。在您的响应变量的值，例如，对于value属性，你可以有：

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:

<?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>

这是您的响应的XML。

which is the XML of your response.

一旦你有你的变量设置，您可以指定变量传递到脚本任务，例如：用户:: yourReqVar

Once you have your variables set up, you can specify the variable to pass into the script task e.g. user::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;
        }
    }

这篇关于如何传递值从SSIS脚本任务Web服务？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！