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: http://aws.amazon.com/
Oracle 11g XE Installation
Once you have an account setup, follow the instructions in this great blog post to install Oracle XE on a new EC2 virtual machine. It's a long post, but it only takes about 30 minutes to get through all of the steps. Installfest: Oracle 11g XE on Amazon Linux Micro Instance
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 features that are not part of the current BidsHelper release (v1.6.2). To complete this sample, download the latest BimlEngine.dll file. Depending on your system settings, you may need to right-click this file, click Properties, and then click Unblock to ensure that it is not prevented from loading. Copy the unblocked BimlEngine.dll over your existing BidsHelper installation. If you used the installer, this is likely to be located at C:\Program Files (x86)\BIDS Helper 2012\BimlEngine.dll.
If you used xcopy deployment, this will be located at:
- [My Documents]/Visual Studio 2005/Addins/BimlEngine.dll (for SQL Server 2005)
- [My Documents]/Visual Studio 2008/Addins/BimlEngine.dll (for SQL Server 2008 and SQL Server 2008 R2)
- [My Documents]/Visual Studio 2010/Addins/BimlEngine.dll (for SQL Server 2012)
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 BIDS/SSDT.
Also note that the next version of BimlEngine.dll will include built-in support for the connectors so that custom connection and component syntax (along with the manual metadata) will not be needed.
<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>ec2-107-20-117-195.compute-1.amazonaws.com</OraServerName>
 <OraUserName>username</OraUserName>
 <OraOracleHome></OraOracleHome>
 <OraOracleHome64></OraOracleHome64>
 <OraWinAuthentication>False</OraWinAuthentication>
 <OraEnableDetailedTracing>False</OraEnableDetailedTracing>
 <OraPassword Sensitive="1" Encrypted="1">AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAm1Wd3JvvU0+cUJlVQyqnaAAAAAAIAAAARABUAFMAAAADZgAAwAAAABAAAAA6xkf97NyWpMZVJk8BfUfOAAAAAASAAACgAAAAEAAAABcdn9bN864uL88pKlNCAlwYAAAAcbtMszBiQhegvc8dBJXJRRNt9xmQRe2pFAAAANMQTe7MVbECC97BJJ8QQO0KMUfc</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.
Comments
Henk
2:12pm 03.07.13
Hi Scott, This example comes exactly at the right moment for our project where we need to import 65 tables from Oracle into SQL Server (and already have Attunity in place). I'm very interested in your BimlScript to automate the generation of the BIML based on the Oracle schema information. A full walkthrough would be nice, but the core parts of the script (connect to Oracle, getting the schema info, looping for the outputpaths, etc.) will do as well. You mention "next version of BimlEngine.dll will include built-in support for the connectors". Should I wait or not? i.e. when will that be? Regards, Henk. Netherlands.
Scott Currie
8:04pm 03.07.13
Hi Henk,
We're working on the development of the Oracle language element right now. It's usage will be identical to the OleDbSource presently in Biml (except for TableFromVariable and Parameters, which aren't supported in the Oracle component).
In terms of timing, we're going to need to do another BidsHelper release very shortly to support the new BI project in VS2012 functionality that Microsoft shipped this past Wednesday. I'm trying to get it into that release. So we're looking at a week or two. Can you wait that long, or should I make it a priority to give a script sample?
The basic workflow of the script would be to run the query against the Oracle server in SchemaOnly mode, get the column metadata as .NET objects, and then translate those into the above syntax. Biml has some helper methods to make that easier than it sounds, but it's still a lot worse than having the nice language feature.
Henk
12:01pm 03.11.13
Hi Scott, Thanks for your info. I have to get this working this week... but I'm absolutely not in the position to set your priorities :-) Which are the helper methods you mention? Regards.
Edmund
8:27pm 03.12.13
The BimlEngine.dll you reference (at https://bimlscript.blob.core.windows.net/bimlengine-v3-2-1/BimlEngine.dll) shows up as file version 2.0.0.0, which is the same version I got with BidsHelper. Are you folks not updating the assembly version numbers?
Paul S. Waters
4:42pm 04.29.13
Edmund,
BIDS Helper is currently tied to 2.0.0.0 of the BimlEngine.dll, so even when there are updates the file version stays the same.
Thanks,
Paul
Yip
4:11pm 03.13.14
Dear Scott, We are now do feasibility to start a SSIS related project. One key objective of the project is transfer the data from one Oracle database to another one daily. After some study, we have some directions: 1. Use BIML to automate the tasks build the data transfer tasks (more than 100 tables in our environment) 2. We need to use the attunity drivers for oracle to speed up the performance (we have 30M records in some tables) 3. Better to use a meta-data driven approach to simply the tasks The aforesaid article is a good start but I still need to clarify some issues and need your advice: 1. The latest version of BIDS release is v.1.6.5. Does the BimlEngine.dll file already support the of Attunity drivers? 2. When use BIML, is it a must to use OLEDB connections instead of ADO.NET or other connectors? 3. I searched the web and some people stated that Attunity and BIML only works on MIST (but not BIDS helper), is it true? 4. what's the usage of Varigence.Hadron.CoreLowerer.SchemaManagement? Can I use it extract the table schemas from Oracle DB and build the BIML scripts automatically? Any information on above items (or even working samples) are welcomed and appreciated. Thx in advance.
Scott Currie
1:42am 03.14.14
Yip,
Ireneus
7:12am 10.13.14
Hi, After generating a SSIS 2012 package in Mist, I discovered that transformation relationships are missing while editing the component in SSDT. None of the columns has been checked. In BIML script I changed names for some of the OutputColumns so they differ from ExternalColumns. However, I can see them in generated package when editing in Advanced Mode. OLEDB Source has usually IsUsed property for input columns nodes, the Attunity component has not - I'll get compilation error in Mist if I try to add it. What am I doing wrong?
Vidya
9:12pm 11.10.15
Is BIML supports Attunity Oracle Connector? I am getting "No translator was found error when I try to use Oracle Source for Attunity.
Ishfaq
3:00pm 06.27.16
Hi,
does BIML support using the .Net Teradata driver? The issue I have run into is the fact I can’t get BIML to create a data flow task with an ADO.Net Source of Teradata. I can create a ADO.Net Teradata connection in a package which is valid, it only when I try and use it as a source that I encounter this error below:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server). -1 -1
the error message refers to SQL even though I'm connecting to Teradata.
any ideas?
Bhudev
11:29am 08.09.16
Great and only Article on "Using the Attunity Oracle Connector with Biml"
I was trying to take help of this article but could not find success it hence putting my problem here... Hoping if you can help me out:
We have ETL Project requirement to extract data from around 100+ Oracle tables. We are using SSIS 2014 and SSIS Connectors v3.0 for Oracle Microsoft Oracle Source Component by Attunity. We want to use BIML to create all the extract SSIS Packages with single click. We are struggling in BIML for establishing connection string and data flow
This is the data BIML Connections script Connection Name =" MSORAConnectionName " ConnectionString="SERVER=OracleServer;USERNAME=dummyuser;ORACLEHOME=;ORACLEHOME64=;WINAUTH=0
This is the data flow task BIML script OracleSource Name="ORASRC" ValidateExternalMetadata="false" Connection="MSORAConnectionName" BatchSize="10000">
I’m in need of help here; when compile the BIML script I found the error message: Could not resolve reference to of “MSORAConnectionName” type OracleSource. Connection MSORAConnectionName is invalid.
Karthik6
6:00pm 05.10.18
Hi.I need to migrate data from Oracle to SQL, So I need to use Oracle Source for my data flow but unfortunately the Oracle connection is not working for me. So please if someone have tried that post your comments using for establishing Oracle connection and for dataflow tasks.