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
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
- I added a Service Reference - as mentioned here. [STACK OVERFLOW]
- I added code to set the binding - as mentioned here. [STACK OVERFLOW]
- I added one line to fix 'the connection was closed'-error - as provided on MSDN.