Importing Oracle files into SQL Server 2000

[nimarii]

I'm a total newbie at this, so any advice or suggestions are more than welcome.

I need to create a SQL Server db that imports files from an Oracle db. We receive these files in .DAT form every month, that are zipped.

Does anyone know the best way to import this data? I can open the .DAT files in Excel, but the files are so large, that I end up missing data.

Also, I've run into a problem with Oracle's timestamp field, which is binary data. How can I convert this timestamp data into SQL Server's datetime data?

thanks soo so much...

 

[jbenson001]

I do not belive a DTS package will recognize this type of file. Would it be possible to have a linked server connection to the oracle DB and just DTS the data over?

I do not know about Oracle timestamp columns, SQL servers are binary also and do NOT represent a date time format. I don't belive that you would be able to convert the timestamp to a datetime datatype.

 

[mrdenny]

What form is the data in. Is it comma separated? Try to open the file in notepad. If it opens then you can easily import it via DTS, or BCP.

Oracles timestamp field will need to be stored in a varbinary field. It's not something you can change into a real date and time. It's used for internal oracle processes to find out how many times the record has been updated. SQL Server has the same kind of field also called timestamp.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[nimarii]

thanks for the replies - have you heard of adding the SET DATEFORMAT command to the SQL Script and then using the TO_CHAR(field_name, 'MM/DD/YYYY HH:MI:SS PM') to convert it to the sql server format?

thanks!

 

[mrdenny]

using the SET DATEFORMAT command will change the way that sql shows you dates and times.

TO_CHAR is an Oracle command. I believe that the field you are talking about has an Oracle data type of timestamp. To use the TO_CHAR method you referred to you'll need to use an oracle column with a datatype of date (I think).

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)