transfer data from Sybase to SQL

Question

transfer data from Sybase to SQL

pri_sql

SSC-Addicted

Points: 468
More actions
March 3, 2011 at 1:57 pm

#157469

I need to transfer data from Sybase to SQL Server 2008. What is the best way to migrate the data. I have used SSIS and got errors in datatypes mismatch.Please help.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

I have participated in a migration from Sybase to SQL Server...I was handed specific requirements to cleanse and standardize the data during the migration. I evaluated several options including SSIS but using Sybase-BCP to generate a flat-file from Sybase, working on the data file with C# code and then importing it using SQL Server-BCP ended up being what was used. Using SSIS for the entire process would probably have been cleaner and much faster however I had some specific requirements around address standardization and the 3rd party tool we were using for that was not usable within an SSIS component...it was a batch program that only handled flat-files.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 2

Try Microsoft's tool, SSMA, I've used it for several migrations without issues.

http://www.microsoft.com/sqlserver/2005/en/us/migration-sybase.aspx

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

pri_sql SSC-Addicted Points: 468 More actions · Answer 3

I tried importing data using the tool but it didn't work in my case. It is throwing an unknown error. I have billion of rows in my table. Data is huge. BCP didn't work too bacause of the huge data. Please let me know if there is any other way to transfer data from Sybase to SQL 2008. Please help me. Thanks for your help, much appriciated.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

I have used bcp to transfer tables with more than a billion rows. What is the error message you are receiving?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

pri_sql SSC-Addicted Points: 468 More actions · Answer 5

Do you have the command to export data into CSV file from Sybase? Rapid SQL is slow and I got out of memory error while exporting data into SQL. Please help me. I am stuck.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

Just a thought, in case you were not aware, the instance of bcp.exe that is part of SQL Server client tools will not work against a Sybase instance so you have to ensure that you are running the instance of bcp that belongs to the Sybase client tools. When you just type "bcp" at a command line you may get the SQL Server bcp depending on the order in which you installed the SQL Server and Sybase client tools.

Since there are two on your machine now, I would suggest fully-qualifying the path to the bcp executable you want to call each time (i.e. don't just start your command with bcp, say C:\Sybase_Client_Directory\blah\blah\bcp.exe or C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe) to ensure you're calling the correct one for whatever you're doing.

I am confident you can easily find the command line help doc for Sybase bcp online.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

pri_sql SSC-Addicted Points: 468 More actions · Answer 7

Thank you so much for your help!!

I have created the csv file from Sybase but now getting error in importing into SQL Server.

I tried DTS/SSIS/BCP command in SQL Server but no luck. CSV file contains string datatype for all the columns. However, All the columns have different datatyes in table (i.e text, datetime, int). Please suggest how to convert the datatypes from string to int/text/datetime.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

SQL Server will implicitly convert your data in the text file to the destination data type per the table definition. That said, if you exported some data from Sybase and the formatting is not what SQL Server can handle, this is especially important for dates, you may need to craft a select statement to issue to Sybase that will output dates in a format that SQL Server will accepting.

Without seeing your DDL or data file it is hard to tell.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

sateswar_sahu SSC Enthusiast Points: 161 More actions · Answer 9

Hi All,

I've an application, which uses SYSBASE as back end. Now I want to use SQL Server 2008 and want to transfer entire database to MS SQL 2008.

I know how to build SSIS Package, but couldn't find provider for SYBASE under OLE DB or ADO.NET Source Editor->Configure OLE DB Connection Manager/Configure ADO.NET Connection Manager->Connection Manager->Provider.

Does anybody have solution for this?

Thanks in advance!

Sat

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 10

sateswar_sahu (6/16/2011)
Hi All,
I've an application, which uses SYSBASE as back end. Now I want to use SQL Server 2008 and want to transfer entire database to MS SQL 2008.
I know how to build SSIS Package, but couldn't find provider for SYBASE under OLE DB or ADO.NET Source Editor->Configure OLE DB Connection Manager/Configure ADO.NET Connection Manager->Connection Manager->Provider.
Does anybody have solution for this?
Thanks in advance!
Sat

Start a new thread using the "ADD TOPIC" button.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato