SQL Server/Oracle data load-SQL loader

[bonny]

I use to load data from MS-SQL server to Oracle-9i using DTS package on SQL Server.It works fine,but takes more time.

In order to reduce the load time for this tranformation,I loaded data from SQL Server to flat file using DTS package and
from flat file to Oracle using SQL Loader which is much faster than using only DTS package.In few cases it works very well .

BUT THE PROBLEM COMES in the follwoing columns :

sql server oracle
datatype datatype
---------- ----------

datetime date
money number
Int number
bit char
these columns data give the problem for ORA-01722: invalid number when I load from flat file to oracle using sqlloader.THIS PROBLEM DOES NOT COME WHEN I AM USING DTS PACKAGE.

ANY CLUE to resolve this problem...I would appreciate the help.

Thanks

[slimdave]

More info required ... give us the SQL*Loader control file and sample data also please

[tamilselvan]

This may be due to date format in the flat file.
If you use to_date function in the sqlloader control file, you will be ok.

Tamil

[bonny]

Here is content of my control file-

Load data
infile 'c:\temp\feeschedule.txt'
badfile 'c:\temp\feeschedule.bad'
discardfile 'c:\temp\feeschedule.dsc'
append into table odstest.feeschedule
fields terminated by '|'
trailing nullcols
(feescheduleid,
revenue_l,
revenue_h,
anualfee,
lastmodifed "to_char(:lastmodifed,'yyyy-mm-dd')")

But this control file gives error on last line-ORA-01722: invalid number

I would appreciate you help.

Thanks

[jmodic]

What is the datatype of the column LASTMODIFIED in the table ODSTEST.FEESCHEDULE? Also, can you post a sample of your actual datafile you are loading from. Few lines will be sufficient.

I'm suspecting the column's datatype is DATE and the corresponding part in your infile is in format "YYYY-MM-DD". If this is true, then you should change xour controlfile to something like:

Load data
infile 'c:\temp\feeschedule.txt'
badfile 'c:\temp\feeschedule.bad'
discardfile 'c:\temp\feeschedule.dsc'
append into table odstest.feeschedule
fields terminated by '|'
trailing nullcols
(feescheduleid,
revenue_l,
revenue_h,
anualfee,
lastmodifed DATE "yyyy-mm-dd")

[bonny]

Thanks Guys!The problem has been resolved .It was formatting problem and i used the following format in control file-

Load data
infile 'c:\temp\feeschedule.txt'
badfile 'c:\temp\feeschedule.bad'
discardfile 'c:\temp\feeschedule.dsc'
replace into table odstest.feeschedule
fields terminated by '|'
trailing nullcols
(feescheduleid,
revenue_l,
revenue_h,
anualfee,
lastmodifed "to_date(:lastmodifed,'YYYY-MM-DD HH24:MI:SS')")

Thanks for all the help.