Repository

  • Repository Database

    In this tutorial, you will learn how to work Advanced ETL Processor repository.

    The best way to become acquainted with the new tools, controls and features available in Advanced ETL Processor is to use them. This tutorial walks you through repository creation, maintenance, import and export processes.

    This tutorial is intended for users familiar with fundamental database operations, but who have limited exposure to the new features available in Advanced ETL Processor

    All our ETL tools have no limitations, that means you can load your data today and solve all the data quality problems right now.

    Our WIKI has more detailed information if you are stuck post your question on our support forum and we will do our best to assist you

  • CREATING REPOSITORY DATABASE

    The repository is a very important database. It holds all the metadata used by our products, therefore it is important to understand how to work with it.

    All our ETL tools have no limitations, that means you can load your data today and solve all the data quality problems right now.

    Our WIKI has more detailed information if you are stuck post your question on our support forum and we will do our best to assist you

  • Working With Repository Backups

    The repository is a very important database. It holds all the metadata used by our products, therefore it is important to understand how to work with it.

    All our ETL tools have no limitations, that means you can load your data today and solve all the data quality problems right now.

    Our WIKI has more detailed information if you are stuck post your question on our support forum and we will do our best to assist you

  • Performing repository synchronisation

    The repository is a very important database. It holds all the metadata used by our products, therefore it is important to understand how to work with it.

    All our ETL tools have no limitations, that means you can load your data today and solve all the data quality problems right now.

    Our WIKI has more detailed information if you are stuck post your question on our support forum and we will do our best to assist you

  • This article provides information about configuring Interbase/Firebird repository connection

    To create a new repository connection

    Click Options
    Click  Plus
    Enter connection name
    Select type
    Enter user name and password
    Click ODBC manager and create new ODBC DSN
    Select newly created DSN
    Make sure that connection actually works

    Interbase Create

    Firebird ODBC settings

    Interbase

    Videos

    Download Free Trial Learn More About Our ETL Software
  • This article provides information about configuring MySQL repository connection

    To create a new repository connection

    Click Options
    Click  Plus
    Enter connection name
    Select type
    Enter username and password
    Click ODBC manager and create new ODBC DSN
    Select newly created DSN
    Make sure that connection actually works

    MySQL Create

    MySQL ODBC settings

    MySQL1

    MySQL2

    MySQL3

    MySQL5

    MySQL6

    MySQL7

    Note:
    It is recommended to increase max_allowed_packet. The server's default max_allowed_packet value is 1MB. 
    You can increase this if the server needs to handle big queries (for example, if you are working with big BLOB columns).
    For example, to set the variable to 16MB, start the server like this: shell> mysqld --max_allowed_packet=16M
    You can also use an option file to set max_allowed_packet. For example, to set the size for the server to 16MB,
    add the following lines in an option file: [mysqld] max_allowed_packet=16M

    Videos

    Download Free Trial Learn More About Our ETL Software
  • This article provides information about configuring Oracle Repository connection

    To create a new Oracle repository connection

    Click Options
    Click Plus
    Enter connection name
    Set type to Oracle
    Select/Enter TNS name or use instant client syntax
    Enter user name and password
    Make sure that connection actually works

    Oracle Repository

    Working with Oracle:

    Always install the latest relevant client

       Oracle client 8.1.7 to load data into/from Oracle
         Or
       Oracle client 9 to load data into/from Oracle
         Or
       Oracle client 10 to load data into/from Oracle
         Or
       Oracle client 11 to load data into/from Oracle
         Or
       Oracle client 12 to load data into/from Oracle

    Videos

    Note: 32-bit version of our ETL Software uses 32-bit version of Oracle client and 64-bit version of our ETL Software uses 64-bit version of Oracle client

    Download Free Trial Learn More About Our ETL Software
  • This article provides information about configuring PostgreSQL Repository connection

    To create a new repository connection

    Click Options
    Click  Plus
    Enter connection name
    Select type
    Enter username and password
    Click ODBC manager and create new ODBC DSN
    Select newly created DSN
    Make sure that connection actually works

    Configure PostgreSQL

    PostgreSQL ODBC settings

    PostgreSQL1

     

    PostgreSQL2

     

    PostgreSQL3

    Videos

    Download Free Trial Learn More About Our ETL Software
  • This article provides information about configuring MS SQL Server Repository connection

    All versions are supported including 2000, 2005, 2008, 2012, 2014, 2016, 2017 and Compact Edition.
    By default MS SQL Server repository connection is using 32 bit Microsoft OLE DB Provider for SQL Server, 
    it is possible to use different 32 bit OLE DB Provider by switching to OLE DB Repository connection type.

    oledb

    To create a new MS SQL Server repository connection

    Click Options
    Click  Plus
    Enter connection name
    Set type to MS SQL Server
    Select/Enter server name
    Enter username and password
    Select database name
    Make sure that connection actually works

     

    MS SQL Server Repository1

    Note: When user name and password are blank windows autentification is used

    To create a new MS SQL Server repository connection using Ole DB

    Click Options
    Click  Plus
    Enter connection name
    Set type to Ole DB
    Select build connection
    Select relevant Ole DB provider and enter necessary parameters
    Make sure that connection actually works

     

    MS SQL Server Repository2

    To avoid problems always make sure that you are using the latest version of 32 bit MS SQL Server client
    Notes:

    Using Sql Server user name and password is always better than using windows authentication. Consider the following scenario. Administrator Jonh installs the software everything is working fine. Developer Peter logs in, nothing is working, because developer has no access to repository database. Peter gets proper access and he can do his job. Over weekend another administrator want to check execution status he logs in, the same problem happens again.

    Videos

    Download Free Trial Learn More About Our ETL Software
  • Dealing with repository is a popular topic on our support forum; this article combines all the information about repository database into one place.

    What is repository anyway?

    Repository is a database which stores information about connections, objects, schedule and execution details. Some of the data is encrypted.
    The repository structure is the same for all relevant products. This allows sharing and reusing the data.

    Following products use repository database:

    •  Visual Importer ETL Professional
    •  Visual Importer ETL Enterprise
    •  Advanced ETL Processor Professional
    •  Advanced ETL Processor Enterprise
    •  Active Table Editor
    •  Database Browser

    Supported repository types

    • MS Access
    • SQL server
    • SQL server compact
    • Oracle    
    • PostgreSQL
    • MySQL
    • Interbase/Firebird
    Note: Default repository database is MS Access. It is not recommended to use MS Access as repository in production environment, because it tends to get corrupted over time

    Creating repository

    Before "Repository" can be used it must be created and tables inside the repository must be created. There two ways to create repository manually and using repository creation wizard

    Note:  Once installation is completed user may find all the scripts in C:\Users\Public\Documents\DBSL\Repository Scripts\Repository

    VIDEOS

    Note:  Active Table Editor uses the same repository creation wizard

    Database specific repository articles

    More information

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

    Repository Sincronization wizard1

    Repository Sincronization wizard2

    Repository Sincronization wizard3

    Video

    Download Free Trial Learn More About Our ETL Software
  • New release of Advanced ETL Processor and Visual Importer ETL is available for download

    Changes are

    Two new tables were added QUEUE_HISTORY and QUEUE_ACTIONS_HISTORY
    Once package execution is completed records from QUEUE and QUEUE_ACTIONS are moved into those two tables
    These modifications improve overall application stability

    Upgrade procedure:

    There are two ways to update the repository

  • Below is a list of tables used by etl-tools.com Products

    Table NameUsage Visual Importer ETLAdvanced ETL  ProcessorActive Table EditorDatabase Browser
    TEMPLATES Library of objects    
    ID_GENERATOR PK Generators
    CONNECTIONS Connection details
    OBJECT_TYPES Object Types Lookup
    OBJECTS Holds objects data: SQL, Forms, Transformations Mappings, Packages and Reports  
    OBJECTS_HISTORY Version Control    
    OBJECTS_TREE Objects Tree    
    QUEUE List of objects being executed right now or waiting to be executed, once execution is completed records are moved into QUEUE_HISTORY table    
    QUEUE_ACTIONS Current execution details, once execution is completed records are moved into QUEUE_ACTIONS_HISTORY table    
    QUEUE_HISTORY Object Execution History    
    QUEUE_ACTIONS_HISTORY Object Actions Execution History    
    SCHEDULE Execution schedule    
    USERS List of users      
    GROUPS List of groups      
    ACCESS_RIGHTS Access Rights      
    GROUP_MEMBERS List of groups members      
    NODES Holds computer metadata    
    NODE_STATUS Holds information about software installed    
    APPLICATION_SETTINGS  Application settings    
    SQL_EXECUTION_HISTORY  History of SQL execution
    MENUS  User menus      

    Repository Schema

    ETL Repository Tables
    ETL Repository Tables
    ETL Repository Tables
  • Why it is important to know how to work with the repository.

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

    Working with repositoryDefault 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 to perform “compact and repair” using MS Access. For heavy production environment for example when we want to run packages every minute in parallel we recommend using something else like MS SQL server or Oracle.

    Note:

    Information about current repository connection can be seen at the window header

    Creating a 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 stars it connects to default MS Access repository.

    Connecting to a 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 the different repository.

    Creating a new repository.

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

    Creating a new repository connection

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

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

    Same dialogue can be used to amend existing repository connections.

    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
  • What is repository anyway?

    Both Visual Importer ETL and Advanced ETL Processor use repository database to store all its objects.  Default repository database is MS Access. It is not recommended to use MS Access as repository in production environment.

    Supported repository types are:

    MS Access
    SQL server
    Oracle    
    PostgreSQL
    MySQL
    Interbase/Firebird

    Before "Repository" can be used it must be created and tables inside the repository must be created. If repository database exits but no tables were created "Wrong Repository Version" error message will be shown

    Wrong Repository Version

     Or if it is not possible to connect to the repository: "Failed to connect to the repository"

    Failed To Connect To The Repository

    Creating Repository:

    The repository can be created by running scripts manually or by using repository creation wizard

    Watch these tutorials to understand working with Repository better:

     

    Note:  Once installation is completed user may find all the scripts in C:\Users\Public\Documents\DBSL\Repository Scripts\Repository
    Download Free Trial Learn More About Our ETL Software

We have 267 guests and 2 members online