Introduction
As we do not have any option to transfer data from an Oracle data source to SQL Server destination in SSIS. This tip will help in creating the attunity connectors to transfer data from Oracle database to SQL Server database.
Background
In one of my projects, I had to migrate data from Oracle data source to SQL Server data source but I didn't find any option in SSIS to have data source as Oracle. After some research, I am able to configure attunity connector to migrate data from Oracle data source to SQL Server data source. So I would like to share this concept and hope it will be helpful.
Using the Code
Firstly, let's discuss about the prerequisites for configuring attunity connectors.
- Attunity connector does not work on all version of BIDS, make sure that you have BIDS 2008 and above.
- An Oracle client is installed and TNS service name is defined.
- The attunity connector does not support all datatypes, below are the datatypes which are supported by it:
| Oracle Database Data Types | SSIS Data Types |
VARCHAR2 | DT_STR |
NVARCHAR2 | DT_WSTR |
NUMBER | DT_R8 |
NUMBER (P,S) | DTNUMERIC(P,S) |
DATE | DT_DBTIMESTAMP |
RAW | DT_BYTES |
CHAR | DT_STR |
TIMESTAMP | DT_STR |
- The attunity connector can be downloaded here.
- Follow the instructions and install the attunity connector. To finish the installation, SSIS must be restarted.
After the installation of connector, the Oracle data source and Oracle data destination should be available in toolbox of BIDS. In case Oracle data source or destination is not present in toolbox, then click on Tools->Choose Toolbox Items and select Oracle data source and destination and add it into Toolbox.
Now you can configure the Oracle data source / destination, enter the TNS service name and Oracle account same as you configure for OLEDb source/destination.
