Vimp Professional User Manual

Visual Importer Professional & Enterprise User manual

© DB Software Laboratory 2009

Visual Importer Professional & Enterprise User Manual

www.dbsoftlab.com Contents Contents....................................................................................................................................... 2 1. Introduction ............................................................................................................................. 5 2. Requirements........................................................................................................................... 5 3. Key features ............................................................................................................................. 7 4. Visual Importer Architecture................................................................................................. 10 5. Demo Data............................................................................................................................. 11 6. Options .................................................................................................................................. 12 7. User Interface ........................................................................................................................ 16 Main window......................................................................................................................... 16 Objects tree............................................................................................................................ 17 8. Connections and Directories.................................................................................................. 18 Oracle Connection ................................................................................................................. 19 Microsoft SQL Server Connection ........................................................................................ 20 ODBC Connection................................................................................................................. 21 ODBC Connection Strings ................................................................................................ 23 MySql Connection................................................................................................................. 25 PostgreSQL Connection ........................................................................................................ 26 Interbase/Firebird Connection ............................................................................................... 27 Directories ............................................................................................................................. 28 9. Importing Data....................................................................................................................... 29 Mapping editor screen overview ........................................................................................... 31 Main tool bar ..................................................................................................................... 32 Source tool bar................................................................................................................... 32 Mapping panel ................................................................................................................... 33 SQL Statements ................................................................................................................. 35 Template tab ...................................................................................................................... 36 Log File tab........................................................................................................................ 37 Rejected Records Tab........................................................................................................ 38 Import Process ................................................................................................................... 39 Data Mapping for a Flat File Data Source............................................................................. 40 Auto mapping ........................................................................................................................ 44 Data Mapping for an ODBC Data Source ............................................................................. 47 ODBC Manager..................................................................................................................... 48 How to clear mapping ........................................................................................................... 49 Loading data from the Cross tables ....................................................................................... 50 Performing Calculations........................................................................................................ 52 Filtering Records ................................................................................................................... 53 Working with Date fields ...................................................................................................... 55 How to Update/Delete Records ............................................................................................. 56 Using Database Specific functions. ....................................................................................... 58 MS SQL Server specific parameters ..................................................................................... 59 Oracle specific parameters..................................................................................................... 60 Copyright © 2009 DB Software Laboratory

Page 2 of 117

Visual Importer Professional & Enterprise User Manual

ODBC specific parameters .................................................................................................... 63 Error Handling....................................................................................................................... 65 10. Exporting Data..................................................................................................................... 66 11. SQL Scripts ......................................................................................................................... 68 SQL Scripts Tool Bar ............................................................................................................ 71 12. Packages .............................................................................................................................. 72 Package screen overview....................................................................................................... 73 Joining Actions...................................................................................................................... 75 Package Tool bar ................................................................................................................... 75 Action Dialog ........................................................................................................................ 76 Import Action ........................................................................................................................ 77 Export Action ........................................................................................................................ 78 SQL Script Action ................................................................................................................. 79 Package Action...................................................................................................................... 80 Check file Action................................................................................................................... 81 Application Action ................................................................................................................ 82 File Operation Action ............................................................................................................ 83 Email Action.......................................................................................................................... 84 Ftp Action.............................................................................................................................. 85 Compare files......................................................................................................................... 86 ZIP ......................................................................................................................................... 87 POP3 Email Receiver ............................................................................................................ 88 SQL Data Check.................................................................................................................... 89 Working with filenames and directories................................................................................ 90 13. Scheduler ............................................................................................................................. 91 14. Execution Monitor............................................................................................................... 95 Tool bar ................................................................................................................................. 97 Stopping execution ................................................................................................................ 97 15. SQL...................................................................................................................................... 98 SQL Toolbar.......................................................................................................................... 98 Side toolbar............................................................................................................................ 99 16. Import Functions ............................................................................................................... 100 String Functions................................................................................................................... 100 Numeric Functions .............................................................................................................. 103 Date Functions..................................................................................................................... 104 Conversion Functions.......................................................................................................... 107 Miscellaneous Functions ..................................................................................................... 108 17. Date formats ...................................................................................................................... 109 18. Execution Agent ................................................................................................................ 110 19. Command Line .................................................................................................................. 111 20. Repository Synchronisation............................................................................................... 113 21. Support Procedure ............................................................................................................. 114 22. License Agreement ............................................................................................................ 115

Copyright © 2009 DB Software Laboratory

Page 3 of 117

Visual Importer Professional & Enterprise User Manual

Copyright © 2009 DB Software Laboratory Limited. All rights reserved. No portion of this document may be reproduced, transmitted, transcribed, stored in a retrieval system, or translated into any language, in any form or by any means, without prior written consent of DB Software Laboratory Limited.

Note to U.S. Government users: Documentation and programs related to restricted rights - use, duplication or disclosure is subject to restrictions set forth in: GSA FMSS Schedule Contract No. GS00K92AFS2505-PS05. License Information You have access to Visual Importer Professional and Enterprise software and documentation pursuant to the terms of a Software License Agreement granted by DB Software Laboratory Limited. As a user of this software and documentation, you are bound by the terms of the Software License Agreement. All rights, title, and interest to this software remain with DB Software Laboratory Limited. Requests for copies of this publication and for technical information about DB Software Laboratory products should be made directly to DB Software Laboratory Limited. Disclaimer All information in this manual is subject to periodic change and revision without notice. While every effort has been made to ensure that this manual is accurate, DB Software Laboratory Limited excludes its liability for errors or inaccuracies (if any) contained herein. Registered Marks Any products or services mentioned or depicted in this document are identified by the trademarks or service marks of their respective companies or organisations. Edition Information This document refers to Visual Importer Professional and Enterprise version 7.7.3.0

Copyright © 2009 DB Software Laboratory

Page 4 of 117

Visual Importer Professional & Enterprise User Manual

1. Introduction Visual Importer is a business intelligence tool that provides an integrated solution for designing and deploying data warehouses and data marts. It solves complex problem of integration between different data sources and targets. It combines components of both ETL and designs tools into one tool.

2. Requirements Below is the list of Software that must be installed before installation of Visual Importer: Software Microsoft Windows MDAC

Version 98 or higher 2.6 or higher

MS Excel ODBC driver

4.00.6305.00 or higher

MS Excel 2007 ODBC driver MS Access ODBC driver

12.00.4518.1014 or higher 4.00.6364.00 or higher

Separate download

MS Access 2007 ODBC driver

12.00.4518.1014 or higher

Separate download

FoxPro ODBC driver SQL ODBC driver

6.1.8629.1 or higher 2000.81.9041.40

Separate download

Oracle Client

7.3.4 or higher

Interbase client

Jet 4.0

#otes Part of OS on W2K, XP, Vista. Part of OS on W2K, XP, Vista.

Part of OS on W2K, XP, Vista.

Part of OS on W2K, XP, Vista. Provided by Oracle

GDS32.DLL

Service pack 5 or higher

Copyright © 2009 DB Software Laboratory

Part of OS on W2K, XP, Vista.

Only to import data from Excel 3.0-2003 files Only to import data from Excel 2007 files Only to load data from MS Access 95-2003 Databases Only to load data from MS Access 2007 Databases Only to load data from DBF/FoxPro Files Only to import data into MS SQL Server 7/2008 Only to import data into Oracle Databases/Connect to Oracle repository Only to work with Interbase or Firebird Databases To connect to MS Access repository

Page 5 of 117

Visual Importer Professional & Enterprise User Manual

