Importing from Oracle 10g

I am trying to import a variety of data from an Oracle 10g database. When I import the data using the OLE DB it takes a long time (100K records an hour).
How can I improve the throughput of my import?
Is there a better driver I could be using?
Any input will be appreciated.
db55

Some interesting reading for you:

http://www.sqljunkies.com/WebLog/donald_farmer/archive/2005/03/13/8819.aspx

http://microsoftdw.blogspot.com/2005/11/how-to-load-oracle-with-ssisfast.html

-Jamie

|||The second link gives another link to a 3rd party solution that could work. Thank you for your response.|||

Please create a ADO.NET Connection manager and use Data Reader Source..there will be a sure Improvement, over oledb.

Is the Data transferred over the Congested network. This might also reduce the performance.

Please let me know the increase of the performnce, once you use Ado.net Connection manager.

Importing Excel Into MS SQL Table

Hi.

I have done this successfully the last time but just couldn't get it to work this time round. Please help.

I was trying to import data from an Excel file into MS SQL table. NONE of the numeric or value fields (right justified) works because they shown <NULL> in MS SQL table after the import via DTS. Those string fields were able to import ok into the table. DTS didn't show any error message during the DTS run.

Any advise? Thank you.

Best regards
Teck BoonDo the values in the cells in the Excel-sheet have a ' in front of them?

This character tells Excel that it should leave the value in the cell alone, and when they are imported they are considered to be text.|||Hello oneleg_theone.

Thanks for your email reply. There isn't a ' in the cell. The problem was there were many other unwanted text at some point in the file. After removing them, I saved the file as a .csv file everything works. I was able to import into my SQL table using DTS.

Best regards
Teck Boon|||I don't know if this applies to this situation or not, but wanted to throw it out there as an FYI. Using DTS to import Excel, DTS will look at the first 8 rows (I think it's 8 rows anyways) of Excel data to look for certain characteristics, and base how it will do the import on that. I know that number can be changed, possibly even ignored. However, it's been several years since Ive had to deal with this, so my apologies for not being able to recall how to go about it.

importing excel column with multiple values separated by '/'

I would like to import two columns from an excel file into a sql server
table as an area code - time zone look up.
The area code column sometimes has multiple area codes in the area code
cell. eg. 207/208/209.
What is a good way to import those two columns so that 3 table rows are
created for each of those Excel rows that contain these multiple values
separated by the '/' character?
Thank you,
GregOn Wed, 1 Mar 2006 07:32:52 -0800, hazz wrote:

>I would like to import two columns from an excel file into a sql server
>table as an area code - time zone look up.
>The area code column sometimes has multiple area codes in the area code
>cell. eg. 207/208/209.
>What is a good way to import those two columns so that 3 table rows are
>created for each of those Excel rows that contain these multiple values
>separated by the '/' character?
Hi Greg,
Some useful techniques are disccussed at
http://www.sommarskog.se/arrays-in-sql.html
Hugo Kornelis, SQL Server MVP|||Thank you Hugo, I'll take a look !
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:spdc0210aq8daomldhm1lmbpviqf7hp4gi@.
4ax.com...
> On Wed, 1 Mar 2006 07:32:52 -0800, hazz wrote:
>
> Hi Greg,
> Some useful techniques are disccussed at
> http://www.sommarskog.se/arrays-in-sql.html
> --
> Hugo Kornelis, SQL Server MVP

Importing Excel 2007 and/ or DBF files into SQl server 2005

Greetings,

I'm having a tough time importing some of my legacy database into sql.
I have a number of dbase (IV) files I need to get into SQL. I have tried building a SSIS package with either an foxpro oledb connection or a jet 4.0 one, none of them work bec. of inconsistencies in the data format in my tables (e.g. date fields, etc).

I have tried to save the .dbfs as excel 2007 files, taking advantage of the larger space that comes with '07. Problem is you can't use the import/export wizard with 2007 for some reason and I haven't been able to create a package with the access 12 oledb as I have read.

I have to get some crucial data out of that old system and into the new one and I can't seem to be able to import them properly.

Any hints on what I should do ? (maybe I'm doing something awfully wrong)

Thank you for taking the time to answer my question,
Val

If you have SP2 installed, you should be able to load data from Excel 2007 format using the I/E wizard. Do not use the Excel connection, but use the new OLE DB provider for Office 2007. You will need to set extended properties to "Excel 12.0".

Let me know if you need more assistance.

Thanks.

|||

Try by looking at this : http://msdn2.microsoft.com/en-us/library/aa337084.aspx and also you'll need to configure your connection manually to connect. Set up a Jet OLEDB Connection - point to your folder containing the DBase files. Click the "All" button and change the "Extended Properties" to "DBASE IV".

importing data from excel to SQL server real time

hi,
how can i take data from an Excel spread sheet and populate
a SQL-server database . I have 4-5 spread sheets which are
constantly updated real time. All updates,deletes,inserts in Excel
must be reflected in SQL server concurrently.
thanks
aparThe equivalent of SQL Server trigger in Excel can be handled with Worksheet_Change() event. So, write your vba to connect to SQL Server and update the changes.