Consuming Web Service in SSIS gives 'Mismatch

2019-07-09 02:59发布

问题:

I tried the tutorial below to consume a web service in a Script Component in SSIS.

--Link to tutorial--

After pre-compiling I get following warning:

Warning 1 There was a mismatch between the processor architecture of the project being built "MSIL" and the processor architecture of the reference "Microsoft.SqlServer.DTSRuntimeWrap, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91", "AMD64". This mismatch may cause runtime failures. Please consider changing the targeted processor architecture of your project through the Configuration Manager so as to align the processor architectures between your project and references, or take a dependency on references with a processor architecture that matches the targeted processor architecture of your project. C:\Windows\Microsoft.NET\Framework\v4.0.30319\Microsoft.Common.targets

And my script component cannot run.

I found the solution of Microsoft here but I can't apply this in SSIS. Any help is appreciated

-- EDIT --

I sent a new feedback to Microsoft, they are reviewing the issue - Status: Active

回答1:

This is my solution

I added a Service Reference, instead of Web Reference

by Right-clicking on References in the 'Solution Explorer' -> select 'Add Service Reference' -> Paste the address (http://www.ibanbic.be/IBANBIC.asmx) -> Click on 'Go' Button - Then 'OK'

I added the following References in code

using MYSCRIPTCOMPONENT.ServiceReference1;
using System.ServiceModel;
using System.Net.Http;

UPDATED

This is how my code looks like:

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        using (var client = ScriptMain.CreateWebServiceInstance())
        {
            Row.BankIBAN = client.BBANtoIBANandBIC(Row.AccountNum_IsNull ? "" : Row.AccountNum).ToString();
        }
    }

    internal static SC_MYSCRIPTCOMPONENT.ServiceReference1.BANBICSoapClient CreateWebServiceInstance()
    {
        BasicHttpBinding binding = new BasicHttpBinding();
        // I think most (or all) of these are defaults--I just copied them from app.config:
        binding.Security.Mode = BasicHttpSecurityMode.None;
        binding.SendTimeout = TimeSpan.FromMinutes(1);
        binding.OpenTimeout = TimeSpan.FromMinutes(1);
        binding.CloseTimeout = TimeSpan.FromMinutes(1);
        binding.ReceiveTimeout = TimeSpan.FromMinutes(10);
        binding.AllowCookies = false;
        binding.BypassProxyOnLocal = true;
        binding.HostNameComparisonMode = HostNameComparisonMode.StrongWildcard;
        binding.MessageEncoding = WSMessageEncoding.Text;
        binding.TextEncoding = System.Text.Encoding.UTF8;
        binding.TransferMode = TransferMode.Buffered;
        System.Net.ServicePointManager.Expect100Continue = false;
        binding.UseDefaultWebProxy = true;
        return new SC_MYSCRIPTCOMPONENT.ServiceReference1.BANBICSoapClient(binding, new EndpointAddress("http://www.ibanbic.be/IBANBIC.asmx"));
    }

EDIT

I was getting an error when running my Package, which said:

The underlying connection was closed: The connection was closed unexpectedly.

I added this line of code (in my CreateWebServiceInstance-method):

System.Net.ServicePointManager.Expect100Continue = false;

SUMMARY

  1. I added a Service Reference - as mentioned here. [STACK OVERFLOW]
  2. I added code to set the binding - as mentioned here. [STACK OVERFLOW]
  3. I added one line to fix 'the connection was closed'-error - as provided on MSDN.