Separate Downloads: FoxPro ODBC driver http://msdn.microsoft.com/en-us/vfoxpro/bb190233.aspx Office 2007 Data Access Components http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72EF94E038C891&displaylang=en Working with Oracle: Oracle client 8.1.7 and Oracle Ole DB Provider for Oracle to use Oracle repository on Oracle 8-9 and load data into/from Oracle Or Oracle client 9 and Oracle Ole DB Provider for Oracle to use Oracle repository on Oracle 8-9 and load data into/from Oracle Or Oracle client 10 and Oracle Ole DB Provider for Oracle to use Oracle repository on Oracle 910g and load data into/from Oracle. Or Oracle client 11 and Oracle Ole DB Provider for Oracle to use Oracle repository on Oracle 10-11g and load data into/from Oracle.

#ote: Depending on the Requirements you may or may not need to have all components installed There is no need to install clients for MySql and PostgreSQL they are integrated into the software itself.

Copyright © 2009 DB Software Laboratory

Page 6 of 117

Visual Importer Professional & Enterprise User Manual

3. Key features Data import Data targets: • Oracle 7-11g database (using OCI API) • SQL server 7- 2005 (using BCP API) • ODBC source (using ODBC API) • Interbase/Firebird • MySQL • PostgreSQL Data sources: • • • • • • • • • • •

Multiple Delimited or Fixed width Text files Multiple Excel files + Multiple Excel Spreadsheets Multiple MS Access Databases + Multiple Tables Multiple DBF Files Multiple Tables Oracle 7-11g database SQL server 7- 2005 ODBC source Interbase/Firebird MySQL PostgreSQL

Repository: • MS Access • Oracle • MS Sql Server • Interbase • MySQL • PostgreSQL This product features: • Great performance - thousands of records per second • Comprehensive logging • Rejected records file • Integrated Expressions builder • Filtering Data Allows the user to perform calculation during the loading process such as splitting fields, concatenations, data formatting, and loading cross/pivot tables. Copyright © 2009 DB Software Laboratory

Page 7 of 117

Visual Importer Professional & Enterprise User Manual

Copyright © 2009 DB Software Laboratory

Page 8 of 117

Visual Importer Professional & Enterprise User Manual

Oracle There are two ways of loading available: • Oracle direct path loading • Conventional path MS SQL server This software uses the same API as Microsoft DTS/SSIS service. ODBC Any ODBC compliant database. Other features are: • • • • • • • • • • • • • • • • • •

Easy Repository switching Data Preview Mapping editor Exports SQL scripts editor Packages editor Integrated Scheduler Logging Lookups Email Notifications File Operations Check Files Compare Files FTP Downloads/Uploads ZIP Applications PO3 Email Receiver SQL Data Check

Copyright © 2009 DB Software Laboratory

Page 9 of 117

Visual Importer Professional & Enterprise User Manual

4. Visual Importer Architecture Oracle

Oracle

Execution Queue

SQL

SQL

MS Access

Imports/Exports SQL Scripts Packages Ftp downloads File checks File Compare File operations Emails Logging

DBF Files

Scheduler

ODBC Excel

MySQL

Packages

OCI API

ODBC Excel

BCI API

MS Access ODBC API

Integrated Clients

DBF Files MySQL

PostgreSQL

PostgreSQL

Interbase

Interbase

Flat Files

SQL Scripts

Flat Files

Imports/ Exports Connections

Copyright © 2009 DB Software Laboratory

Oracle, MS SQL Server, MS Access, MySQL, PostgreSQL, Interbase

Page 10 of 117

Visual Importer Professional & Enterprise User Manual

5. Demo Data Text files Examples provided are using text files in “C:\Program Files\DB Software Laboratory\Demo\Buffer” and “C:\Program Files\DB Software Laboratory\Demo\Text Files” If you want to use another location please amend Directories properties for ‘Text files’ and “Buffer”. Demo tables Use SQL Scripts provided to create demo tables for Oracle and MS SQL server. Please adjust connection details before executing these scripts. Most of the SQL Server Imports use DEMO database. You have to create demo tables within this database first before executing import scripts. ODBC connections Setup program creates following ODBC DSN during the installation: • • • • • •

ODBC_FOXPRO ODBC_ACCESS_SOURCE ODBC_ACCESS_TARGET ODBC_MS ODBC_ORACLE ODBC_EXCEL

Copyright © 2009 DB Software Laboratory

Page 11 of 117

Visual Importer Professional & Enterprise User Manual

6. Options To change Visual Importer settings click System menu-> File-> Options. Dialog box will appear.

Copyright © 2009 DB Software Laboratory

Page 12 of 117

Visual Importer Professional & Enterprise User Manual

Repository tab defines repository connection. Repository type can be: • MS Access • MS SQL Server • Oracle • Interbase (Firebird) • MySql • PostgreSQL #ote: Provided Default Repository [MS Access] contains all the SQL scripts required to create Oracle, MS SQL server, Interbase, MySql and PostgreSQL repositories.

Execution tab defines settings related to the logging and Packages execution.

Copyright © 2009 DB Software Laboratory

Page 13 of 117

Visual Importer Professional & Enterprise User Manual

Interface tab defines settings for the log refreshing and records deletion, etc.

Email tab defines settings required to send Emails.

Copyright © 2009 DB Software Laboratory

Page 14 of 117

Visual Importer Professional & Enterprise User Manual

Copyright © 2009 DB Software Laboratory

Page 15 of 117

Visual Importer Professional & Enterprise User Manual

7. User Interface Main window System Menu

Objects tree Status bar

Copyright © 2009 DB Software Laboratory

Page 16 of 117

Visual Importer Professional & Enterprise User Manual

Objects tree Visual Importer uses Objects tree to provide the user with an easy access to the main objects. Below is a list of possible objects with the short descriptions:

Directories

Defines path to flat files, Excel files, MS Access databases or DBF files for loading into the database SQL Server Connection Defines connection to load data into or from ODBC Connection

Defines connection to load data into or from

MySql Connection

Defines connection to load data into or from

PostgreSQL

Defines connection to load data into or from

Interbase

Defines connection to load data into or from

Oracle Connection

Defines connection to load data into or from

Import

Defines mapping between Data source and Data target

Export

Defines Export format

SQL Scripts

Defines SQL statements to perform against target Databases

Lookups

Provides quick way to edit/view data, also used for checking data in packages. Combines complex Actions together like Ftp downloads File operations, emails, Check files, SQL scripts and Imports/Exports.

Packages

Copyright © 2009 DB Software Laboratory

Page 17 of 117

Visual Importer Professional & Enterprise User Manual

8. Connections and Directories The database connection functionality provides a way of connecting to database servers. Once a connection is set up, it is always available until deleted or amended. In order to set up a new connection it will be necessary to have database credentials to hand. When setting up database connections, you should ensure that the account you wish to use has access to all the available functionality you may wish to use. For instance, it is not much use, setting up a connection to an Oracle account to use certain stored procedures, if the underlying Oracle permissions are not set for this facility. To create a new connection Click System menu-> New-> Connection.

Once this step has been completed, you will be provided with an appropriate dialogue which will request information about the new connection you wish to create, and the type of connection you are creating.

Copyright © 2009 DB Software Laboratory

Page 18 of 117

Visual Importer Professional & Enterprise User Manual

Oracle Connection For an Oracle connection you will need to specify the TNS name required, and provide the user name and password for the connection. #ote: It is also possible to use Oracle instant client.

The procedure to set up an Oracle Connection is as follows: • • • •

In the Name Text Box type in a new name for the connection you are about to create Select Oracle from the Connection Type Drop Down List Select a TNS Name from the TNS drop down List or type it in. Fill in Username/Password for the database you wish to connect to a. If you are unsure of these parameters, please contact your Database Administrator for the correct settings.

• •

Click Test to ensure the details you have provided are correct Click OK to close the Connection Properties Window

Copyright © 2009 DB Software Laboratory

Page 19 of 117

Visual Importer Professional & Enterprise User Manual

Microsoft SQL Server Connection The procedure for many database connections is very similar. In the case of MS SQL Server it is necessary to specify the server, database name and user name/password combination.

An SQL Server connection setup requires the following steps: • • • • •

In the Name Text Box type in a new name for the connection you are about to create Select MS SQL Server from the Connection Type Drop Down List Select a Server Name from Server Drop Down List Select a Database Name form the Drop Down List Fill in Username/Password for the database you wish to connect to a. If you are unsure of these parameters, please contact your Database Administrator for the correct settings.

• •

Click Test to ensure the details you have provided are correct Click OK to close the Connection Properties Window

#ote: Leave user name and password blank to use Windows Authentication.

Copyright © 2009 DB Software Laboratory

Page 20 of 117

Visual Importer Professional & Enterprise User Manual

ODBC Connection ODBC history ODBC, standing for Open Data Base Connectivity, represents a standard database access method working as SQL API software. It was developed back in 1992 with the sole purpose of facilitating the work with database management systems (DBMS). It was created by the Microsoft corporation and is since then included in every copy of Microsoft Windows. In 1995 the ODBC was included in the SQL Standard, which played a great role in making ODBC more popular. Today, there are versions for almost any operating system currently used. How ODBC works ODBC was intended to enable developers' access to any data through any application, regardless of the DBMS used for managing that data. ODBC boasts platform independence since it has been purposefully designed in a way that makes it distinct from database systems, programming languages and operating systems. Facilitating the data access from an application to a database management system through ODBC is done through a specific mechanism. A common ODBC implementation contains one or more applications, a core ODBC 'Driver Manager' library, and one or more database drivers. The Driver Manager's role is to interpret the data queries coming from an application by using the DBMS-specific details contained in database drivers. The latter represent a middle layer inserted between an application and the DBMS in use. This way, the application's data queries are translated into commands that can easily be read by the DBMS. A basic requirement for an ODBC implementation to be run is that both the application and the DBMS be ODBC-compliant. In other words, the application must be able to issue ODBC commands and the DBMS must be capable of responding to them. Thanks to its modular nature ODBC gives developers a great freedom in creating the separate components of an ODBC implementation. Thus, a programmer can write applications that use standard features without needing to worry about the type of DBMS used for administering the database that the application tries to access. Likewise, the only thing the database driver programmers need to keep in mind during the development process is how to attach their database driver to the 'Driver Manager' library. The result is that currently there are hundreds of ODBC drivers created for a large variety of data sources. Thanks to the long period of existence and the fruitful efforts of its team of developers, ODBC now offers access to a much wider range of data sources than any other database access method available today. It has turned into a universal data access standard, working with a great variety of operating systems and providing access to even non-relational data, including text and XML files.

Copyright © 2009 DB Software Laboratory

Page 21 of 117

Visual Importer Professional & Enterprise User Manual

The ODBC Connection properties dialogue allows the administrator to create appropriate connections to various databases. It is be necessary to create connections to databases, whenever they are to be processed

In order to create an ODBC connection, the following steps should be followed: • • • •

In the Name Text Box type in a new name for the connection you are about to create Select ODBC from the Connection Type Drop Down List Select a ODBC DSN Name from Server Drop Down List Fill in Username/Password for the database you wish to connect to a. You may use ODBC admin to create/modify ODBC data sources.

• •

Click Test to ensure the details you have provided are correct Click OK to close the Connection Properties Window

#ote: ODBC connection can be used as a Data Target and Data source.

Copyright © 2009 DB Software Laboratory

Page 22 of 117

Visual Importer Professional & Enterprise User Manual

ODBC Connection Strings It also possible to use ODBC connection strings for both Reader and Writer connections. For example for MS SQL Server connection string is: Driver={SQL Native Client}; Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword; One of the major benefits of using connection strings that it makes it no longer necessary to create ODBC Dsn’s manually on every single computer where Visual Importer is installed. It also gives a greater control over the connection parameters. #ote: Leave user name and password blank and provide it within connection string More information about connection strings can be found at: http://www.connectionstrings.com The simplest way to create ODBC connection string is to use ODBC Connection builder dialog. Double click on ODBC driver name to create a connection string

Copyright © 2009 DB Software Laboratory

Page 23 of 117

Visual Importer Professional & Enterprise User Manual

Copyright © 2009 DB Software Laboratory

Page 24 of 117

Visual Importer Professional & Enterprise User Manual

MySql Connection

In order to create an MySql connection, the following steps should be followed: • • • • •

In the Name Text Box type in a new name for the connection you are about to create Fill in host name of the server you wish to connect Select a Database Name form the Drop Down List Select port number Fill in Username/Password for the database you wish to connect to b. If you are unsure of these parameters, please contact your Database Administrator for the correct settings.

• •

Click Test to ensure the details you have provided are correct Click OK to close the Connection Properties Window

#ote: My SQL connection can be used as a Data Target and Data source. Set port 0 to use default port

Copyright © 2009 DB Software Laboratory

Page 25 of 117

Visual Importer Professional & Enterprise User Manual

PostgreSQL Connection

In order to create an PostgreSQL connection, the following steps should be followed: • • • • •

In the Name Text Box type in a new name for the connection you are about to create Fill in host name of the server you wish to connect Select a Database Name form the Drop Down List Select port number Fill in Username/Password for the database you wish to connect to c. If you are unsure of these parameters, please contact your Database Administrator for the correct settings.

• •

Click Test to ensure the details you have provided are correct Click OK to close the Connection Properties Window

#ote: PostgreSQL connection can be used as a Data Target and Data source. Set port 0 to use default port

Copyright © 2009 DB Software Laboratory

Page 26 of 117

Visual Importer Professional & Enterprise User Manual

Interbase/Firebird Connection

In order to create an Interbase connection, the following steps should be followed: • • •

In the Name Text Box type in a new name for the connection you are about to create Fill in Database name of the server you wish to connect Fill in Username/Password for the database you wish to connect to d. If you are unsure of these parameters, please contact your Database Administrator for the correct settings.

• •

Click Test to ensure the details you have provided are correct Click OK to close the Connection Properties Window

#ote: Interbase connection can be used as a Data Target and Data source.

Copyright © 2009 DB Software Laboratory

Page 27 of 117

Visual Importer Professional & Enterprise User Manual

Directories

• • •

In the Name Text Box type in a new name for the directory you are about to create Fill in Directory path you wish to load data from Click OK to close the directory Properties Window

#ote: User may change connection or directory properties at any time by double clicking on it.

Copyright © 2009 DB Software Laboratory

Page 28 of 117

Visual Importer Professional & Enterprise User Manual

9. Importing Data In order to load data from the data source into the data target user must define data mapping between target table and data source. Possible data sources are: • • • • • •

Flat files (delimited and non-delimited). Excel Files MS Access Databases DBF files MS SQL Server Databases ODBC sources.

Import screen is designed to allow user to create, delete, modify, and test data mapping to the target databases. At the beginning the simplest way to create Import sctipt is to use import script wizard. • • •

To create a new Import click System menu-> New-> Import. Fill in Description edit box with the name of an Import you are about to create. Follow wizard Instructions

Copyright © 2009 DB Software Laboratory

Page 29 of 117

Visual Importer Professional & Enterprise User Manual

Once Import script is created it is possible to change data source and data target as shown on the picture below.

Copyright © 2009 DB Software Laboratory

Page 30 of 117

Visual Importer Professional & Enterprise User Manual

Mapping editor screen overview Double click on any demo Import. Import editor will appear. Main Tool bar

Target table fields list

Field Width

Source tool bar Target Fields

Copyright © 2009 DB Software Laboratory

Mapping Panel

Source fields Source file/Table Field No

Source data

Page 31 of 117

Visual Importer Professional & Enterprise User Manual

Main tool bar

1

2

3 4 5

6 7

8 9 10 11

1. Data Target options 2. Loads Import Script From the file 3. Saves Import Script to the file 4. Saves as 5. Saves Import to the Repository 6. Refreshes fields list fro the database 7. Checks Import for mapping errors 8. Data preview 9. Allows user to clear field mapping 10. Hides mapping panel 11. Data Import Source tool bar

1

2 3 4 5 6 1. 2. 3. 4. 5. 6. 7. 8.

7

8

Data source options Refreshes Source data Add new column Delete last column Auto map the source fields to the target fields Filter Source file name/ table name Records to fetch

Copyright © 2009 DB Software Laboratory

Page 32 of 117

Visual Importer Professional & Enterprise User Manual

Mapping panel Mapping panel is designed to provide user with all information related to the mapping of one particular field. There are two ways of mapping: direct and through calculations.

Mapping type

Source Field

Errors Handling Default value

Calculation

Date format Alternately you may hide Mapping panel and use grid to perform mapping. See the picture below:

Copyright © 2009 DB Software Laboratory

Page 33 of 117

Visual Importer Professional & Enterprise User Manual

Copyright © 2009 DB Software Laboratory

Page 34 of 117

Visual Importer Professional & Enterprise User Manual

SQL Statements Visual Importer provides functionality to execute SQL statements before and after data import. Note: • •

In order to Execute several SQL statements user must specify SQL statements delimiter. No select statements allowed.

Copyright © 2009 DB Software Laboratory

Page 35 of 117

Visual Importer Professional & Enterprise User Manual

Template tab

Copyright © 2009 DB Software Laboratory

Page 36 of 117

Visual Importer Professional & Enterprise User Manual

Log File tab

Copyright © 2009 DB Software Laboratory

Page 37 of 117

Visual Importer Professional & Enterprise User Manual

Rejected Records Tab

Copyright © 2009 DB Software Laboratory

Page 38 of 117

Visual Importer Professional & Enterprise User Manual

Import Process Calculation?

Assign Source Field to Target Field

Calcualte Field Value

No Errors?

Reject If Error

Set To Null If Error

Set To Default if Error

Result Is Null or Blank

Set to default If Null?

Reject Record

Date Format Defined?

Apply Date Format Reject If Null?

Reject If Error Ignore If Null?

Set To Null If Error

Set to default If Null? Set T o Default if Error

Continue to the next Field

#ote: Copyright © 2009 DB Software Laboratory

Page 39 of 117

Visual Importer Professional & Enterprise User Manual

Records can be also rejected by the Server. Data Mapping for a Flat File Data Source Visual Importer can load data from ASCII and Unicode files in UTf8, UTF16BE and UTF16LE formats with BOM marker and without Unicode is a computing industry standard allowing computers to consistently represent and manipulate text expressed in most of the world's writing systems. To perform data mapping: •

Click Data Target Options button.

• • • •

Dialog box will appear. Click Get tables list. Select Table you would like to import data into from Drop down List. Click OK.

Copyright © 2009 DB Software Laboratory

Page 40 of 117

Visual Importer Professional & Enterprise User Manual

The following list of fields should appear:

Click Data Source Option Button.

Dialog box will Appear. Select “Text file(s)” as Data Source Type. Click “Text” tab Set Delimiter and Quota to appropriate values.

Copyright © 2009 DB Software Laboratory

Page 41 of 117

Visual Importer Professional & Enterprise User Manual

Copyright © 2009 DB Software Laboratory

Page 42 of 117

Visual Importer Professional & Enterprise User Manual

Click OK.

Copyright © 2009 DB Software Laboratory

Page 43 of 117

Visual Importer Professional & Enterprise User Manual

Click

and select the file you want to import.

Select First field in the Data Target fields list and drag and drop it above [F1] field.

You may change field mapping by using mapping panel at any time.

Auto mapping If the Data Source and Data Target have got the same fields’ names you may use Auto map feature. Click

, Fill in all necessary data and click map.

Copyright © 2009 DB Software Laboratory

Page 44 of 117

Visual Importer Professional & Enterprise User Manual

Now we are ready to import data. Let’s check script first. Click

to check script.

Copyright © 2009 DB Software Laboratory

Page 45 of 117

Visual Importer Professional & Enterprise User Manual

We have to correct the error first.

Date format is missing

Click

to load data into the database

Once loading is finished you may check Log file or Rejected records file.

Copyright © 2009 DB Software Laboratory

Page 46 of 117

Visual Importer Professional & Enterprise User Manual

Data Mapping for an ODBC Data Source Data mapping for ODBC is very similar to the flat file mapping. Click Data Source Option Button.

Dialog box will appear. Fill in User name and Password if required. Click Get Tables List. Select table name to load data from the Drop Down List. Click OK.

Copyright © 2009 DB Software Laboratory

Page 47 of 117

Visual Importer Professional & Enterprise User Manual

ODBC Manager

Copyright © 2009 DB Software Laboratory

Page 48 of 117

Visual Importer Professional & Enterprise User Manual

How to clear mapping Click . Click on field you would like to clear.

Copyright © 2009 DB Software Laboratory

Page 49 of 117

Visual Importer Professional & Enterprise User Manual

Loading data from the Cross tables Let us say we have table like the following in the database: No 1 2 3 4 5

Field CUSTOMERID YEAR MONTH PRODUCTID AMOUNT

Data Type CHAR DECIMAL DECIMAL DECIMAL DECIMAL

And a text files like the one below: Year CustomerID ProductID Month_1 Qty_1 … Month_12 Qty_12

Click Data Source Button and check ‘Source file is a Cross table’ check box and set First Field to 4, Blocks to 12 and Block length to 2

Copyright © 2009 DB Software Laboratory

Page 50 of 117

Visual Importer Professional & Enterprise User Manual

Finally we are ready to import data Click

to load data into the database

Copyright © 2009 DB Software Laboratory

Page 51 of 117

Visual Importer Professional & Enterprise User Manual

Performing Calculations Visual Importer is capable of performing calculations during the import. To perform a simple calculation set mapping type to calculation and type constant or formula into calculation edit box.

For character type fields’ type '12' or "12'' Multiplying fields [INTEGER_F]*[FLOAT_F] Concatenation "[INTEGER_F]"+ " kilos" More complicated examples Iif([FLOAT_F]>[INTEGER_F],1,2) Trim('[CHAR_F]') You may also use Expression Editor

Copyright © 2009 DB Software Laboratory

Page 52 of 117

Visual Importer Professional & Enterprise User Manual

. Filtering Records User may filter records using the following example: RECORDTYPE=1 Customer information RECORDTYPE=2 Invoices Press

and type

Copyright © 2009 DB Software Laboratory

Page 53 of 117

Visual Importer Professional & Enterprise User Manual

If you want to use multiple criteria use following example ([RECORDTYPE]==1) || ([RECORDTYPE]==56) Where || - OR && - AND ! - NOT

Copyright © 2009 DB Software Laboratory

Page 54 of 117

Visual Importer Professional & Enterprise User Manual

Working with Date fields In order to load data into date or time fields date format must be provided for source field Visual Importer automatically converts data into format required for the target database Full list of date formats can be found in chapter 17

Date Format Source Date Field

Copyright © 2009 DB Software Laboratory

Page 55 of 117

Visual Importer Professional & Enterprise User Manual

How to Update/Delete Records In order to Update/Delete records you must specify update key.

For the example provided below, Visual Importer will execute the following SQL (Update key is CustomerId,OrderNo) Add #ew And Update Old Records Select count(*) from [DEMO].[dbo].[orders] where CustomerId=? And OrderNo=? If any records found Visual Importer will update them by executing Update [DEMO].[dbo].[orders] set orderdate=?, amount=? where customerid=? And OrderNo=? If no records is found Visual Importer will add new records

Copyright © 2009 DB Software Laboratory

Page 56 of 117

Visual Importer Professional & Enterprise User Manual

Update Records Update [DEMO].[dbo].[orders] set OrderDate=?, Amount=? where CustomerId=? And OrderNo=? Delete Records Delete from [DEMO].[dbo].[orders] Where CustomerId=? And OrderNo=?

Update Key

Copyright © 2009 DB Software Laboratory

Page 57 of 117

Visual Importer Professional & Enterprise User Manual

Using Database Specific functions. Mapping type SQL function allows using Database specific functions like sequences during import.

#ote: This option works only for ODBC connection or Oracle conventional path loading

Copyright © 2009 DB Software Laboratory

Page 58 of 117

Visual Importer Professional & Enterprise User Manual

MS SQL Server specific parameters

#ote: These options are only available when transformation type is Add All Records. When transformation type is different from Add All Records they are same as for ODBC connection Check constraints Ensure that any constraints on the destination table are checked during the bulk copy operation. By default, constraints are ignored. Keep identity Specify that there are values in the data file for an identity column. Keep #ULLS Specify that any columns containing a null value should be retained as null values, even if a default value was specified for that column in the destination table. Batch size Specify the number of rows in a batch. The default is the entire data file. The following values for the Batch size property have these effects: If you set Batch size to zero, the data is loaded in a single batch. The first row that fails will cause the entire load to be cancelled, and the step fails. If you set Batch size to one, the data is loaded a row at a time. Each row that fails is counted as one row failure. Previously loaded rows are committed. If you set Batch size to a value greater than one, the data is loaded one batch at a time. Any row that fails in a batch fails that entire batch; loading stops and the step fails. Rows in previously loaded batches are either committed or, if the step has joined the package transaction, provisionally retained in the transaction, subject to later commitment or rollback.

Copyright © 2009 DB Software Laboratory

Page 59 of 117

Visual Importer Professional & Enterprise User Manual

Oracle specific parameters Visual importer supports three methods of loading data into oracle Direct path load, conventional path load and ODBC. Direct-path load in Oracle is used when a session is reading buffers from disk directly into the PGA(opposed to the buffer cache in SGA). During direct-path INSERT operations, the database appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. Free space in the existing data is not reused, and referential integrity constraints are ignored. You may prefer to use a direct path load when you have a large amount of data to load quickly and you want to load data in parallel for maximum performance, but there are alternative costs to be aware of. With the conventional path load method, arrays of rows are inserted with standard sql INSERT statements, integrity constraints and insert triggers are automatically applied. But when you load data with the direct path, Visual Importer disables some integrity constraints and all database triggers. The constraints that remain in force are: NOT NULL UNIQUE PRIMARY KEY (unique-constraints on not-null columns) The following constraints are automatically disabled by default: CHECK constraints Referential constraints (FOREIGN KEY)

Copyright © 2009 DB Software Laboratory

Page 60 of 117

Visual Importer Professional & Enterprise User Manual

For conventional path loading when Commit every Array is checked import works as follows: Execute SQL before statement Commit Insert Array of records Commit Insert Array of records Commit Execute SQL after statement Commit When Commit every Array is not checked import is executed inside one big transaction: Start transaction Execute SQL before statement Insert Array of records Insert Array of records More inserts Execute SQL after statement Commit transaction #ote: Copyright © 2009 DB Software Laboratory

Page 61 of 117

Visual Importer Professional & Enterprise User Manual

Loading Unicode data via direct path is not supported. If your database was not created with Unicode support but you have some NCHAR or NVARCHAR2 fields you may need to set array size to 1.

Copyright © 2009 DB Software Laboratory

Page 62 of 117

Visual Importer Professional & Enterprise User Manual

ODBC specific parameters

When Commit is set to “every statement” import works as follows: Execute SQL before statement Commit Insert one record Commit Insert one record Commit Execute SQL after statement Commit #ote: Most databases support this way of loading data including files

Copyright © 2009 DB Software Laboratory

Page 63 of 117

Visual Importer Professional & Enterprise User Manual

When Commit is set to “once import is completed” import is executed inside one big transaction: Start transaction Execute SQL before statement Insert one record Insert one record More inserts Execute SQL after statement Commit transaction #ote: Not all databases support this way of loading data.

Copyright © 2009 DB Software Laboratory

Page 64 of 117

Visual Importer Professional & Enterprise User Manual

Error Handling Error handling tab defines format of rejected records file and allows user to abort import on error. #ote: When “Abort Import” is selected import is immediately aborted after an error and SQL After script is not executed

Copyright © 2009 DB Software Laboratory

Page 65 of 117

Visual Importer Professional & Enterprise User Manual

10. Exporting Data • • • • • • • • • • •

To create a new Export Click System menu-> New-> Export. Dialog box will appear Fill in Name edit box with the name of Export you are about to create Select Connection from the Drop Down List you want to export data from. Select a Target Directory you want to export data into. Fill in comment if required Specify delimiter Specify Text Qualifier Select SQL tab Type SQL Click OK to finish creation of Export Script.

Copyright © 2009 DB Software Laboratory

Page 66 of 117

Visual Importer Professional & Enterprise User Manual

Query builder

#ote: It is also possible to use Query builder to design queries

Copyright © 2009 DB Software Laboratory

Page 67 of 117

Visual Importer Professional & Enterprise User Manual

11. SQL Scripts Once data is loaded into the database user has to perform various tasks like transformations, validations or calculations. User may perform any SQL command allowed by the target database. SQL commands must be separated by a separator. SQL Scripts Screen is designed to provide such functionality for the end user. • • • • • • •

To create a new SQL Script Click System menu-> New-> SQL Script Dialog box will appear Fill in Description edit box with the name of SQL Script you are about to create Select Connection from the Drop Down List you want to run the script against. Select a Separator from the Drop Down List or fill it in. Fill in comment if required Click OK to finish creation of SQL Script

Copyright © 2009 DB Software Laboratory

Page 68 of 117

Visual Importer Professional & Enterprise User Manual

To edit script double click on any previously created SQL Scripts.

Copyright © 2009 DB Software Laboratory

Page 69 of 117

Visual Importer Professional & Enterprise User Manual

Copyright © 2009 DB Software Laboratory

Page 70 of 117

Visual Importer Professional & Enterprise User Manual

SQL Scripts Tool Bar

1

2 3 4

5 6

7 8 9 10 11 12 13 14 15 16 17

1. Sql Script Properties 2. Open the SQL script from the disk 3. Save the SQL script to the disk 4. Save the SQL Script to the repository 5. Print the SQL script 6. Preview 7. Cut 8. Copy 9. Paste 10. Undo 11. Search 12. Repeat Search 13. Find Previous 14. Replace 15. Execute SQL Script 16. Stop execution when any error happens 17. Show/hide log

Copyright © 2009 DB Software Laboratory

Page 71 of 117

Visual Importer Professional & Enterprise User Manual

12. Packages Packages Screen combines different Actions together. Possible Actions are: • • • • • • • • • • • •

Imports Exports SQL Scripts Packages File Checks File operations Ftp Downloads/Uploads Emails Applications ZIP’s POP3 Email Receivers SQL Data checks

• • • • •

To create a new Package Click System menu-> New-> Package. Dialog box will appear. Fill in Description edit box with the name of the Package you are about to create. Fill in comment if required. Click OK to finish creation of the Package.

Copyright © 2009 DB Software Laboratory

Page 72 of 117

Visual Importer Professional & Enterprise User Manual

Package screen overview Double click to edit an Action

Copyright © 2009 DB Software Laboratory

Available Actions

Page 73 of 117

Visual Importer Professional & Enterprise User Manual

Objects Tree Drag and Drop Actions from the left to the right

What to do if error happened

Copyright © 2009 DB Software Laboratory

What to do next after success

Drag and Drop Actions from the list

Page 74 of 117

Visual Importer Professional & Enterprise User Manual

#ote: It is impossible to execute package without starting point (Action in blue box on the picture). Joining Actions Drag and Drop

#ote: To remove join repeat same procedure again. Package Tool bar

1

2 3 4 5 6 7 8 9 10 11 12 1. Packages Properties 2. Save to the Repository 3. Clear all 4. Cut 5. Copy 6. Paste 7. Delete selected Action(s) 8. Execute 9. Align Vertical 10. Align Horizontal 11. Print

Copyright © 2009 DB Software Laboratory

Page 75 of 117

Visual Importer Professional & Enterprise User Manual

12. Print Preview Action Dialog

Copyright © 2009 DB Software Laboratory

Page 76 of 117

Visual Importer Professional & Enterprise User Manual

Import Action To create a new Import Action, Drag and Drop it from the Object tree. You may change type of an Action at any time.

Copyright © 2009 DB Software Laboratory

Page 77 of 117

Visual Importer Professional & Enterprise User Manual

Export Action

Copyright © 2009 DB Software Laboratory

Page 78 of 117

Visual Importer Professional & Enterprise User Manual

SQL Script Action

Copyright © 2009 DB Software Laboratory

Page 79 of 117

Visual Importer Professional & Enterprise User Manual

Package Action #ote: Recursion is not allowed

Copyright © 2009 DB Software Laboratory

Page 80 of 117

Visual Importer Professional & Enterprise User Manual

Check file Action

Copyright © 2009 DB Software Laboratory

Page 81 of 117

Visual Importer Professional & Enterprise User Manual

Application Action

Copyright © 2009 DB Software Laboratory

Page 82 of 117

Visual Importer Professional & Enterprise User Manual

File Operation Action

Copyright © 2009 DB Software Laboratory

Page 83 of 117

Visual Importer Professional & Enterprise User Manual

Email Action

Copyright © 2009 DB Software Laboratory

Page 84 of 117

Visual Importer Professional & Enterprise User Manual

Ftp Action Ftp Action performs various operations on the ftp server

Copyright © 2009 DB Software Laboratory

Page 85 of 117

Visual Importer Professional & Enterprise User Manual

Compare files

Copyright © 2009 DB Software Laboratory

Page 86 of 117

Visual Importer Professional & Enterprise User Manual

ZIP

Copyright © 2009 DB Software Laboratory

Page 87 of 117

Visual Importer Professional & Enterprise User Manual

POP3 Email Receiver

Copyright © 2009 DB Software Laboratory

Page 88 of 117

Visual Importer Professional & Enterprise User Manual

SQL Data Check

#ote: Sql Data Check uses lookups to check data in the database. For example user may write following sql Select count(*) from table Execution will be successful if the value of first field of lookup query is more than 0 Execution will fail if the value of first field of lookup query is equals to 0 or less than 0 or field type is not numeric.

Copyright © 2009 DB Software Laboratory

Page 89 of 117

Visual Importer Professional & Enterprise User Manual

Working with filenames and directories Consider the following scenarios. • •

Every day we export data from our database and would like to save it into different file using current date as a part of the file. We would like to load yesterday’s data from the folder which has date part in the name

We can use functions in filenames to do it as follows

#ote: Only one {} pair is allowed. GETSYSTEMVARIABLE('SYSTEM_DATE') will return current date in ‘YYYYMMDDHHNNSS’ format If we want only part of date we can use LeftString(String,Count):String for example LeftString(GETSYSTEMVARIABLE('SYSTEM_DATE'),8) will return only date part. Now more complicated example for loading yesterday’s data; LeftString(DecDateS(GETSYSTEMVARIABLE('SYSTEM_DATE'),'YYYYMMDDHH NNSS', 'DAY',1),8) #ote: For complete reference of available functions consult chapter 16.

Copyright © 2009 DB Software Laboratory

Page 90 of 117

Visual Importer Professional & Enterprise User Manual

13. Scheduler Once Package is created Scheduler allows user to execute it on a regular basis. Package may be executed once, daily, weekly, or monthly. User may also specify day of the week or month when to execute the Package. .

Copyright © 2009 DB Software Laboratory

Page 91 of 117

Visual Importer Professional & Enterprise User Manual

Advanced Schedule Options allows you to define execution boundaries.

Copyright © 2009 DB Software Laboratory

Page 92 of 117

Visual Importer Professional & Enterprise User Manual

User may specify day of the week when to execute the batch. User must specify at least one day of the week.

Copyright © 2009 DB Software Laboratory

Page 93 of 117

Visual Importer Professional & Enterprise User Manual

User may specify month when to execute the batch. User must specify at least one month.

Copyright © 2009 DB Software Laboratory

Page 94 of 117

Visual Importer Professional & Enterprise User Manual

14. Execution Monitor Once Package is running or completed Execution Monitor screen allows checking status or troubleshooting if any error happens. Package may have four different statuses: • Executing • Submitted • Failed • Finished Log screen consists of two panels. Top panel shows overall status of the Package execution.

Double click on the top panel to see the Package log.

Copyright © 2009 DB Software Laboratory

Page 95 of 117

Visual Importer Professional & Enterprise User Manual

Bottom panel shows status of individual items within the Package. Double click on Bottom panel to check item log.

Copyright © 2009 DB Software Laboratory

Page 96 of 117

Visual Importer Professional & Enterprise User Manual

Tool bar

1

2 3

4 5

6

7

8

9 10 11 12 13

14

1. Shows Log dialog 2. Refreshes the screen 3. Deletes the record 4. Prints 5. Previews Grid 6. Find Record 7. Export Grid 8. Show/Hide Fields 9. Delete all records from the log 10. Stops Execution 11. Show/Hides bottom panel 12. Refresh log every minute 13. Open directory containing log files 14. Open Package Stopping execution User can stop execution at any time by pressing

.

#ote for stopping SQL scripts SQL script stops once execution of current SQL statement is finished. It could take some time to do.

Copyright © 2009 DB Software Laboratory

Page 97 of 117

Visual Importer Professional & Enterprise User Manual

15. SQL SQL Screen is a tool to run a free hand SQL or SQL scripts against the target databases.

SQL Toolbar 1 2 3 4 1. 2. 3. 4.

Show/hide SQL Statement Show/hide connection panel Show/hide log panel Show System Tables

Copyright © 2009 DB Software Laboratory

Page 98 of 117

Visual Importer Professional & Enterprise User Manual

Side toolbar

1. Previous SQL Statement 2. Next SQL Statement 3. Select SQL Statement to execute

Copyright © 2009 DB Software Laboratory

Page 99 of 117

Visual Importer Professional & Enterprise User Manual

16. Import Functions String Functions

Trim Trim(String):string Trims leading and trailing spaces from a string.

TrimLeft TrimLeft(String):string Trims leading spaces from a string.

TrimRight TrimRight(String):string Trims trailing spaces from a string.

UpperCase UpperCase(String):string UpperCase returns a string with the same text as the string passed in, but with all letters converted to Uppercase

LowerCase LowerCase(String):string LowerCase returns a string with the same text as the string passed in, but with all letters converted to LowerCase

