Proxy Class is fine when used in C# project but returns error when Used with SSIS

My proxy class returns web service responses well if I use it in a C# code.

I compile it with CSC and put it in GAC to use it with SSIS.

Then I get web service response with some field values populated with "Exception of type :{System.InvalidOperationException "' occured.

Here is the part of web service response received by the C# program:

NameValueType
mainAddress{Address}Address
cityMetuchenstring
cityFieldMetuchenstring
countryUSACountry
countryFieldUSACountry
stateNEW_JERSEYState
stateFieldNEW_JERSEYState

Here is the same object returned by compiled dll used in SSIS:

mainAddress{BrokerConnectServiceV1.Address}BrokerConnectServiceV1.Address
cityMetuchenString
country{System.Nullable(Of BrokerConnectServiceV1.Country)}System.Nullable(Of BrokerConnectServiceV1.Country)
HasValueFALSEBoolean
ValueException of type: '{System.InvalidOperationException}' occurred.BrokerConnectServiceV1.Country
Data{System.Collections.ListDictionaryInternal}
HelpLinkNothing
InnerExceptionNothing
MessageNullable object must have a value.
Sourcemscorlib
StackTraceat System.ThrowHelper.ThrowInvalidOperationException(ExceptionResource resource)

at System.Nullable`1.get_Value()
TargetSite{System.Reflection.RuntimeMethodInfo}
state{System.Nullable(Of BrokerConnectServiceV1.State)}System.Nullable(Of BrokerConnectServiceV1.State)
HasValueFALSEBoolean
ValueException of type: '{System.InvalidOperationException}' occurred.BrokerConnectServiceV1.State

Below is another posting by my associate on the same issue :

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=536615&SiteID=1

Any help is appreciated....

[21397 byte] By [Gulden] at [2007-12-22]
# 1

Any comments on this?

Thanks,

Gulden at 2007-8-30 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 2

Any questions or Comments about this...?

Gulden at 2007-8-30 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 3
where exactly is ssis calling the web service?...
DuaneDouglas at 2007-8-30 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 4

Thanks Duane for answering.

SSIS is calling the web service in a Script Task. WSDL is compiled into a DLL and referenced in the Script task.

Gulden at 2007-8-30 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 5
have you tried using ssis to debug the script? http://msdn2.microsoft.com/en-us/library/ms142157.aspx
DuaneDouglas at 2007-8-30 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 6
The error lines posted above are from examining web service responses on the debugger.
Gulden at 2007-8-30 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 7
Gulden wrote:
The error lines posted above are from examining web service responses on the debugger.
what is the line of code in ssis that threw the invalidoperationexception?
DuaneDouglas at 2007-8-30 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 8
As I mentioned above, Error is embedded in the web service response. It is not generated by the line of code
Gulden at 2007-8-30 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 9

Gulden wrote:
As I mentioned above, Error is embedded in the web service response. It is not generated by the line of code

i understand. i am attempting to help you figure out why an exception is being thrown when ssis calls the web service. my suspicion is that there is a problem with the way that ssis calls the web service. this is why i asked you to post the ssis code that calls the web service.

DuaneDouglas at 2007-8-30 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 10

I appreciate the effort.

Below is the script task code.

There is a DLL generated from WSDL this code references on the BrokerConnectServiceV1 and LoginServiceV1 lines

Imports System

Imports System.Data

Imports System.Math

Imports System.Net

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Collections.Generic

Imports BrokerConnectServiceV1

Imports LoginServiceV1

Public Class GetAccounts

Public Sub Main()

'

Dim logByte(0) As Byte

Dim accountService As New BrokerConnectServiceV1()

Dim header As New SessionIdHeader

Dim accountDetail As Account

Dim myProxy As New System.Net.WebProxy(CStr(Dts.Variables("WebProxy").Value), True)

myProxy.Credentials = CredentialCache.DefaultCredentials

Try

header.sessionId = CStr(Dts.Variables("Aptus_Session_Id").Value)

accountService.Proxy = myProxy

accountService.SessionIdHeaderValue = header

accountDetail = accountService.getAccount(117177) 'This is the line I set my debugger to review the values returned in accountDetail object

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception

Dts.Log("GET Accounts:" & ex.Message, 0, logByte)

Dts.Variables("BPA_Exception").Value = ex.Message

Dts.TaskResult = Dts.Results.Failure

End Try

End Sub

End Class

Gulden at 2007-8-30 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 11

in the script task, did you add a reference to the dll that contains the BrokerConnectServiceV1 namespace?

can you also post the code for this namespace?

DuaneDouglas at 2007-8-30 > top of Msdn Tech,SQL Server,SQL Server Integration Services...

SQL Server

Site Classified