Open Linking and Embedding Database (OLEDB) is a set of COM-based interfaces that expose data from a variety of sources. They provide applications with uniform access to data stored in diverse information sources, or data stores. These interfaces support the amount of DBMS functionality appropriate to the data store, enabling the data store to share its data.

Reference: https://www.connectionstrings.com/microsoft-ole-db-provider-for-sql-server-sqloledb/ .

Connection String

To Connect to OLEDB, set the Server and Database Properties. Additionally, set the provider and Integrated Security Property.

Connection String Parameters

Parameter Description
Provider Set this to SQLOLEDB.
Data Source Set this to the Hostname of the server.
Initial Catalog Set this to the DatabaseName.
Integrated Security Set this to authenticate with Using Windows authentication.

Connecting to OLEDB Connector

  1. Set the ‘Provider’ parameter to “sqloledb”.

  2. Set the ‘Data Source’ to the address to your database’s server. Since using a local network server to test this tutorial. I place the server name and instance name, separated by a backslash.

  3. Set the ‘Initial Catalog’ parameter to the name of your database.

  4. Set the Integrated Security to SSPI to authenticate your connection string to use windows authentication. SSPI is the OLEDB compatible way of setting integrated security to true.

Data Migrations

To Check for the data migrations:

  1. Add a new connection in Loome Integrate as shown.

    • Go to tasks and click on Connections.

    • Add a new connection Using Add New Connection Option.

    • Choose OLEDB Connector from the available Connector Options.

  2. Using the connection string parameters created (You can Specify the Username and password either in the connection string or in the box Shown Below), Verify the connection using the VERIFY CONNECTION Option in Loome Integrate. Once the Connection is verified, Insert the Connection Using Insert Option.

  3. Create a Job Using ADD A JOB option in Loome Integrate, add a data migration task to the job.

  4. Create a New Task by right clicking job list and then Add a New Task Option. If you want to edit an Existing Task Use Edit Task.

  5. Choose a Data Migration Task.

  6. Choose the source and the destination for the migration of data. Name the Task.

  7. Choose the Tables or the data that need to be migrated from the source to the destination. You can Copy all Tables by checking Copy all tables box.

  8. Execute the Job. Check for results and the details of the data migrated in Execution History.