Converting data to SQL Server data with the DTS wizard

SQL Server Ask the Experts

2585 pts.

Tags:

DTS

DTS Import/Export Wizard

SQL Server

Sybase

I need to convert my Sybase data to SQL Server data. I tried using several methods for a single table that contains 1357 records. When I used the DTS wizard, the tranfer completed sucessfully but the data in the column with data type "money" was not correctly transferred. For an example, the value 9700.00 was transferred as 97.00. I found that the leading zero of decimal points was supressed. Is there any solution to proceed?

Asked: February 27, 2009 3:41 PM Last updated: February 27, 2009 7:11 PM

Answer Wiki

Last updated: February 27, 2009 4:37 PM GMT

SQL Server and databases165 pts.
History
Contributors
- Ordered by most recent
- SQL Server and databases165 pts.

Thanks. We'll let you know when a new response is added.

Try to transfer the data to a non-existing table in SQL Server and see how the DTS package creates this column in the new table (which datatype).
If the data is then transferred properly, use this “temporary” table to hold the data and insert the data to the real table using an insert statement, in SQL Server , after the transfer (converting that column if required).

Please let me know how it went.

Thanks,
Michelle.

Discuss This Question: 1 Reply

Denny Cherry Feb 27, 2009 7:11 PM GMT

I would assume this has something to do with how the money datatype is displayed on the Sybase system by default. According to what I just read on Sybases Website it is displayed with only two digits after the decimal by default (four are stored). DTS may be doing something funky with this. Try putting the value into a table with the numeric(18,4) datatype instead and see how that works. I would also be interested in knowning what data types DTS uses when it creates the table automatically.

68,390 pointsBadges:

report