CODEWITHSUNDEEP
sql-serverssisdata-warehousedata-migration
Doodle
Doodle

Reputation: 479

data warehouse load

We are building a DWH and the initial load would be millions of rows(a few tables have like around 300 million rows).

Can you suggest an efficient way of extracting data initially. Is using SQL Server Import and Export a good and faster option ?

Thanks

Upvotes: 0

Views: 236

Answers (2)

Doodle
Doodle

Reputation: 479

I just extracted and loaded 24.5 million rows in 9 minutes from Oracle DB to SQL Server which I found super awesome!!!

Solution : Used Attunity connector for Oracle and change the batch size to whatever suits to you(1000/5000/10000) 1000 worked for me. (default 100)

Upvotes: 1

Brian
Brian

Reputation: 1248

First: the SQL Server Import and Export wizard creates an SSIS "package under the covers".

I recently had to solve the same problem - our Oracle-to-SQL Server replication infrastructure cratered and we had to rebuild it, which involved initial table loads of the same size that you describe. We used SSIS packages for all of them, and the performance was sufficient to complete the task in the window we had available.

Another option to consider would be getting the Oracle data as a flat file export and BCP import, if the Oracle data are clean enough. If you go that route, though, I'm afrad that others will need to assist - I can barely spell "BCP".

Upvotes: 1

Related Questions