Hi All, I am in process of transfering data from Sybase to Sql Server using SSIS 2005 have taken a Data Flow Task in Control Flow tab In Data flow tab, I have taken one Ole DB Source and One OLe DB Destination For the source, I am using Sybase Adaptive Server Anywhere Provider 8.0 For Destination, I am using Sql Server 2005 database In Ole Db Source Editor ,For OLe Db Connection Manager, I choose Sybase ConnectionFor Data access mode, I choose Table or ViewFor Name of the table or the view, I choose a table by name Table1( it lists all the tables from Sybase database) When i click on preview button or Columns link, I get the following Error Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E21. Error at Data Flow Task [OLE DB Source [1]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available. ------------------------------ADDITIONAL INFORMATION: Exception from HRESULT: 0xC020204A (Microsoft.SqlServer.DTSPipelineWrap) Please help me out. Thanks in Advance Srinivas

Sorry I could not help, I have the same exact problem. I have tried reloading the drivers, but still the same. Please help. Thanks

I am just as luck as you are. It's a broad problem with "Microsoft.SqlServer.DTSPipelineWrap". I have no idea how to fix it. But I get quite a few problem with it. Exception from HRESULT: 0xC020204A Exception from HRESULT: 0xC0202022 Exception from HRESULT: 0xC02020E8 Like you, I did uninstall and reinstall, did not solve the problem. For no particular reason, it could back to work. I have no idea what bring it back to work. I have searched for two days, failed to find any helpful information. Sorry for sharing this bad news with you.

We've had similar problems trying to use OLEDB to retrieve data from an AS400. We had to resort to using a DataReader Source with the ODBC .NET Provider. Is that an option for you?

It is funny, on the datareader source you are able to read from an actual database, howevere, the datareader destination don't. How could I use the datareader destination with other objects to ultimately export data to another database, not just to an object in memory? Thanks

It's a workaround for extracting data using an OLEDB source. The same approach cannot be used to transfer data to Sybase. Have you looked into a third party provider such as DataDirectas an alternative?

Since the same driver works on DTS (sql 2000, and sql 7), I wonder ifthere is someone from microsoft looking into their SSIS fiasco? Thanks.

was this issue ever resolved ? If so how ?

Guatvao wrote: It is funny, on the datareader source you are able to read from an actual database, howevere, the datareader destination don't. How could I use the datareader destination with other objects to ultimately export data to another database, not just to an object in memory? Thanks You can't. That is not what it is for. Or did you see something in the documentation that caused you to think otherwise? -Jamie

Well, Then How am I suppose to create a DTS that interchanges data between sybase sql anywhere and sql server 2005. The DTS on SQL server 7 and 2000 were able to perform such tasks. Now, the transformation services are incapable to do that!!!! Thanks.

Guatvao wrote: Well, Then How am I suppose to create a DTS that interchanges data between sybase sql anywhere and sql server 2005. The DTS on SQL server 7 and 2000 were able to perform such tasks. Now, the transformation services are incapable to do that!!!! Thanks. I don't have any experience of Sybase so I can't really help I'm afraid. From reading everything above though it seems as if your problem is connectivity to Sybase so the Datareader destination would be of no use even if it did what you evidently were hoping it does. Its clear that the problem is outside SSIS in the OLE DB Provider however I accept your assertion that this always worked in DTS2000 so I can understand your frustration. My first point of attack would be to eliminate SSIS and see if the problem still exists. Try consuming the OLE DB provider from somewhere else but make sure you keep the same connection details and the query that SSIS is using. In other words - try and recreate the problem outside SSIS. I would also try accessing the data via a SQL query in the OLE DB Source component rather than just selecting a table from the drop-down list. These 2 options access the data source in different ways as far as I am aware. -Jamie

Hi, I can access the sybase database using oledb from code without a problem. Both using a sqlcommand and a OleDbDataReaderand using a OleDbDataAdapter. So in my opinion the problem is not in the oledb provider but in ssis. So again the question remains, how are we going to resolve this. Mark

We have not tested with the Sybase drivers, so it is difficult to give detailed information.The error you are seeing is a very genericOLEDB error returned to SSIS, so whatever is causing the problem is in the provider layer. That DTS could connect to the same provider does not give any guidance - SSIS has a completely different architecture, and consequently requests different (and more detailed) metadata from providers. It is therefore quite possible that some providers will return errors from an SSIS request, where DTS made no such call. Where agivenOLEDB provider does not work, there a number of possibilities to explore: Try sending a query, rather than selecting a table or view; Try another OLEDB provider if available - there are often several implementations and some support a wider range of OLEDB properties and methods than others; Try an ADO.Net provider if it is available for use with VS 2005; Try an ODBC provider, used with the ODBC Provider for .Net. Donald

Hi to AllI use Sybase Adaptive Server Anywhere Provider 9.0and I have the same problem of Srinivas Govada (first post)My error ,message is:________________________________TITLE: Microsoft Visual Studio------------------------------ Error at Data Flow Task [OLE DB Source [215]]: An OLE DB error has occurred. Error code: 0x80040E21. Error at Data Flow Task [OLE DB Source [215]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available. ------------------------------ADDITIONAL INFORMATION: Exception from HRESULT: 0xC020204A (Microsoft.SqlServer.DTSPipelineWrap)___________________________________ I've tried to install "ODBC .NET Data Provider"but it wasn't tested with Sybase :( and it doesn't work. when you say>Try an ODBC provider, used with the ODBC Provider for .Net have you and idea of what i can use? Anyone have resolved the problem? Thanks Salmec

Hi to all,thanks for your interesting... i resolved the problem: i choose the "SSIS import and export Wizard" procedure (project menu)At th first step - choose a Data Source - I choose ".Net Framework Data Provider for Odbc" As destination i've a SQL Native Client I write a query to select the column tha i would like to import;I choose the destination tableand after click Finish buttonThis procedure create a new package that import and convert the data. and it works Thanks to all Sal

Glad to know that you solved this problem using a different data source. I have the same problem as Srinivas (the initiator of this thread). Trying to copy from Sybase into SqlServer 2005 using SSIS and Sybase OLEDB provider. For the source, I am using Sybase OLEDB provider that comes with Sybase ASE Client 12.5.4 For Destination, I am using Sql Server 2005 database In Ole Db Source Editor ,For OLe Db Connection Manager, I choose Sybase ConnectionFor Data access mode, I choose Table or ViewFor Name of the table or the view, I choose a table by name Table1( it lists all the tables from Sybase database) When i click on preview button I see the data in the source table But when I execute the package I get: Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E21. Error at Data Flow Task [OLE DB Source [1]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available. ------------------------------ADDITIONAL INFORMATION: Exception from HRESULT: 0xC020204A (Microsoft.SqlServer.DTSPipelineWrap) Now, when I choose ".Net Framework Data Provider for Odbc" instead of Sybase OLEDB Provider, the next dialogue is asking for 3 pieces of information. Data ConnectionString NamedConnectionString Dsn Source Driver What are these? Can you please help me with these entries?

Some additional information. I found out what those 3 pieces of information. I had installed the Sybase ASE ODBC drivers earlier so I went to the Control pabel=>Admin Tools==>Data Source==>System DSN and created a new DSN called SybODBC using this driver. I entered all the pertinent info such as server name, port #, uid, pwd and tested the connection. I got the message Login Succeeded. Whoopie! Then I tried using this DSN in the SqlServer Import Export Wizard by selecting the .Net Data Framework Provider for ODBC as the data source but I got ERROR[HY000][SYBASE][ODBC Sybase Driver]Insufficient information to connect to the data sourceERROR[01S00][SYBASE][ODBC Sybase Driver]Invalid Attribute in connection string: na

Hi Jay87, I'm sorry butI'm not working in this problem right now, but for Connectionstring, you can take some information in this web site: http://www.connectionstrings.com/ Regards Salmec

I had the same issue I got away with ASE driver. I was having the issue with ASA driver. Problem with the Dot net connection is that you cannot have parameters to the SQL Command. Therefore, I had to use OLEDB Data source.

I had the same problem. I have resolved by setting the "AlwaysUseDefaulCodePage" Custom Property of the OLE DB Source control to True. Try this.

I also had the same "Unable to retrieve column information from the data source". I found that Sybase did not like having any columns that were derived from a subquery. The fix was to use a natural join and avoid any derived columns using subqueries. The Sybase driver must not be able to interpret the schema for some reason when you utilize subqueries. AlwaysUseDefaulCodePage did not work for me.

Yes, setting the "AlwaysUseDefaulCodePage" Custom Property of the OLE DB Source control to True solves the problem.

Hi all: I found the answear to above problem. If you use DBF table as source in SSIS Package and table name has more than 7 characters then you will get above problems. Rename the table (DBF table) and keep only 7 characters in the file name, the problem is solved. Thanks Fayaz

I have also found a potential solution/workaround - I had the same error, but I was connecting to a SQL2005 database and using a linked server on it to query a DB2 database running on an AS400.All I had to do was close the SSIS package and re-open it. I didn't even have to close visual studio. Only the package.I have no idea why this worked but I hope this helps someone out there.Carl

Can you point us towards a list of approved+tested providers for the main databases DB2 Oracle etc?

I've been suffering from this problem as well. The database i need to take my data from is Sybase. To get around this issue I used a script transformation as a source and used the following code Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper <Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _ <CLSCompliant( False)> _ Public Class ScriptMain Inherits UserComponent Dim ODBCConnString As String Dim ODBCConn As Odbc.OdbcConnection Dim ODBCReader As Odbc.OdbcDataReader Public Overrides Sub PreExecute() MyBase.PreExecute() Dim Cmd As New Odbc.OdbcCommand() 'Add the Sybase connection to the connetion manager section ODBCConn = New Odbc.OdbcConnection(Me.Connections.Sybase.ConnectionString) ODBCConn.Open() Cmd.Connection = ODBCConn Cmd.CommandText = "{CALL dbo.your_sybase_sproc}" Cmd.CommandType = CommandType.StoredProcedure Cmd.CommandTimeout = 0 ODBCReader = Cmd.ExecuteReader End Sub Public Overrides Sub CreateNewOutputRows() Do While ODBCReader.Read With Output0Buffer .AddRow() 'Need to set up the columns in the input and outputs column If ODBCReader.GetValue(0).ToString = String.Empty Then .column1_IsNull = True Else .column1 = ODBCReader.GetValue(0) End If End With Loop ODBCReader.Close() End Sub End Class