SSIS Data Compression to Oracle

Question

SSIS Data Compression to Oracle

Ray Laubert

SSCrazy

Points: 2910
More actions
March 5, 2010 at 12:28 pm

#145689

I have a requirement to set up a data transfer between a SQL Server Database in Philadelphia and an Oracle Data Warehouse in Boston. The connection will not be persistant and the requirement calls for compressing the result set to reduce transfer time. The current proposal is to export from SQL via BCP, compress the result file, create a secure FTP connection and transfer the file to a directory in Boston. Then Uncompress the file and BCP it into a temporary database and then load it into the data warehouse.
I would like to use SSIS to manage this process directly into the Oracle temp DB but compress the data for transfer. Initial Database size is 64GB We are looking at 200KB per day of data growth but will grow faster as more clients are brought on board. The data to be extracted is day does have a last modified timestamp to make extraction of required data easy to do.
Can this be done? What additional software if any would be required?
Raymond Laubert
Exceptional DBA of 2009 Finalist
MCT, MCDBA, MCITP:SQL 2005 Admin,
MCSE, OCP:10g

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 1

That is a LOT of steps to make this work, as well as a lot of places for it to go wrong. You are saying 200K/day right now. How much more is it per customer and what is the upper size that you are thinking?

Also the connection is not persisted, ok, but what kind of connection is it? VPN? Please describe the options here.

It is easy to get a .NET assembly to zip up a file of data but I want to look at other options.

CEWII

Ray Laubert SSCrazy Points: 2910 More actions · Answer 2

During some testing with our clients prior to the release we maxed out at 20 GB per day growth. We are now releasing it to US and Canada with another set of servers in Europe. The connection will be VPN, but the underlying network information I dont have.

I am really looking for a SQL Based solution. If we have to go with additional programming development than the proposed solution will stay in place. I was sure that SSIS could handle this process. But I am not that good with SSIS to say for sure.

Raymond Laubert
Exceptional DBA of 2009 Finalist
MCT, MCDBA, MCITP:SQL 2005 Admin,
MCSE, OCP:10g

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 3

I'm sure SSIS could do it, but I'm thinking that you could use transactional replication to Oracle.

Look here:

http://www.sql-server-performance.com/articles/dba/peer-to-peer_replication_p1.aspx

Just thinking about the method you have outlined and it seems to have at least 5 parts:

1. Extraction.

2. Compress.

3. Move.

4. Uncompress.

5. Apply.

All of which you have to handle. Although I'm not a big fan of replication I think it is a consideration.. You can accomplish what you want using the method you have outlined, but I'm thinking something simpler is better.

CEWII

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 4

We did something similar once. It was a huge amount of data, to boot. The process was relatively slow, the FTP repository needed to be maintained, etc, etc, etc. People kept trying to figure out clever ways to speed up the process and data transfers but to no avail. Then, I found a fix...

Wrote the data to one of 3 portable harddrives in a "swing set" and sent it by overnight FedEx. Problem solved.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)