Resolving Dates between Fox Pro and SQL
-
I'm trying to import from Fox Pro tables and every date field fails to import to SQL. I can import the date field as a char(12) field, but that obviates the value of the date in future reporting. I've looked at the choices I have in the DTS Wizard and nothing comes close to working.
-
You have a few options,
1) define a seperate transformation for that column. DTS has a builtin datetime transformation task.
2) perform an ActiveXScript transformation for that column. This would be necessary if data conversion doesn't work with the previous method.
3) import the data into a staging table, with the date as text. Then run a stored procedure to insert data into the final table doing conversions as necessary.
Keep in mind that either of the first two options will perform the data conversion on a row-by-row basis. This can have an impact on the data import performance if you have a lot of data to import.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
Yeah, Phil, it helps... a little.
I've tried the staging table already, using MS Access as an intermediate step. I'm kind of glad you suggested it, because I thought I was really dumb for having to do it that way.
I'm moving upwards of 100,000 records at a time, so this option seems to be the best. I just wish there was a more, uh, elegant solution.
Thanks for the info!
-
Why are you using Access as the intermediate step? Can't you create a staging table in the SQL Server database?
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface -
PS: I love your tag line! It's a hoot!
-
Well, yeah, now that I know its the recommended method. We'll go straight to SQL. Access handles the dates from Fox Pro natively, so it was an easy move.
I can do it either way.
-
Well having the staging table on SQL Server will give you a "more elegant" solution than using Access.
For reference, importing data into staging tables, then performing data transformations whilst updating the production tables, is almost standard technique for most datawarehouse loads, regardless of the platform.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply