Using the Attunity Oracle Connector with Biml
We'll start the walkthrough with a description of how to setup a free Oracle database on Amazon EC2, how to configure your development machine to use the Attunity Oracle Connector, and then how to use Biml to connect everything together. If you already have an Oracle database or your developer environment configured, just skip to the relevant sections.
Setting up a free Oracle database
Oracle offers a free version of its database call Oracle XE. While it is a bit limited in features and size, it is more than sufficient for our purposes. Let's set it up.
Amazon EC2
If you don't already have an Amazon EC2 account, you should create one now. They offer a free usage tier that allows you to run one "micro" virtual machine instance 24/7. If you need more, you pay per hour of virtual machine time consumed plus piecemeal storage and other costs. It's a great way to create an Oracle XE dev server that you can share with other members of your team. For more information about EC2 and to setup an account, visit the link below: https://aws.amazon.com/
Oracle 11g XE Installation
Once you have an EC2 account, install Oracle XE on a new virtual machine following Oracle's official Linux installation guide. The setup typically takes about 30 minutes.
Installing sample database schemas
After Oracle 11g XE is installed, you can install a sample schema that will be used in our sample package. The Oracle docs describe the sample schema and its creation process in full: Sample Schema Scripts and Object Descriptions. In summary, open sqlplus, connect to the server using sys as sysdba, and then follow the instructions after run this command:
@?/demo/schema/human_resources/hr_main.sql
Setting up your developer environment
Installing the connectors
First you need to install the connectors. Select the appropriate link for the version of SQL Server you are using. NOTE: If you want to be able to use the components from the BIDS/SSDT designer, you must install the x86 version of the connectors. You can also install the x64 version for runtime, but BIDS/SSDT is a 32-bit only application that must have the 32-bit components.
SQL Server 2008 (R2): Microsoft® Connectors v1.2 for Oracle and Teradata
SQL Server 2012: Microsoft® Connectors v2.0 for Oracle and Teradata
Installing the Oracle Data Access Components (ODAC)
If you haven't already, you will also need to install Oracle Data Access Components (ODAC) for Windows. That link provides several versions of the components, in case you need xcopy deployment support or other options. Again, be sure to install the 32-bit and 64-bit versions of the included Oracle OLEDB provider if you want full BIDS/SSDT support.
Using the Attunity Oracle connector with Biml
Now that you have an Oracle database and your developer environment setup, we can finally get to the task of using Biml to create packages with the Attunity Oracle connector.
Update BimlEngine.dll
The connector requires a current BimlEngine.dll. Install or update BimlExpress, which ships with a recent BimlEngine, or replace BimlEngine.dll in your BimlExpress installation with the version that matches your scenario. Depending on your system settings, you may need to right-click the BimlEngine.dll file, click Properties, and then click Unblock to ensure that it is not prevented from loading.
The Biml
At long last! The following Biml will generate a package that extracts all data from the employee table in the Oracle HR schema, and writes it to a table on a local database. Note that you do have to fill out the column metadata by hand. If you need help figuring out the types, you can get them from looking at the DTSX for an appropriately configured connector component in SSDT.
Recent BimlEngine releases include built-in support for the connectors so that custom connection and component syntax (along with the manual metadata) is generally not required.
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<CustomSsisConnection Name="Oracle Connector 1" CreationName="MSORA" ObjectData="<OracleXMLPackage>
 <OraConnectionString>SERVER=XXXX;USERNAME=username;ORACLEHOME=;ORACLEHOME64=;WINAUTH=0</OraConnectionString>
 <OraRetain>False</OraRetain>
 <OraInitialCatalog></OraInitialCatalog>
 <OraServerName>your-oracle-server.example.com</OraServerName>
 <OraUserName>username</OraUserName>
 <OraOracleHome></OraOracleHome>
 <OraOracleHome64></OraOracleHome64>
 <OraWinAuthentication>False</OraWinAuthentication>
 <OraEnableDetailedTracing>False</OraEnableDetailedTracing>
 <OraPassword Sensitive="1" Encrypted="1">[encrypted-password-placeholder]</OraPassword>
