CODEWITHSUNDEEP
c#.netweb-servicesssis
rvphx
rvphx

Reputation: 2402

How to pass values to a Web Service from SSIS Script task?

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

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.

Any help in this regard is greatly appreciated.

Upvotes: 1

Views: 17144

Answers (1)

Robert Anderson
Robert Anderson

Reputation: 1256

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>

which is the XML of your response.

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;
        }
    }

Upvotes: 5

Related Questions