(Sorry, this is supposed to be in the SSIS forum, not XSL/XML)
I am quite new to SSIS so please bear with me if I use incorrect terminology.
I developed a SSIS package on MY PC and it works fine. But I'm having trouble when it runs on the server.
On our SSIS server (which is a 64 bit server where we installed SQL Server 2008) we have a series of ODBC connections configured using the Adaptive Server Enterprise. I have configured 32-bit ODBC connections and these do seem to be available to SSIS via ADO.net.
I created a data flow task and linked my sybase database to the ADO.net source connection. The query I used as my data source was a stored procedure call, i.e.:
When I click on Preview, generally first time in I get an error message like this:
Error at Data Flow Task ADO NET Source: System.AccessViolationException: Attempted to read or write protected memory.
Next time it seems to work OK and I get rows back. Clicking on the 'columns' are doesn't show me any output. If I then connect the source to a data flow transformation such as a 'Row Sampling' task it claims there are no input columns.
If I then click on the ADO.NET Source again I get a message saying, 'The component is not in a valid state. Do you want the component to fix itself automatically?'. If I say yes I get the error message again.
Can anyone suggest what I'm doing wrong? I'm sure it is something to do with these ODBC drivers.