Oracle to Dynamics 365 for Finance and Operations (on-premises) Integration using SSIS
Data is typically stored in many different data storage systems. One of the biggest challenges faced by many of our customers is extracting data from all sources and merging the data into a single, consistent dataset. In this tutorial, I will describe how we implemented a robust framework to integrate Oracle with Microsoft Dynamics 365 on-premises ERP for one of our large customers.
Microsoft Integration Services provides a rich set of built-in tasks, containers, transformations, and data adapters that support the development of business applications. Without writing a single line of code, you can create SSIS solutions that solve complex business problems using ETL and business intelligence, manage SQL Server databases, and copy SQL Server objects between instances of SQL Server.
In this example, we will focusing on the task of exporting data from an Oracle source database and importing said data into a staging SQL Server database using an integration package. This database in turn can be used as a source by Dynamics 365 to create Journals via Batch Job, updating Setups, and so on. The resulting Integration package can be triggered both manually ad-hoc or in on schedule using SQL Server Agent.
Note that, the Staging Environment can either be a separate standalone environment, or can also be implemented within the Dynamics 365 Environment itself since the required features to be installed do not clash.
Within the Staging Environment install the following components:
1. Integration Services feature using MSSQL Installer;
2. Download and install Microsoft Connectors v5.0 for Oracle and Teradata;
3. Install Oracle Client (32-bit). If this step is skipped an error will be throw when trying to connect to the Oracle source from within SQL Data Tools (Error: Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed).
Creating Integration Service Package
This package will connect directly to the Oracle tables. Where required Data Conversions will be performed to align Oracle Data Types to SQL Server ones. Finally validation whether record has already been copied or not is done to filter out unwanted records is done. If record exists, an update is done, if not a bulk copy is executed.
- Copy the Oracle Database’s DDLs and convert them to MSSQL queries;
- Create Staging Destination database using MSSQL queries;
- Open SQL Server Data Tools in Administrator Mode (Run as Administrator).
Note: Always run in Administrator to have enough privileges to run and deploy package.
- Create a new Package;
- Add Data Flow Task to the Package;
6. Create Oracle Connection;
7. Go to Start and create an ODBC Data Source. Depending on your installation this could be in place;
8. Add Oracle Source to Package;
9. Edit Oracle Source. Choose previously configured Oracle Connection from Drop-Down;
10. Add a Data Conversion Connect to Oracle Source;
11. Convert the required fields within the Data Conversion task, changing Data Type, Length and/or Precision where necessary;
12. Add a Look Up Connect to Data Conversion. This task will be used to check if records exist already in the Destination;
13. Specify how to handle rows with no matching entries. Choose Redirect rows to no match output;
14. Add Connection from second tab. Use results of an SQL Query. In this query, only select the primary keys in the table;
15. In the Columns tab, connect the Primary Key from the Select to the Primary Key of the table;
16. Add an OLE DB Destination, and choose previously created ODBC Data Source;
17. Connect the Lookup No Match Output to this destination;
18. Add OLE DB Command to Data Flow;
19. Connect Lookup Match Output to OLE DB Command;
20. Edit OLE DB Command, add the Connection from drop-down in Connection Managers;
21. Add UPDATE SQL Command in the very last property;
22. Map the columns to the parameters from Column Mappings;
23. Package is ready to be run.
Deploying Integration Service Package
1. From within SQL Server Management Studio, navigate to the Integration Services Catalog and Create Catalog…;
2. Create an encryption for the catalog;
3. From within SQL Data Tools, right-click on the SSIS Package created and choose Deploy Package;
4. Select a Destination where you will be deploying the package;
5. Choose the Path. The catalogue previously create will be the Destination;
6. You are now ready to deploy.
Maria Refalo is a Senior Solution Developer at Bluefort, specialising in Dynamics 365 for Finance and Operations (Enterprise Edition) and Dynamics NAV. Have a question? Contact her on email@example.com or www.linkedin.com/in/mariarefalo