</OracleXMLPackage>" />
<OleDbConnection Name="localhost\SQLSERVER2012.MistTest" ConnectionString="Data Source=localhost\SQLSERVER2012;Initial Catalog=MistTest;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
</Connections>
<Packages>
<Package Name="Package1" ForcedExecutionValueDataType="Empty" Language="None" ConstraintMode="Parallel" ProtectionLevel="EncryptSensitiveWithUserKey" SsisPackageType="5">
<Tasks>
<Dataflow Name="Data Flow Task" ForcedExecutionValueDataType="Empty" DefaultBufferMaxRows="0" DefaultBufferSize="0" RunInOptimizedMode="false">
<Annotations>
<Annotation AnnotationType="Description">Data Flow Task</Annotation>
</Annotations>
<Transformations>
<CustomComponent Name="Oracle Source" LocaleId="None" Version="4" UsesDispositions="true" ComponentClassId="{CB67CD40-126C-4280-912D-2A625DFAFB66}" ComponentTypeName="CB67CD40-126C-4280-912D-2A625DFAFB66" ContactInfo="Oracle Source;Microsoft Connector for Oracle by Attunity; Attunity Ltd.; All Rights Reserved; http://www.attunity.com;4">
<Annotations>
<Annotation AnnotationType="Description">Microsoft Oracle Source Component by Attunity</Annotation>
</Annotations>
<CustomProperties>
<CustomProperty Name="TableName" DataType="String" SupportsExpression="true" Description="The name of the table to be fetched.">"HR"."EMPLOYEES"</CustomProperty>
<CustomProperty Name="SqlCommand" DataType="String" SupportsExpression="true" Description="The SQL command to be executed.">SELECT * FROM "HR"."EMPLOYEES"</CustomProperty>
<CustomProperty Name="BatchSize" DataType="Int32" SupportsExpression="true" Description="The number of rows fetched in a batch.">100</CustomProperty>
<CustomProperty Name="PrefetchCount" DataType="Int32" SupportsExpression="true" Description="Number of pre-fetched rows.">0</CustomProperty>
<CustomProperty Name="LobChunkSize" DataType="Int32" SupportsExpression="true" Description="Determines the chunk size allocation for LOB columns">32768</CustomProperty>
<CustomProperty Name="DefaultCodePage" DataType="Int32" SupportsExpression="true" Description="The code page to use when code page information is unavailable from the data source.">1252</CustomProperty>
<CustomProperty Name="AccessMode" DataType="Int32" TypeConverter="AccessMode" Description="The mode used to access the database.">1</CustomProperty>
</CustomProperties>
<OutputPaths>
<OutputPath Name="Oracle Source Output" ErrorRowDisposition="FailComponent" TruncationRowDisposition="FailComponent">
<OutputColumns>
<OutputColumn Name="EMPLOYEE_ID" ExternalMetadataColumnName="EMPLOYEE_ID" />
<OutputColumn Name="FIRST_NAME" Length="20" DataType="AnsiString" CodePage="1252" ExternalMetadataColumnName="FIRST_NAME" />
<OutputColumn Name="LAST_NAME" Length="25" DataType="AnsiString" CodePage="1252" ExternalMetadataColumnName="LAST_NAME" />
<OutputColumn Name="EMAIL" Length="25" DataType="AnsiString" CodePage="1252" ExternalMetadataColumnName="EMAIL" />
<OutputColumn Name="PHONE_NUMBER" Length="20" DataType="AnsiString" CodePage="1252" ExternalMetadataColumnName="PHONE_NUMBER" />
<OutputColumn Name="HIRE_DATE" DataType="DateTime" ExternalMetadataColumnName="HIRE_DATE" />
<OutputColumn Name="JOB_ID" Length="10" DataType="AnsiString" CodePage="1252" ExternalMetadataColumnName="JOB_ID" />
<OutputColumn Name="SALARY" Precision="8" Scale="2" DataType="Decimal" ExternalMetadataColumnName="SALARY" />
<OutputColumn Name="COMMISSION_PCT" Precision="2" Scale="2" DataType="Decimal" ExternalMetadataColumnName="COMMISSION_PCT" />
<OutputColumn Name="MANAGER_ID" ExternalMetadataColumnName="MANAGER_ID" />
<OutputColumn Name="DEPARTMENT_ID" DataType="Int16" ExternalMetadataColumnName="DEPARTMENT_ID" />
</OutputColumns>
<ExternalColumns>
<ExternalColumn Name="EMPLOYEE_ID" />
<ExternalColumn Name="FIRST_NAME" Length="20" DataType="AnsiString" CodePage="1252" />
<ExternalColumn Name="LAST_NAME" Length="25" DataType="AnsiString" CodePage="1252" />
<ExternalColumn Name="EMAIL" Length="25" DataType="AnsiString" CodePage="1252" />
<ExternalColumn Name="PHONE_NUMBER" Length="20" DataType="AnsiString" CodePage="1252" />
<ExternalColumn Name="HIRE_DATE" DataType="DateTime" />
<ExternalColumn Name="JOB_ID" Length="10" DataType="AnsiString" CodePage="1252" />
<ExternalColumn Name="SALARY" Precision="8" Scale="2" DataType="Decimal" />
<ExternalColumn Name="COMMISSION_PCT" Precision="2" Scale="2" DataType="Decimal" />
<ExternalColumn Name="MANAGER_ID" />
<ExternalColumn Name="DEPARTMENT_ID" DataType="Int16" />
</ExternalColumns>
</OutputPath>
<OutputPath Name="Oracle Source Error Output" IsErrorOutput="true">
<OutputColumns>
<OutputColumn Name="EMPLOYEE_ID" />
<OutputColumn Name="FIRST_NAME" Length="20" DataType="AnsiString" CodePage="1252" />
<OutputColumn Name="LAST_NAME" Length="25" DataType="AnsiString" CodePage="1252" />
<OutputColumn Name="EMAIL" Length="25" DataType="AnsiString" CodePage="1252" />
<OutputColumn Name="PHONE_NUMBER" Length="20" DataType="AnsiString" CodePage="1252" />
<OutputColumn Name="HIRE_DATE" DataType="DateTime" />
<OutputColumn Name="JOB_ID" Length="10" DataType="AnsiString" CodePage="1252" />
<OutputColumn Name="SALARY" Precision="8" Scale="2" DataType="Decimal" />
<OutputColumn Name="COMMISSION_PCT" Precision="2" Scale="2" DataType="Decimal" />
<OutputColumn Name="MANAGER_ID" />
<OutputColumn Name="DEPARTMENT_ID" DataType="Int16" />
</OutputColumns>
</OutputPath>
</OutputPaths>
<Connections>
<Connection Name="MSOraConnection" ConnectionName="Oracle Connector 1" />
</Connections>
</CustomComponent>
<OleDbDestination Name="OLE DB Destination" LocaleId="None" ConnectionName="localhost\SQLSERVER2012.MistTest" UseFastLoadIfAvailable="false" MaximumInsertCommitSize="2147483647" DefaultCodePage="1252">
<Annotations>
<Annotation AnnotationType="Description">OLE DB Destination</Annotation>
</Annotations>
<InputPath OutputPathName="Oracle Source.Oracle Source Output" SsisName="OLE DB Destination Input" />
<ExternalTableOutput Table="[OracleEmployees]" />
</OleDbDestination>
</Transformations>
</Dataflow>
</Tasks>
<Connections>
<Connection ConnectionName="localhost\SQLSERVER2012.MistTest" />
<Connection ConnectionName="Oracle Connector 1" />
</Connections>
</Package>
</Packages>
</Biml>
Enhancing the Sample
It is possible to use BimlScript to get the schema information for the columns in your Oracle result set, and automatically emit the corresponding column metadata. If you would like to see that walkthrough, leave a comment, and I'll put it together.