Working with Repository

Why it is important to know how to work with the repository?

The repository database stores all the information about connections, transformations, packages, SQL scripts, reports and execution logging. This is where the results of ETL designer hard work is stored and obviously, no one wants to lose it.

Default Repository

The default repository is MS Access. This type of repository works fine for development and for a small production environment. From time to time we recommend performing “compact and repair” using MS Access. For heavy production environments for example, when we want to run packages every minute in parallel we recommend using something else like MS SQL Server Express. It is free and very easy to configure

Provider Not Found Error Message

The user may receive the message above after installing 64 version of our ETL Software. This is because by default 64 bit OLE driver for MS Access is not installed on windows. Although it is possible to install a 64 bit OLE driver for MS Access by using our software we strongly recommend against it. The best course of action is to use a proper database as repositories such as MS SQL Server or Oracle.

Installing MS Access Runtime

During setup, our software will install Access runtime if selected

Installing MS Access Runtime

Installing 64-bit Access runtime on the computer where the 32-bit version of MS Office is already installed will lead to problems working with MS office (Repairing MS Office installation error message).

Working with Repository

Creating Repository

The repository can be created by running a script or by using the repository creation wizard.\ They are located in C:\Users\Public\Documents\DBSL\Repository Scripts.

To create a new MS Access repository copy
C:\Users\Public\Documents\DBSL\Repository Scripts\Repository\Repository.mdb into the different directory and connect to it.

Default Repository Connections

When software is installed default repository connections are created such as MS Access, Oracle, SQL Server, MySQL, PostgreSQL. When the application first starts it connects to the default MS Access repository.

Connecting to different Repository

  • Click Maintain tab
  • Select the desired connection from the drop-down box
  • Click reconnect.

Provided that you are using default settings all open objects will be saved and the application will connect to a different repository.

Creating new Repository

  • Click Maintain tab
  • Click create new
  • Follow the wizard steps
  • Once the repository is created connect to it

Creating new Repository connection

If you want to create a new connection to the existing repository:

  • Click Maintain tab
  • Click options
  • Click plus
  • Fill in all necessary details
  • Test connection
  • Click OK

Backing up Repository

  • Click Maintain tab
  • Click backup
  • Select the file to save data into
  • Done

Restoring Repository

  • Click Maintain tab
  • Click restore
  • Select the file to restore data from
  • Done

Copying repository data from development to production environment

  • Connect to the development environment
  • Backup repository
  • Connect to the production environment
  • Restore repository

Copying single project from development to production environment

  • Connect to the development environment
  • Backup repository
  • Connect to the production environment
  • Right-click on the object tree and restore the project
  • Select the project to restore and click OK
Restore Project
Restore Repository Dialogue

Repository objects synchronization

  • Run Repository Synchronization Wizard
  • Select source repository or repository backup
  • Select target repository
  • Drug and drop source repository objects on top of target repository objects to update target object
  • Drug and drop source repository objects in the target category to add a new object
  • Connect to target repository and update connections if necessary
Synchronization Wizard
Synchronization Wizard
Synchronization Wizard
Synchronization Wizard
Synchronization Wizard

Controlling size of Repository

While executing scheduled tasks the size of the repository is constantly growing. From time to time it might become necessary to clear execution log tables otherwise it might have a negative impact on performance.

  • Click the Execution Log tab
  • Click the clear log button
Clear Log

Unable to connect to the Repository error

The default repository is MS Access. The 64-bit setup offers the option to install the 64-bit version of access drivers. Without them, the 64-bit version of our software will not be able to work with access or use it as a repository. We do not recommend using MS access as a repository in a production environment. The important thing to understand that the 32-bit version uses 32-bit version drivers and it cannot use 64-bit drivers. The same applies to the 64-bit version. Before installing the 64-bit version you have to make sure that relevant 64-bit drivers, ODBC Drivers, OleDB Providers and Clients are installed.

Video Tutorials

For more technologies supported by our ETL Software see Advanced ETL Processor Versions

Confused? Ask question on our ETL Forum
Last updated: March 14, 2023