Error read data from Sybase with SSIS 2005

6 Jul 2006 8:51 AM

Sal

Hi to All,

I am in process of transfering data from Sybase to Sql Server using
Microsoft Visual Studio 2005
have taken a Data Flow Task in Control Flow tab
In Data flow tab, I have taken one Ole DB Source
I am using Sybase Adaptive Server Anywhere Provider 9.0

In Ole Db Source Editor ,
For OLe Db Connection Manager, I choose Sybase Connection
For Data access mode, I choose Table or View
For 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

________________________________
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.

Could anyone help me please?

Thanks
Salmec

6 Jul 2006 6:43 PM

James Luetkehoelter

First, make sure that you can connect to the Sybase system by setting up a
linked server in SQL 2005 (take SSIS out of the picture). If that fails,
check www.connectionstrings.com to see if you're missing something in the
connection string. I assume you have the sybase client installed on the SSIS
box? It would need to be the same if SQL Server were on a different server.

If you can make that connection and make a simple query of the table, beware
of implicit data type conversions. I've had problems doing just this sort of
thing with Sybase when it came to the "money" datatype. You'd think that
since Sybase and SQL Server have a money type that it would work fine. Um,
no. So if you can get it to work as a linked server, try eliminating fields
(especially those that have some uncommon data type).

Hope that helps a bit.
Show quoteHide quote

"Sal" <sal***@gmail.com> wrote in message
news:1152175894.975209.80120@m73g2000cwd.googlegroups.com...
> Hi to All,
>
> I am in process of transfering data from Sybase to Sql Server using
> Microsoft Visual Studio 2005
> have taken a Data Flow Task in Control Flow tab
> In Data flow tab, I have taken one Ole DB Source
> I am using Sybase Adaptive Server Anywhere Provider 9.0
>
> In Ole Db Source Editor ,
> For OLe Db Connection Manager, I choose Sybase Connection
> For Data access mode, I choose Table or View
> For 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
>
> ________________________________
> 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.
>
> Could anyone help me please?
>
> Thanks
> Salmec
>

10 Jul 2006 8:32 AM

Sal

Hi James,
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 table
and after click Finish button
This procedure create a new package that import and convert the data.

and it works

Thanks to all

Sal

Thread options