September 23, 2004 at 7:39 am
Wondering what file type is best to use for a regular export from Oracle and import to SQL Server. - tab delimitted, comma delimited, fixed length or??? Or does it matter?
Guess that I would use a Bulk Insert Task to load data in a temporary table so that I could delete existing data using the same week numbers as appeared in the incoming file.
Never used Bulk Insert - am I right in saying it does not affect the transaction log. And if I truncate the temporary table after processing I guess it will keep the backup small.
Above is my second choice, I am hoping that (but with not very high expectations) that my colleagues will allow me to link direct to our Core Oracle databases. In that case guess I would just create an Oracle ODBC connection. Are there any issues I should watch out for when connecting to Oracle this way?
September 23, 2004 at 11:05 am
An ODBC connection to Oracle would certainly make the DTS package simpler. You wouldn't have to worry about adequate disk space for the intermediate text files and it would eliminate a couple of steps from the package. I've used this method and the only problems I've had were trying to translate Oracle's LONG datatype to SQL Server's VARCHAR.
If you must use text files, I don't think the format matters much. Be aware that a package containing a Bulk Insert task can only be executed by a member of sysadmin. Also, the task doesn't automatically roll back loaded batches if it fails before finishing, so you'll either have to handle that in the package or delete them manually before restarting. If those are concerns for you, use a Transform Data task. It's a bit slower than Bulk Insert, but it's more flexible.
Greg
Greg
September 24, 2004 at 9:21 am
ODBC Connection
It is unusual for our Oracle DBA's to allow any external access but they are under quite a bit of pressure to supply me with the data and a lot of other priorities. There are signs they might exceptionally agree to an ODBC connection to get me off their back.
Can I create a connection in a DTS package just like to Access, Excel etc or will I need to install any special drivers? (If need be would like to get this in place while I am waiting)
In DTS can I create a query against Oracle based on an SQL Server table e.g. only retrieve customers addresses from Oracle for customer numbers in the SQL Server table. Or do I have to pull all the data across and do the filtering later.
Flat Files
I experimented with the manually created files.
I found that I was unable to use Bulk Insert unless I stripped out the file header and footer and the column headers using wordpad, then the file came in OK. As I would be able to specify the file format for the automated extract this would not be an issue.
I found that Bulk insert was faster 15 secs without index, 23 secs with. Data Transform was actually faster with an index 33 secs than without 35 secs??!!
Bulk Insert seems much more complicated to use than Data Transform and with an index, the differences are not that great so you can probably guess which I will use.
September 24, 2004 at 10:55 am
You can create a connection using the MS ODBC driver for Oracle, the MS OLE DB driver for Oracle, or Oracle's own ODBC or OLE DB drivers. I used the MS ODBC driver when I was migrating from Oracle v7. There's a recent article on this site comparing the various drivers: http://www.sqlservercentral.com/columnists/hji/comparisonoforacledrivers.asp
You should be able to use a cross-platform query in a Transform Data task once you have the connections set up.
Greg
Greg
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply