Oracle to Dynamics 365 for Finance and Operations (on-premises) Integration using SSIS

Introduction

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.

Architecture

Oracle to Dynamics 365 for Finance and Operations (on-premises) Integration using SSIS image

Installation

Within the Staging Environment install the following components:

1. Integration Services feature using MSSQL Installer;

Oracle to Dynamics 365 for Finance and Operations (on-premises) Integration using SSIS image

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.

  1. Copy the Oracle Database’s DDLs and convert them to MSSQL queries;
  2. Create Staging Destination database using MSSQL queries;
  3. 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.

  1. Create a new Package;
  2. Add Data Flow Task to the Package;
Oracle to Dynamics 365 for Finance and Operations (on-premises) Integration using SSIS image

6. Create Oracle Connection;

Oracle to Dynamics 365 for Finance and Operations (on-premises) Integration using SSIS image
Oracle to Dynamics 365 for Finance and Operations (on-premises) Integration using SSIS image

7. Go to Start and create an ODBC Data Source. Depending on your installation this could be in place;

Oracle to Dynamics 365 for Finance and Operations (on-premises) Integration using SSIS image
Oracle to Dynamics 365 for Finance and Operations (on-premises) Integration using SSIS image

8. Add Oracle Source to Package;

Oracle to Dynamics 365 for Finance and Operations (on-premises) Integration using SSIS image

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;

Oracle to Dynamics 365 for Finance and Operations (on-premises) Integration using SSIS image

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;

Oracle to Dynamics 365 for Finance and Operations (on-premises) Integration using SSIS image

14. Add Connection from second tab. Use results of an SQL Query. In this query, only select the primary keys in the table;

Oracle to Dynamics 365 for Finance and Operations (on-premises) Integration using SSIS image

15. In the Columns tab, connect the Primary Key from the Select to the Primary Key of the table;

Oracle to Dynamics 365 for Finance and Operations (on-premises) Integration using SSIS image

16. Add an OLE DB Destination, and choose previously created ODBC Data Source;

Oracle to Dynamics 365 for Finance and Operations (on-premises) Integration using SSIS image

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;

Oracle to Dynamics 365 for Finance and Operations (on-premises) Integration using SSIS image

21. Add UPDATE SQL Command in the very last property;

Oracle to Dynamics 365 for Finance and Operations (on-premises) Integration using SSIS image

22. Map the columns to the parameters from Column Mappings;

Oracle to Dynamics 365 for Finance and Operations (on-premises) Integration using SSIS image

23. Package is ready to be run.

Oracle to Dynamics 365 for Finance and Operations (on-premises) Integration using SSIS image

Deploying Integration Service Package

1. From within SQL Server Management Studio, navigate to the Integration Services Catalog and Create Catalog…;

Oracle to Dynamics 365 for Finance and Operations (on-premises) Integration using SSIS image

2. Create an encryption for the catalog;

Oracle to Dynamics 365 for Finance and Operations (on-premises) Integration using SSIS image

3. From within SQL Data Tools, right-click on the SSIS Package created and choose Deploy Package;

Oracle to Dynamics 365 for Finance and Operations (on-premises) Integration using SSIS image

4. Select a Destination where you will be deploying the package;

Oracle to Dynamics 365 for Finance and Operations (on-premises) Integration using SSIS image

5. Choose the Path. The catalogue previously create will be the Destination;

Oracle to Dynamics 365 for Finance and Operations (on-premises) Integration using SSIS image

6. You are now ready to deploy.

Oracle to Dynamics 365 for Finance and Operations (on-premises) Integration using SSIS image

References

https://www.microsoft.com/en-us/download/details.aspx?id=55179

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 mrefalo@bluefort.com.mt or www.linkedin.com/in/mariarefalo