Repository

  • 001 How to enable MongoDB connections

    Upgrade procedure (from VI versions 9 and AETL version 6)

    1. Create repository backup.
    2. Stop all services(Enterprise version only)
    3. Uninstall software
    4. Upgrade repository
    5. Install software
    Repository Upgrade Procedure:

    Run the following SQL against your repository:

    All repository types

    Insert into OBJECT_TYPES
    (OBJECT_TYPE,OBJECT_TYPE_NAME)
    Values
    ('903','MongoDB Connection')

    Insert into OBJECT_TYPES
    (OBJECT_TYPE,OBJECT_TYPE_NAME)
    Values
    ('904','MongoDB Connections')

    INSERT INTO OBJECTS_TREE
    (OBJECT_ID,
    PARENT_ID,
    NAME,
    OBJECT_TYPE
    )
    Select
    OBJECT_ID*-4 as OBJECT_ID,
    PARENT_ID,
    'MongoDB' as NAME,
    903 as OBJECT_TYPE
    From
    OBJECTS_TREE
    where OBJECTS_TREE.OBJECT_TYPE=320

    UPDATE OBJECT_TYPES
    set OBJECT_TYPE_GROUP=22000
    WHERE OBJECT_TYPE= '903'

    For Oracle

    ALTER TABLE QUEUE ADD TERMINATE_ON_TIMEOUT NUMBER (1) default 0 NOT NULL
    ALTER TABLE QUEUE_HISTORY ADD TERMINATE_ON_TIMEOUT NUMBER (1) default 0 NOT NULL
    ALTER TABLE SCHEDULE ADD TERMINATE_ON_TIMEOUT NUMBER (1) default 0 NOT NULL
    ALTER TABLE EVENT_MONITORS ADD TERMINATE_ON_TIMEOUT NUMBER (1) default 0 NOT NULL

    For MS SQL Server

    ALTER TABLE QUEUE ADD TERMINATE_ON_TIMEOUT decimal(1, 0) NULL default 0
    ALTER TABLE QUEUE_HISTORY ADD TERMINATE_ON_TIMEOUT decimal(1, 0) NULL default 0
    ALTER TABLE SCHEDULE ADD TERMINATE_ON_TIMEOUT decimal(1, 0) NULL default 0
    ALTER TABLE EVENT_MONITORS ADD TERMINATE_ON_TIMEOUT decimal(1, 0) NULL default 0

    For MySQL

    ALTER TABLE QUEUE ADD TERMINATE_ON_TIMEOUT NUMERIC(1) DEFAULT 0
    ALTER TABLE QUEUE_HISTORY ADD TERMINATE_ON_TIMEOUT NUMERIC(1) DEFAULT 0
    ALTER TABLE SCHEDULE ADD TERMINATE_ON_TIMEOUT NUMERIC(1) DEFAULT 0
    ALTER TABLE EVENT_MONITORS ADD TERMINATE_ON_TIMEOUT NUMERIC(1) DEFAULT 0

    For PostgreSQL

    ALTER TABLE QUEUE ADD TERMINATE_ON_TIMEOUT NUMERIC(1) DEFAULT 0
    ALTER TABLE QUEUE_HISTORY ADD TERMINATE_ON_TIMEOUT NUMERIC(1) DEFAULT 0
    ALTER TABLE SCHEDULE ADD TERMINATE_ON_TIMEOUT NUMERIC(1) DEFAULT 0
    ALTER TABLE EVENT_MONITORS ADD TERMINATE_ON_TIMEOUT NUMERIC(1) DEFAULT 0

    For Interbase

    ALTER TABLE QUEUE ADD TERMINATE_ON_TIMEOUT NUMERIC (1) default 0,
    ALTER TABLE QUEUE_HISTORY ADD TERMINATE_ON_TIMEOUT NUMERIC(1) DEFAULT 0
    ALTER TABLE SCHEDULE ADD TERMINATE_ON_TIMEOUT NUMERIC(1) DEFAULT 0
    ALTER TABLE EVENT_MONITORS ADD TERMINATE_ON_TIMEOUT NUMERIC(1) DEFAULT 0

    Upgrade procedure (from VI version 8 and AETL version 5)

    1. Create repository backup (backup must have one file inside).
    2. Stop all services
    3. Uninstall software
    4. Install software
    5. Create a brand new repository and connect to it
    6. Restore repository from backup
    7. Run the following SQL against the repository:

    INSERT INTO OBJECTS_TREE
    (OBJECT_ID,
    PARENT_ID,
    NAME,
    OBJECT_TYPE
    )
    Select
    OBJECT_ID*-4 as OBJECT_ID,
    PARENT_ID,
    'MongoDB' as NAME,
    903 as OBJECT_TYPE
    From
    OBJECTS_TREE
    where OBJECTS_TREE.OBJECT_TYPE=320

    UPDATE OBJECT_TYPES
    set OBJECT_TYPE_GROUP=22000
    WHERE OBJECT_TYPE= '903'

    Note: If you are having problems upgrading our software please let us know and we will do our best to assist you

  • 003 How to enable cloud connections

    Run the following SQL against the repository to enable Cloud connections 

    Insert into OBJECT_TYPES
    (OBJECT_TYPE,OBJECT_TYPE_NAME)
    Values
    ('905','Cloud Storage Connection')

    Insert into OBJECT_TYPES
    (OBJECT_TYPE,OBJECT_TYPE_NAME)
    Values
    ('906','Cloud Storage Connections')

    INSERT INTO OBJECTS_TREE
    (OBJECT_ID,
    PARENT_ID,
    NAME,
    OBJECT_TYPE
    )
    Select
    OBJECT_ID*-4 as OBJECT_ID,
    PARENT_ID,
    'Cloud Storage' as NAME,
    905 as OBJECT_TYPE
    From
    OBJECTS_TREE
    where OBJECTS_TREE.OBJECT_TYPE=322

    UPDATE OBJECT_TYPES
    set OBJECT_TYPE_GROUP=24000
    WHERE OBJECT_TYPE= '905'

    Note: If you are having problems upgrading our software please let us know and we will do our best to assist you

  • 004 How to enable Amazon and Azure connections

    Run the following SQL against the repository to enable Amazon and Azure connections 

    Insert into OBJECT_TYPES
    (OBJECT_TYPE,OBJECT_TYPE_NAME)
    Values
    ('907','Amazon Connection')

    Insert into OBJECT_TYPES
    (OBJECT_TYPE,OBJECT_TYPE_NAME)
    Values
    ('908','Amazon Connections')

    Insert into OBJECT_TYPES
    (OBJECT_TYPE,OBJECT_TYPE_NAME)
    Values
    ('909','Azure Connection')

    Insert into OBJECT_TYPES
    (OBJECT_TYPE,OBJECT_TYPE_NAME)
    Values
    ('910','Azure Connections')

    UPDATE OBJECT_TYPES
    set OBJECT_TYPE_GROUP=25000
    WHERE OBJECT_TYPE= '907' --Amazon Connection

    UPDATE OBJECT_TYPES
    set OBJECT_TYPE_GROUP=26000
    WHERE OBJECT_TYPE= '908' --Amazon Connections

    UPDATE OBJECT_TYPES
    set OBJECT_TYPE_GROUP=26000
    WHERE OBJECT_TYPE= '909' --Azure Connection

    UPDATE OBJECT_TYPES
    set OBJECT_TYPE_GROUP=26000
    WHERE OBJECT_TYPE= '910' --Azure Connection/

    INSERT INTO OBJECTS_TREE
    (OBJECT_ID,
    PARENT_ID,
    NAME,
    OBJECT_TYPE
    )
    Select
    OBJECT_ID*-5 as OBJECT_ID,
    PARENT_ID,
    'Amazon' as NAME,
    907 as OBJECT_TYPE
    From
    OBJECTS_TREE
    where OBJECTS_TREE.OBJECT_TYPE=322

    INSERT INTO OBJECTS_TREE
    (OBJECT_ID,
    PARENT_ID,
    NAME,
    OBJECT_TYPE
    )
    Select
    OBJECT_ID*-6 as OBJECT_ID,
    PARENT_ID,
    'Azure' as NAME,
    909 as OBJECT_TYPE
    From
    OBJECTS_TREE
    where OBJECTS_TREE.OBJECT_TYPE=322

    Note: If you are having problems upgrading our software please let us know and we will do our best to assist you

  • 005 Dealing with Wrong Repository Version message

    What is a repository anyway?

    Both Visual Importer ETL and Advanced ETL Processor use a repository database to store all its objects.  The default repository database is MS Access. It is not recommended to use MS Access as a repository in a 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 the 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 the 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
    Direct link, no registration required.
  • 006 How to enable Microsoft Office 365 connections

    Run the following SQL against the repository to enable Microsoft Office 365 connections 

    Insert into OBJECT_TYPES(OBJECT_TYPE,OBJECT_TYPE_NAME,OBJECT_TYPE_GROUP)
    Values(913,'Office 365 Connection',28000)

    Insert into OBJECT_TYPES
    (OBJECT_TYPE,OBJECT_TYPE_NAME,OBJECT_TYPE_GROUP)
    Values(914,'Office 365 Connections',28000)

    INSERT INTO OBJECTS_TREE
    (OBJECT_ID,PARENT_ID,NAME,OBJECT_TYPE)
    Select
    OBJECT_ID*-7 as OBJECT_ID,
    PARENT_ID,'Office 365' as NAME,
    913 as OBJECT_TYPE
    From
    OBJECTS_TREE
    where
    OBJECTS_TREE.OBJECT_TYPE=322

    UPDATE OBJECT_TYPES
    set OBJECT_TYPE_GROUP=28000
    WHERE OBJECT_TYPE= '913' 

    UPDATE OBJECT_TYPES
    set OBJECT_TYPE_GROUP=28000
    WHERE OBJECT_TYPE= '914' 

    Note: If you are having problems upgrading our software please let us know and we will do our best to assist you

  • 007 How to enable RabbitMQ connections

    Run the following SQL against the repository to enable RabbitMQ connections 

    Insert into OBJECT_TYPES(OBJECT_TYPE,OBJECT_TYPE_NAME,OBJECT_TYPE_GROUP)
    Values(915,'RabbitMQ Connection',29000)

    Insert into OBJECT_TYPES(OBJECT_TYPE,OBJECT_TYPE_NAME,OBJECT_TYPE_GROUP)
    Values(916,'RabbitMQ Connections',29000)

    INSERT INTO OBJECTS_TREE (OBJECT_ID,PARENT_ID,NAME,OBJECT_TYPE)
    SelectOBJECT_ID*-8 as OBJECT_ID,
    PARENT_ID,'RabbitMQ' as NAME,
    915 as OBJECT_TYPE
    From
    OBJECTS_TREE
    whereOBJECTS_TREE.OBJECT_TYPE=322

    UPDATE OBJECT_TYPES
    set OBJECT_TYPE_GROUP=29000 WHERE OBJECT_TYPE= '915' 

    UPDATE OBJECT_TYPES
    set OBJECT_TYPE_GROUP=29000 WHERE OBJECT_TYPE= '916'

    Note: If you are having problems upgrading our software please let us know and we will do our best to assist you

    RabbitMQ is a trademark of VMware, Inc. in the U.S. and other countries.

  • 008 How to enable Apache Kafka connections

    Run the following SQL against the repository to enable Apache Kafka connections 

    Insert into OBJECT_TYPES(OBJECT_TYPE,OBJECT_TYPE_NAME,OBJECT_TYPE_GROUP)
    Values(917,'Apache Kafka Connection',30000)

    Insert into OBJECT_TYPES(OBJECT_TYPE,OBJECT_TYPE_NAME,OBJECT_TYPE_GROUP)
    Values(918,'Apache Kafka Connections',30000)

    INSERT INTO OBJECTS_TREE (OBJECT_ID,PARENT_ID,NAME,OBJECT_TYPE)
    SelectOBJECT_ID*-9 as OBJECT_ID,
    PARENT_ID,'Apache Kafka' as NAME,
    917 as OBJECT_TYPE
    From
    OBJECTS_TREE
    whereOBJECTS_TREE.OBJECT_TYPE=322

    UPDATE OBJECT_TYPES
    set OBJECT_TYPE_GROUP=30000 WHERE OBJECT_TYPE= '917' 

    UPDATE OBJECT_TYPES
    set OBJECT_TYPE_GROUP=30000 WHERE OBJECT_TYPE= '918'

    Note: If you are having problems upgrading our software please let us know and we will do our best to assist you

  • 03 Repository Database

    Repository Database

    In this tutorial, you will learn how to work with 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

  • 04 Creating Repository Database

    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

  • 05 Working With Repository Backups

    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

  • 06 Performing repository synchronisation

    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

  • Configuring Interbase Repository

    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

     

     

     

    Direct link, no registration required.
  • Configuring MySQL Repository

    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 a 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

     

     

     

    Direct link, no registration required.
  • Configuring Oracle Repository

    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

     

     

     

    Direct link, no registration required.

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

  • Configuring PostgreSQL Repository

    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

     

     

     

    Direct link, no registration required.
  • Configuring SQL Server Repository

    This article provides information about configuring MS SQL Server Repository connection

    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

    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 the developer has no access to the 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.

     

     

     

    Direct link, no registration required.
  • Repository

    Dealing with the repository is a popular topic on our support forum; this article combines all the information about the repository database into one place.

    What is a repository anyway?

    The repository is a database that 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: The default repository database is MS Access. It is not recommended to use MS Access as a repository in a 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 a repository manually and using the 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
    Direct link, no registration required.
  • Repository database updates

    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.

    More information about the repository can be found in our ETL wiki

    Please always make a repository backup before making any changes.

    For the very old versions, the repository update can be a complex task. We are always happy to do it for you on a chargeable basis. The price for the repository update service is $150.

  • Repository objects synchronisation

    • 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

     

    Direct link, no registration required.
  • Repository Tables

    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

     

    Direct link, no registration required.

Page 1 of 2

This site uses cookies. By continuing to browse the site, you are agreeing to our use of cookies