Product Evaluation – SSIS Attunity Adaptor

Description – A plugin for Integration Services which allows us to move data between SQL and Oracle

Currently we use  an OLEDB Provider by Oracle.

Prerequisties

The Oracle client software must be installed on the computer running SQL Server Integration Services (SSIS)

Installation

Attunity can be downloaded from here: http://www.microsoft.com/en-us/download/details.aspx?id=29284

The SSIS service should be restarted once Attunity is installed

The following Data Flow Items should be added to Visual Studio

  • Oracle Source
  • Oracle Destination

Data Source Type when connecting to Oracle is MSORA

The connection manager configuration when connecting to database is

TNS Service Name:   ip:port/servicename

An installation guide can be found here: http://msdn.microsoft.com/en-us/library/ee470675(v=sql.100).aspx

Performance

I have our largest 900k row table to test the product

Using our current method of data import I can import the table in 2 and a half minutes.

Using Attunity with different batch sizes yields different results

Tool Batch Size test 1 (secs) test 2 (secs) test 3 (secs) avg (secs)
Attunity 1000 93 80 82 85
Attunity 10000 62 68 66 65
Attunity 50000 76 62 62 66
Attunity 100000 65 80 68 71
OLE DB Driver NA 147 148 147 148

Test Conclusion: The fastest attunity batch size appears to be around the 10000 mark and is more than twice as fast as the currently employed method.

The following blog post tells us that we can achieve even faster results if we convert data to string when we extract data and then convert it back to native type when importing however this was not evident in testing where average speed was around 120 seconds.

http://blogs.msdn.com/b/sqlperf/archive/2009/08/14/using-ssis-to-get-data-out-of-oracle-a-big-surprise.aspx

Price: Free

Conclusion: We should start using this tool immediately on our larger tables where the time savings will be noticeable. Smaller tables will see only negligible saving and so should stay in the current import package. Utilising SSIS means that we lose flexibility provided by the current TSQL solution (for example adding new tables would mean opening up the package making changes and then uploading the new file as opposed to simply adding a new record to the import table) however the time saving and the fact that we can run tables imports in parallel means that this is a price worth paying.

Possible other Benefits: This tool allows us to upload data to Oracle databases, we could use it to transfer data from text files, excel files, SQL/Postgres/Access database directly into Oracle.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: