SSIS脚本任务Web服务错误
本文关键字：服务 错误 Web 任务 脚本 SSIS | 更新日期: 2023-09-27 18:15:54
我在SSIS包中有一个脚本任务来调用web服务(WCF)。我所要做的就是向web服务发送请求，我会收到一个结果(1或0)。但是，如果失败，则会出现以下错误消息。当我把代码放在一个窗口应用程序的形式，它工作。
我几乎从窗口应用程序复制了代码，并试图使其在SSIS中工作。不幸的是，我不知道web服务是如何工作的，也不知道从哪里开始寻找解决方案。我需要装订吗?有一个应用配置文件，上面写着CustomBinding。我再次为我的无知道歉，我完全没有头绪。任何帮助都会很感激。谢谢!
在System.RuntimeMethodHandle。调用方法(对象目标，对象[]参数，签名签名，布尔构造函数)在System.Reflection.RuntimeMethodInfo。UnsafeInvokeInternal(对象obj，对象[]参数，对象[]参数)在System.Reflection.RuntimeMethodInfo。调用(对象obj, BindingFlags invokeAttr, Binder, Binder, Object[] parameters, CultureInfo culture)在System.RuntimeType。InvokeMember(String name, BindingFlags BindingFlags, Binder Binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)在Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript ()
下面是我在脚本任务中的代码。
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
// TODO: Add your code here
ServiceReference.SendMessageClient svc = new ServiceReference.SendMessageClient();
MessageCredentialsHeader MessageSecurityHeader = new MessageCredentialsHeader("user", "pw");
try
{
using (System.ServiceModel.OperationContextScope contextScope = new System.ServiceModel.OperationContextScope(svc.InnerChannel))
{
System.ServiceModel.OperationContext.Current.OutgoingMessageHeaders.Add(MessageSecurityHeader);
MessageBox.Show(svc.SendMessage("111", "222", "SSIS test").Result.ToString());
}
}
catch (Exception ex)
{
string s = ex.Message;
}
Dts.TaskResult = (int)ScriptResults.Success;
}
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
public class MessageCredentialsHeader: System.ServiceModel.Channels.MessageHeader
{
public string Username { get; set; }
public string Password { get; set; }
public MessageCredentialsHeader(string username, string password)
{
Username = username;
Password = password;
}
public override string Name
{
get { return "Security"; }
}
public override string Namespace
{
get { return "http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"; }
}
public override bool MustUnderstand
{
get
{
return true;
}
}
protected override void OnWriteStartHeader(XmlDictionaryWriter writer, System.ServiceModel.Channels.MessageVersion messageVersion)
{
base.OnWriteStartHeader(writer, messageVersion);
string prefix = writer.LookupPrefix("http://schemas.xmlsoap.org/soap/envelope/");
writer.WriteAttributeString(prefix + ":actor", "http://example.org/ws/webservicesecurity");
}
protected override void OnWriteHeaderContents(System.Xml.XmlDictionaryWriter writer, System.ServiceModel.Channels.MessageVersion messageVersion)
{
writer.WriteStartElement("UsernameToken", Namespace);
writer.WriteStartElement("Username", Namespace);
writer.WriteRaw(Username);
writer.WriteEndElement();
writer.WriteStartElement("Password", Namespace);
writer.WriteAttributeString("Type", "http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText");
writer.WriteRaw(Password);
writer.WriteEndElement();
writer.WriteEndElement();
}
}
}
你可以传递一个readwrite参数给你的脚本任务-例如securityResponse。首先需要在包中创建一个变量，该变量具有xml响应的值。然后，在您的脚本代码中，您可以添加这样的代码，这可能会引导您在正确的方向:
object nativeObject = Dts.Connections["Webservice"].AcquireConnection(null);
HttpClientConnection conn = new HttpClientConnection(nativeObject);
Service ws = new Service(conn.ServerURL);
securityRequest req = new securityRequest();
req.username = "****";
req.password = "****";
securityResponse response = ws.GetSecurityResp(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::securityResponse"].Value = responseToXml.ToString();
Dts.TaskResult = (int)ScriptResults.Success;
然后，您将需要使用XML源创建另一个数据流任务，该XML源读取该XML并将其放置到记录集目标或表中。
原来SSIS无法访问脚本任务中的应用程序配置文件。这就是为什么我必须在代码中编写绑定。我只是添加了以下绑定代码，它工作了。
HttpsTransportBindingElement httpsTransport = new HttpsTransportBindingElement();
httpsTransport.ManualAddressing = false;
httpsTransport.MaxBufferPoolSize = 524288;
httpsTransport.MaxReceivedMessageSize = 65536;
httpsTransport.AllowCookies = false;
httpsTransport.AuthenticationScheme = System.Net.AuthenticationSchemes.Anonymous;
httpsTransport.BypassProxyOnLocal = false;
httpsTransport.DecompressionEnabled = true;
httpsTransport.HostNameComparisonMode = HostNameComparisonMode.StrongWildcard;
httpsTransport.KeepAliveEnabled = true;
httpsTransport.MaxBufferSize = 65536;
httpsTransport.ProxyAuthenticationScheme = System.Net.AuthenticationSchemes.Anonymous;
httpsTransport.Realm = "";
httpsTransport.TransferMode = TransferMode.Buffered;
httpsTransport.UnsafeConnectionNtlmAuthentication = false;
httpsTransport.UseDefaultWebProxy = true;
httpsTransport.RequireClientCertificate = false;
TextMessageEncodingBindingElement encoding = new TextMessageEncodingBindingElement();
encoding.MessageVersion = MessageVersion.Soap11;
encoding.WriteEncoding = Encoding.UTF8;
encoding.MaxReadPoolSize = 64;
encoding.MaxWritePoolSize = 16;
encoding.ReaderQuotas.MaxDepth = 32;
encoding.ReaderQuotas.MaxStringContentLength = 8192;
encoding.ReaderQuotas.MaxArrayLength = 16384;
encoding.ReaderQuotas.MaxBytesPerRead = 4096;
encoding.ReaderQuotas.MaxNameTableCharCount = 16384;
CustomBinding binding = new CustomBinding();
binding.Name = "SendMessage";
binding.Elements.Add(encoding);
binding.Elements.Add(httpsTransport);
EndpointAddress endPoint = new EndpointAddress("https://example.org/SendMessage");
ServiceReference.SendMessageClient svc = new ServiceReference.SendMessageClient (binding, endPoint);