Replace Replace(String,OldPattern,NewPattern):string Replace replaces all occurrences of the OldPattern by NewPattern within the String

SubString SubString(String,Index,Count):string SubString returns a substring containing Count characters or elements starting from Index.

RightString RightString(String,Count):string Copyright © 2009 DB Software Laboratory

Page 100 of 117

Visual Importer Professional & Enterprise User Manual

RightString returns the trailing characters of String up to a length of Count characters

Copyright © 2009 DB Software Laboratory

Page 101 of 117

Visual Importer Professional & Enterprise User Manual

LeftString LeftString(String,Count):string LeftString returns the leading characters of String up to a length of Count characters

AddCharLeft AddCharLeft(Char,String,Count):string AddCharLeft returns a string left-padded to Length with characters Char

AddCharRight AddCharRight(Char,String,Count):string AddCharRight returns a string right-padded to Length with characters Char

MakeString MakeString(Char,Count):string MakeString returns a string of Count filled with character Char.

DelSpaces DelSpaces(String):string DelSpaces returns string with all spaces deleted except one. "two spaces"->"two spaces

Delete Delete(String,Index,Count):String DelSpaces returns string with count characters deleted starting from index.

Insert Insert(Source,S,Index):String Insert returns string with s string inserted in index.

ProperCase ProperCase(String):string ProperCase returns string, with the first letter of each word in uppercase and all other letters in lowercase "proper case"->"Proper Case"

Copyright © 2009 DB Software Laboratory

Page 102 of 117

Visual Importer Professional & Enterprise User Manual

Numeric Functions

Abs Abs(Integer):Integer Abs returns the absolute value of the argument

Round Round(Float,Integer):Float Use Round to round Value to a specified power of ten. The following examples illustrate the use of Round: Expression

Value

Round(1234567, 3) Round(1.234, -2) Round(1.235, -2) Round(1.245, -2)

1234000 1.23 1.24 1.24

Sign Sign(Integer):Integer Use Sign to test the sign of a numeric value. Sign returns 0 if AValue is zero. 1 if AValue is greater than zero. -1 if AValue is less than zero.

Copyright © 2009 DB Software Laboratory

Page 103 of 117

Visual Importer Professional & Enterprise User Manual

Date Functions

Day Day(Date,Format):Integer. Use Day to get the day part of a date value. Day('01012003','DDMMYYYY')

Hour Hour(Date,Format):Integer. Use Hour to get the hour part of a date value. Hour('01012003','DDMMYYYY')

Minute Minute(Date,Format):Integer. Use Minute to get the minute part of a date value. Minute('01012003','DDMMYYYY')

Month Month(Date,Format):Integer. Use Month to get the month part of a date value. Month('01012003','DDMMYYYY')

Second Second(Date,Format):Integer. Use Second to get the second part of a date value. Second('01012003','DDMMYYYY')

Year Year(Date,Format):Integer. Use Year to get the year part of a date value. Year('01012003','DDMMYYYY') Copyright © 2009 DB Software Laboratory

Page 104 of 117

Visual Importer Professional & Enterprise User Manual

DayS DayS(Date,Format):String. Use DayS to get the day part of a date value as string. DayS('01012003','DDMMYYYY')

HourS HourS(Date,Format):String. Use HourS to get the hour part of a date value as string. HourS('01012003','DDMMYYYY')

MinuteS MinuteS(Date,Format):String. Use MinuteS to get the minute part of a date value as string. MinuteS('01012003','DDMMYYYY')

MonthS MonthS(Date,Format):String. Use MonthS to get the month part of a date value as string. MonthS('01012003','DDMMYYYY')

SecondS SecondS(Date,Format):String. Use SecondS to get the second part of a date value as string. SecondS('01012003','DDMMYYYY')

YearS YearS(Date,Format):String. Use YearS to get the year part of a date value as string. YearS('01012003','DDMMYYYY')

Copyright © 2009 DB Software Laboratory

Page 105 of 117

Visual Importer Professional & Enterprise User Manual

IncDateS IncDateS(Date,Format,ChangeType,Increment):String. ChangeType: YEAR,MONTH,WEEK,DAY,HOUR,MINUTE,SECOND Use IncDateS to Increase ChangeType part of a date value by an Increment. IncDateS ('01012003','DDMMYYYY', 'YEAR',1)

DecDateS DecDateS(Date,Format,ChangeType,Decrement):String. ChangeType: YEAR,MONTH,WEEK,DAY,HOUR,MINUTE,SECOND Use DecDateS to Decrease ChangeType part of a date value by an Decrement. DecDateS ('01012003','DDMMYYYY', 'YEAR',1)

Copyright © 2009 DB Software Laboratory

Page 106 of 117

Visual Importer Professional & Enterprise User Manual

Conversion Functions

IntegerToString IntegerToString(Integer):String IntegerToString converts integer value to string value.

NumberToString NumberToString(Float):String NumberToString converts float value to string value.

StringToInteger StringToInteger(String):Integer StringToInteger converts string value to integer value.

StringToNumber StringToNumber(String):Float StringToNumber converts string value to float value.

Copyright © 2009 DB Software Laboratory

Page 107 of 117

Visual Importer Professional & Enterprise User Manual

Miscellaneous Functions

Iif Iif(expr1==expr2,expr3,expr4) Iif function returns expr3 or expr4 depending on expr1==expr2

GetSystemVariable GetSystemVariable('VARIABLENAME'):string GetSystemVariable returns value of 'VARIABLENAME'. Possible values for 'VARIABLENAME' are: COMPUTERNAME, OSUSERNAME, DBUSERNAME, BLOCKNUMBER, LINENUMBER, RECORDNUMBER, SYSTEM_DATE SOURCE_TABLE_NAME SOURCE_FILE_NAME

GetSystemDate GetSystemDate(Format):String Returns Current system date/time in format specified GetSystemDate('MMDDYYYY')

Pos Pos(Substr,String): Integer Pos searches for Substr within String and returns an integer value that is the index of the first character of Substr within String. Pos is case-sensitive. If Substr is not found, Pos returns zero.

Copyright © 2009 DB Software Laboratory

Page 108 of 117

Visual Importer Professional & Enterprise User Manual

17. Date formats Date/Time format strings control the conversion of strings into date time type. Date Time Format Strings are composed from specifies that describe values to be converted into the date time value. In the following table, specifies are given in lower case. Case is ignored in formats, except for the "am/pm" and "a/p" specifies. Specifier d dd

Description Day as a number without a leading zero (1-31). Day as a number with a leading zero (01-31).

m mm mmm mmmm

Month as a number without a leading zero (1-12). Month as a number with a leading zero (01-12). Month as an abbreviation (Jan-Dec). Month as a full name (January-December).

yy yyyy

Year as a two-digit number (00-99). Year as a four-digit number (0000-9999).

h hh

Hour without a leading zero (0-23). Hour with a leading zero (00-23).

n nn

Minute without a leading zero (0-59). Minute with a leading zero (00-59).

s ss

Second without a leading zero (0-59). Second with a leading zero (00-59).

fff

Fraction of Second with a leading zero (000-999).

tt Uses the 12-hour clock for the preceding h or hh specifier, 'am' for any hour before noon, and 'pm' for any hour after noon. Important thing is to understand that this format has nothing to do with your target database. This is the format of the source data. It is there to help to covert string into date time type inside of the software, so it can be loaded later into date or timestamp field So if source data is: 16/08/2009 than the format is DD/MM/YYYY 1/31/2009 than the format is M/D/YYYY 2006-05-23 22:34:42.096 than the format is YYYY-MM-DD HH:NN:SS.FFF 1992/mar/12 00:00 than the format is YYYY/MMM/DD HH:NN Copyright © 2009 DB Software Laboratory

Page 109 of 117

Visual Importer Professional & Enterprise User Manual

18. Execution Agent Execution Agent is Windows NT service which allows user to schedule and execute Visual Importer packages.

Tick it to allow agent to execute packages

#ote: See 13 how to schedule packages for execution and 14 how to use execution monitor. To install the Visual Importer Enterprise Execution Agent as a service run following command VImpAgent.EXE /INSTALL To uninstall the Visual Importer Enterprise Execution Agent as a Windows NT/2000 service run the Visual Importer Enterprise Execution Agent with the /UNINSTALL switch as follows VImpAgent.EXE /UNINSTALL

Copyright © 2009 DB Software Laboratory

Page 110 of 117

Visual Importer Professional & Enterprise User Manual

19. Command Line To run a package from the command line type vpr.exe package id.

Package ID

Copyright © 2009 DB Software Laboratory

Page 111 of 117

Visual Importer Professional & Enterprise User Manual

Parallel Execution Another benefit of using vpr.exe that the combination of Application Action and vpr makes it possible to execute several packages in parallel. See the pictures below.

Copyright © 2009 DB Software Laboratory

Page 112 of 117

Visual Importer Professional & Enterprise User Manual

20. Repository Synchronisation Repository Synchronisation wizard helps to copy objects between development testing and production environment. When package or any other object selected for synchronisation all related objects are selected as well. For example if you select an SQL Script a database connection for this script automatically selected. Once all the objects are selected there are three options available you can add all of them to target repository, Update Existing objects or Use Existing objects. For example if you want to copy an SQL script to the production repository but use the connection which already exists in the production repository You need to do the following Set "Use Existing" for the database connection and map it to the connection in the production repository Set "Add new" for the SQL Script.

Copyright © 2009 DB Software Laboratory

Page 113 of 117

Visual Importer Professional & Enterprise User Manual

21. Support Procedure We welcome your suggestions and comments to make our software better. Please do contact us through email or visit our website For General Sales and License Queries Email to: [email protected] For Technical Help Support Forum: http://www.dbsoftlab.com/index.php/Support-Forum/ Email To: [email protected] Online Contact Form: http://www.dbsoftlab.com/index.php/Contact-Us/ While submitting a bug or problem please include the following to make it easier to solve the problem as soon as possible: • • • • • • • • •

All the symptoms of a Problem Exact steps required to reproduce it Version number Operation system version Database version Log files Script Sql script to create tables Screenshots

We intend to resolve 90 percent of the reported problems with in 48 hours. Most difficult issues or functionality extension are resolved with in one week

Copyright © 2009 DB Software Laboratory

Page 114 of 117

Visual Importer Professional & Enterprise User Manual

22. License Agreement Visual Importer by DB Software Laboratory www.dbsoftlab.com [email protected] END-USER LICENSE AGREEMENT FOR THIS SOFTWARE IMPORTANT - READ CAREFULLY: This End-User License Agreement ("EULA") is a legal agreement between you (either an individual or a single entity) and DB Software Laboratory for the SOFTWARE PRODUCT identified above, which includes computer software and may include associated media, printed materials, and "online" or electronic documentation. By installing, copying, or otherwise using the SOFTWARE PRODUCT, you agree to be bound by the terms of this EULA. If you do not agree to the terms of this EULA, you may be subject to civil liability if you install and use this SOFTWARE PRODUCT. SOFTWARE PRODUCT LICENSE The SOFTWARE PRODUCT is protected by copyright laws and international copyright treaties, as well as other intellectual property laws and treaties. The SOFTWARE PRODUCT is licensed, not sold. INSTALLATION AND USAGE Once SOFTWARE PRODUCT is installed you may use it for 30 days. After evaluation period ends, you have to purchase a license or stop using the SOFTWARE PRODUCT. If this is an EVALUATION VERSION of the SOFTWARE PRODUCT, you may copy and distribute an unlimited number of copies of the SOFTWARE PRODUCT; provided that each copy shall be a true and complete copy, including all copyright and trademark notices, and shall be accompanied by a copy of this EULA. If this is a REGISTERED VERSION of the SOFTWARE PRODUCT, you may install and use it for your personal use only. You may not reproduce or distribute the SOFTWARE PRODUCT for use by others.

Copyright © 2009 DB Software Laboratory

Page 115 of 117

Visual Importer Professional & Enterprise User Manual

LICENSING There are two types of licenses available 1. A single computer usage license. The user purchases one license to use the SOFTWARE PRODUCT on one computer.

2. A SITE usage license. The user purchases a single usage license, authorising the use of SOFTWARE PRODUCT, by the purchaser, the purchaser's employees or accredited agents, on an unlimited number of computers at the same physical site location. This site location would normally be defined as a single building, but could be considered to be a number of buildings within the same, general, geographical location, such as an industrial estate or small town. OTHER RIGHTS AND LIMITATIONS You may not reverse engineer, decompile, or disassemble the SOFTWARE PRODUCT, except and only to the extent that such activity is expressly permitted by applicable law notwithstanding this limitation. Any such unauthorized use shall result in immediate and automatic termination of this license and may result in criminal and/or civil prosecution. All rights not expressly granted here are reserved by DB Software Laboratory. The SOFTWARE PRODUCT is licensed as a single product. Its component parts may not be separated for use on more than one computer. You may permanently transfer all of your rights under this EULA, provided the recipient agrees to the terms of this EULA. SEVERABILITY In the event of invalidity of any provision of this license, the parties agree that such invalidity shall not affect the validity of the remaining portions of this license. COPYRIGHT The SOFTWARE PRODUCT is protected by copyright laws and international treaty provisions. All title and copyrights related to the SOFTWARE PRODUCT (including but not limited to any images, photographs, animations, video, audio, music, text, and "applets" incorporated into the SOFTWARE PRODUCT), the accompanying printed materials, and any copies of the SOFTWARE PRODUCT are owned by DB Software Laboratory. MISCELLANEOUS

Copyright © 2009 DB Software Laboratory

Page 116 of 117

Visual Importer Professional & Enterprise User Manual

Should you have any questions concerning this EULA, or if you desire to contact the author of this Software for any reason, please contact DB Software Laboratory (see contact information at the top of this EULA). LIMITED WARRANTY DB Software Laboratory expressly disclaims any warranty for the SOFTWARE PRODUCT. The SOFTWARE PRODUCT and any related documentation is provided "as is" without warranty of any kind, either express or implied, including, without limitation, the implied warranties or merchantability, fitness for a particular purpose, or no infringement. The entire risk arising out of use or performance of the SOFTWARE PRODUCT remains with you. In no event shall DB Software Laboratory be liable for any damages whatsoever or refund any money (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or any other pecuniary loss) arising out of the use of or inability to use this product, even if DB Software Laboratory has been advised of the possibility of such damages. Because some states/jurisdictions do not allow the exclusion or limitation of liability for consequential or incidental damages, the above limitation may not apply to you. SUPPORT AND MAINTENACE The annual maintenance fee is 20 percent of initial software license cost. Users with a fully paid annual maintenance fee get the following benefits: Priority Support Free software enhancements, updates and upgrades during the maintenance period Advanced and exclusive notification of software promotions "Maintenance Owner ONLY" product promotions ENTIRE AGREEMENT This is the entire agreement between you and DB Software Laboratory which supersedes any prior agreement or understanding, whether written or oral, relating to the subject matter of this license. Thank you for using the Visual Importer. DB Software Laboratory www.dbsoftlab.com [email protected]

Copyright © 2009 DB Software Laboratory

Page 117 of 117