How to import data from sybase to sql2005 using ssis

Question

How to import data from sybase to sql2005 using ssis

r_prasanna82

SSC-Addicted

Points: 456
More actions
June 22, 2009 at 1:55 pm

#139190

Hi All
I am trying to import data from sybase to sql2005 by using ssis. But while establishing the source connection as oledb for sybase i am facing error like login time expired. I tried with the odbc in new connection but the table from sybase is not fetching.
Moreoevr it shows an error .ids file was not created in the \sybase\oledb\ folder.
Please advise how to import the data by using ssis from sybase to sql2005.
Thanks in advance

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

So when you tried the ODBC connection, did you already have an ODBC data source configured to your Sybase DB? What happens if you try to connect to the Sybase DB from Data Sources component outside of SSIS?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

r_prasanna82 SSC-Addicted Points: 456 More actions · Answer 2

Hi

I created a new odbc connection with the sybase server name and database name with port number.

Actually i am able to be establish the odbc connection without any issues. But only when i try connecting the sybase through oledb provider i ma getting the error. Moreover i tried creating the linked server but no luck.

I checked the sybase port and user access it is fine.

Is there any steps to import data from a particular database table from sybase to sql 2005? If so please let me know.

I am trying through ssis.

Your help is highly appreciated.

Thanks

r_prasanna82 SSC-Addicted Points: 456 More actions · Answer 3

i forgot to add one more point i tried importing the sybase data to sql 2005 by using dts it worked. Whereas i am not able to do the same by using ssis. Please advise.

John Rowan SSC Guru Points: 56440 More actions · Answer 4

So if you set up an OLE DB connection in SSIS and use the 'Test Connection' button, does it connect?

ODBC may work, but you may find it to be a bit slow if you are planning on loading large volumes. Some of how you do this may depend on if you are planning to create something that gets used on a scheduled basis or if you are just doing a one-time data transfer.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

r_prasanna82 SSC-Addicted Points: 456 More actions · Answer 5

Hi

It was my mistake. The sybase port is not the default one. It is a differnt port hence i got this issue. Now i changed the port and it worked fine. I never connected the sybase before that was the issue.

Thanks for your reply.

Moreover we are going to do this task in a regular basis hence as you mentioned odbc will be slow. Now through oledb it works fine.

Thanks once again.

Moreover i got one more clarification. When i enable quoted identifier in the configuration manager my package shows warning. The reason why i am enabling the quoted identifier is i got the error

"Error at Data Flow Task [OLE DB Source [1]]: Opening a rowset for ""dbo"."Table1"" failed. Check that the object exists in the database"

Once i enable the quoted identifier i was able to map the package and i was successful running it.

So may know the reason behind this and what is that i can do to avoid the warning.

Thanks

John Rowan SSC Guru Points: 56440 More actions · Answer 6

Where is it getting the table name from? It looks like you've got double quotes in the table name.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

r_prasanna82 SSC-Addicted Points: 456 More actions · Answer 7

No. I checke dthe tables there is no codes in it. But whereas when i click the drop down from the list of tables in the sybase db from oledb provider for sybase i get this tables with " codes.

But when i checked in the sybase machine directly they are fine, those tables does not have " codes.

The exact step what i am trying to do here is importing data from sybase to sql2005 using ssis.

I created a data source using configuration manager and in SSIS i selected Sybase oledb ase provider and in connection i selected the datasource i created in the oledb. Now i am getting the list of tables in that particular sybase db. While getting that list of tables i see this error.

"Error at Data Flow Task [OLE DB Source [1]]: Opening a rowset for ""dbo"."Table1"" failed. Check that the object exists in the database"

As i mentioned earlier when i checked the sybase machine directly there is no " codes for those tables.

Please advise

Thanks