May 21, 2007 at 7:30 am
Hello,
I have to extract lots of data from a Database ORACLE and load them into SQL Server 2005.
SSIS is the best way to do it?, or it is better using traditional tools like BCP, Bulk Insert, or use some special ORACLE Tools?
At the moment is not clear if the ORACLE source will be the transactional database or Datawarehouse database.
SQL server 2005 will be the transactional database in any case.
Any Help will be very appreciated
Thank
May 21, 2007 at 9:00 am
Hi,
Indeed SSIS would be a very good tool for the purpose, especially when you have a large amount of data to transfer. BCP and Bulk Insert are only used if a text files involved, hence you won't be able to use it. As of for Oracle tools, I am not aware since I am not essentially an Oracle person. But I'm sure there'll be someone who will be able to answer that.
If it was me, I'd go with SSIS
Regards
Gogula
May 21, 2007 at 9:10 am
Hello,
thank for replying.
It is possible to extract data from ORACLe into bcp files and then uploaded.
Any further contribution will be very appreciated.
Thank
May 21, 2007 at 4:43 pm
Are you asking if you can extract from oracle then load using BCP commands? Sure...but why? Unless you don't have access to the Oracle servers I guess...
ORacle has a tool SQL*Loader that does stuff like this but since you're loading INTO SQL Server I would recommend that using SSIS. FYI there are BCP tasks in SSIS.
Hope this Helps,
Ben
Ben Sullins
bensullins.com
Beer is my primary key...
May 22, 2007 at 5:43 am
Hello,
I would like to say that there is not corrispondence between Oracle and SQL server table and fields.
This could be limit the use of SSIS 2005?
Thank
May 22, 2007 at 10:28 pm
Hi,
Could you elaborate more on what you mean by that there is no correspondence between Oracle and SQL Server tables and fields?
Regards
May 23, 2007 at 12:53 am
I mean that ORACLE database table structure and SQL Server 2005 Database table structure is different, eg (just as example).:
ORACLE has 3 tables with 4 cols each one and SQL server 2K5 5 tables with 5 cols, and not all the cols from ORACLE db are to be imported.
Thank
May 23, 2007 at 1:02 am
Hi,
SSIS is a fully fledged ETL tool, and as with DTS, you need not worry about the table structure and number of fields not being the same (or even the data types). You can specifically choose what fields need to be imported, what records need to be imported, and what data types that they are to be converted to, among many other things. Your concern will not be a limitation of SSIS.
Regards
May 23, 2007 at 7:56 am
Thank for your useful information
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply