Configuring Attunity Connector to Bulk Insert Data to Oracle using SSIS
Microsoft Connector for Oracle by Attunity offers high speed connectors for Oracle to bulk insert data from SSIS Packages. The connectors can be downloaded at:
Attunity Connector v1.0:
https://www.microsoft.com/downloads/details.aspx?FamilyId=D9CB21FE-32E9-4D34-A381-6F9231D84F1E&displaylang=en
Attunity Connector v1.1:
Below is the Whitepaper that discusses in detail about the components included with this connector that can be used in SSIS packages:
https://msdn.microsoft.com/en-us/library/ee470675.aspx
Once the connector installs successfully, we should be able to add the required Source and Destination Data Flow items by customizing the toolbox items:
In my SSIS Package I used the Oracle Destination component to insert data in the Oracle Database and I wanted this write up to summarize a few things we need to have under consideration:
If we generate the SSIS Package using the Import Export Wizard, we need to replace the OLE DB Destination with the Oracle Destination component that comes with the Connector: (The Connectors are specific to the component it applies to and won’t be listed in the common data connection wizards like Import/Export Wizard or New Connection Manager Wizard)
Since I had columns that have Date/Time and Integer Data types I had to add a Data Conversion task since Oracle Destination does not support those Data Types. I have to cast Date/Time types to DT_STR and Integer types to DT_NUMERIC in the Data Conversion task:
If we don’t cast the data types appropriately we will get validation errors similar to the following:
Error 1 Validation error. Data Flow Task: Data Flow Task: Datatype conversion of input column "<Column_Name>" (67) is not supported. OracleAttunityDemo.dtsx 0 0
From the Attunity Support Forums < https://www.attunity.com/forums/microsoft-ssis-oracle-connector/what-data-type-ms-ssis-oracle-1382.html\>
“The Oracle Destination component doesn't support mapping input column of type different than the destination column type.
If you are using the Convert transformation to convert the problematic column to DT_STR, then change it to convert the column to the destination column data-type. To check what the destination column type is, edit the Destination component and, in the mapping page point on the specific column, a tool-tip will show the type information.” Below is the screenshot of the tool tip that the Oracle Destination shows to identify the proper column type:
We can confirm the Destination types and apply proper casting in the Data Conversion task.
Lastly, I had a few columns in the source Sql Server table which exceed 30 characters and since Oracle does not accept column names>30 characters we had to apply additional transformation to rename the columns to less than 30 characters. We will need to manually map the columns in the Oracle Destination as the source and destination columns do not match any more. This will also apply for any columns to which we have applied data type conversions (like Date/Time and Integer) as there also, after the Data conversion, column names get changed resulting in a manual mapping of those columns.
Author : Debarchan (MSFT), SQL Developer Engineer , Microsoft
Reviewed by : Jason(MSFT), SQL Escalation Services, Microsoft