Oracle 2 SQL 2000
-
I am using DTS packages to import data from an oracle v10 database to an SQL 2000 database.
In the DTS package I use an SQL query that looks like this :
SELECT * from <tablename> or instead of * I use fieldnames
My problem is that I can't get the date fields imported in SQL. Which statement should I use for this ??
-
Hi
Have you tried importing the date fields into a varchar?
Regards
Mike
-
Ofcourse I tried that
and ofcourse it works ... but that's not what I want as a result. The field must be a datetime field ! 
After the import I link the tables in Microsoft Access and that's why the fields must be datetime because in Access datetime statements are used ...
-
why can't you import into SQL , and after the import is completed alter the column definition to be a datetime field, or add datetime columns and migrate the data to the new columns?
then the new columns could be the columns you send to MS Access, instead of the varchar originals.
I usually migrate data from oracle to staging tables exactly because of this issue, and then move the data from staging tables to their final destination.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Hi
I do not have a Oracle server to test this on...But I am guessing you are using a Transform Data Task to get the data into your DB....Can you not go to the transformation and delete the one that is the date...Then re-add it set it as an ActiveX transformation then format the date so that it is in the correct format?
Mike
-
That is exactly what I did and that didn't work !
But ... I'm a little new to SQL and ActiveX is also new for me so maybe I'm not using the right VB commands. I let VB autogen the command for me but that doesn't work ?? -
Use an sql statement with the openquery function, something like:
insert mySQLServer table select ..., convert(datetime, myvarchardate)
from openquery(myOracleServer, 'select ..., munge(cast myOracledate as varchar) as myvarchardate from myOracleTable')
...where munge() is the oracle substr(), etc., to get the date into a character representation that sql server recognizes, i.e. 'yyyy-mm-dd hh:mm:ss'
-
I suspect that the problem might be that in Oracle the minimum allowed date is 1753-01-01, whereas in SQL Server it is 1900-01-01. When the DTS tries to load that record, SQL Server sees it as an invalid date and throws an error. When you specify the destination field as Varchar, SQL Server just views it as text and doesn't check to see if is a valid date as far as SQL server is concerned.
You may be able to add an ActiveX script in your Data Pump Task to check for that and convert the year to 1900. Better still, load the data into a staging table first where the date field type is Varchar, and then use a stored proc to check and adjust the date to make it SQL Server valid, and then finally load it into your production table which has the field as a type Datetime.
-
I'm now using an ActiveX script like this :
If IsDate(DTSSource("T$LVDT")) Then
If DateValue(DTSSource("T$LVDT")) < DateValue("01-01-1753") Then
DTSDestination("Verificatiedatum") = DateValue("01-01-1753")
Else
DTSDestination("Verificatiedatum") = DTSSource("T$LVDT")
End If
Else
DTSDestination("Verificatiedatum") = NULL
End If
and it works !!! But ... it is much slower ?? Any suggestions to speed this one up ??
-
In oracle, if you want to convert the date to character format, you don't use convert. In the SQL task, you have to convert the date to character format,
the syntax is
TO_CHAR(sysdate, 'MM/DD/YYYY') it would return '09/29/2006'
-
That's correct, I'm using
TO_CHAR("T$DDAT", 'mm-dd-yyyy') AS "T$DDAT"
in my SQL query, and
If IsDate(DTSSource("T$DDAT")) Then
If DateValue(DTSSource("T$DDAT")) < DateValue("01-01-1753") Then
DTSDestination("Leverdatum") = DateValue("01-01-1753")
Else
DTSDestination("Leverdatum") = DTSSource("T$DDAT")
End If
Else
DTSDestination("Leverdatum") = NULL
End If
in my ActiveX script.
The above is working, but not as quick as I would like it to be !
-
It would be faster to use the Staging table approach. While the ActiveX script approach works, it slows your transfer speed down. By using a staging table where the field type is varchar, a stored proc to fix the dates, and finally to move the data into a production table where the field type is datetime, the DTS will complete much faster.
-
Can you explain in detail what you mean with staging table ?
-
Sure. Create a table in SQL Server with all the fields that you are pulling from Oracle. We'll name the table "Staging" for now. In that table, make the fields that are date related as varchar fields.
Next, create a table in SQL Server with all the same fields that you created in Staging. We'll name this table "Production" for now. In that table, make the fields that are date related as datetime fields.
In your DTS package, the first thing is to create a Execute SQL Task with the command:
truncate table Staging
Next step, have your Data Pump Task load the Oracle data into the "Staging" table. Once that has completed, The third step is to use a Execute SQL Query task (pointing to your destination database, of course) with an update statement that says:
update Staging
set DateField = '1900-01-01'
where DateField = '1753-01-01'
go
You could do additional data scrubbing at this point too.
That will correct the dates so that SQL will accept them and is much faster than ActiveX. Finally the last step, (and I'm assuming you are refreshing the entire table each time) delete the existing data from the Production table and move the data from the Staging table into it, like so:
truncate table production
go
insert Production (Field1, Field2,...)
select Field1, Field2,... from Staging
go
You could use CAST or CONVERT on the date fields if you like, but so long as the dates are valid, SQL will implicitly convert them to datetime, as the destination field is of datetime type. Anyway, SQL should accept the dates as valid datetime values.
The bonus is that if you had deleted the data from the Production table first, tried to copy the data from Oracle into it, and that task failed for some reason (Oracle server offline, network probs, etc), you would end up with an empty Production table. Generally not good. With the Staging table approach, if the data transfer failed for some reason, your existing Production data would be un-affected. It is very unlikely that a DELETE and INSERT would fail if both Staging and Production tables are in the same DB.
Hope this helps.
-
I don't know about Oracle date ranges, but the difference between '1753-01-01' and '1900-01-01' is between the minimum value for a SQL Server datetime datatype (8 bytes with resolution to 300 ms) vs. the minimum value for a SQL Server smalldatetime datatype (4 bytes with resolution to 1 minute). I would bet that this is not the source of your problem. More likely is that Oracle's character representation is different from SQL Server's (at least if time is included) so must be converted even if translated to varchar.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply