NOTE! This site uses cookies and similar technologies.

If you not change browser settings, you agree to it. Learn more

I understand

Advanced ETL Processor - FAQ

1. What is Advanced ETL Processor?

Answer: Advanced ETL Processor is designed to automate extracting data from ANY database, transform, validate it and load into ANY database . Typical usage of it would be extract data from Excel File,Validate Date Formats, Sort data, deduplicate it and load it into Oracle database, run stored procedure or Sql script, once loading is completed. Unlike Oracle SQL loader, BCP,  DTS or SSIS Advanced ETL Processor can also add new and update old records based on primary key.

2. What does ETL stands for?

Answer: Think of it as housecleaning for your data. ETL stands for extract, transform, and load. ETL is software that enables businesses to consolidate their disparate data while moving it from place to place, and it doesn't really matter that that data is in different forms or formats. The data can come from any source. ETL is powerful enough to handle such data disparities.

For example, a financial institution might have information on a customer in several departments and each department might have that customer's information listed in a different way. The membership department might list the customer by name, whereas the accounting department might list the customer by number. ETL can bundle all this data and consolidate it into a uniform presentation, such as for storing in a database or data warehouse.

3. What are the requirements for Advanced ETL Processor installation? 

 Answer:

 Software Version Notes
 Microsoft Windows  W2K or higher    Bothh 32 bit and 64 bit versions are supported
 MDAC  2.6 or higher  Part of OS on W2K,
XP, Vista,Win7 and Win8.
 
 MS Access ODBC
driver
 4.00.6364.00 or
higher
Part of OS on W2K,
XP, Vista,Win7 and Win8.
 Only to work with MS Access Databases
MS Access 2007 ODBC driver 12.00.4518.1014 or
higher
Separate download Only to work with MS Access 2007/2013 Databases
FoxPro ODBC driver 6.1.8629.1 or higher Separate download Only to work with DBF/FoxPro Files
SQL ODBC driver 2000.81.9041.40 or higher Part of OS on W2K,
XP, Vista,Win7 and Win8.
Only to work with MS SQL Server 7/2014
Interbase client   GDS32.DLL Only to work with Interbase or Firebird Databases
SQLite   Sqlite3.dll Only to work with SQLite databases
Oracle Client 7.3.4 or higher Provided by Oracle Only to work with Oracle Databases
BDE Drivers Latest Version   Only to work with Paradox or other BDE databases
Jet 4.0 Service pack 5 or higher Part of OS on W2K,
XP, Vista,Win7 and Win8.
To connect to MS Access repository
Microsoft SQL Server Compact     To connect to Microsoft SQL Server Compact repository 

Separate Downloads:

FoxPro ODBC driver

http://msdn.microsoft.com/en-us/vfoxpro/bb190233.aspx

Microsoft Access Database Engine 2010 Redistributable

http://www.microsoft.com/en-us/download/details.aspx?id=13255

BDE

www.etl-tools.com/dmdocuments/BDE.zip

Working with Oracle:

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

Repository Scripts

Repository Creation scripts are provided for the following databases:

  1. MS Access
  2. Oracle
  3. MS Sql Server
  4. Interbase/Firebird
  5. MySQL
  6. PostgreSQL

Notes:

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

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 as they are integrated into the software itself.

Only 32 bits drivers and clients can be used

4. Which Data sources are supported?

Answer: Advanced ETL Processor works directly with following data sources

  • Multiple Delimited or Fixed width Text files
  • Multiple Excel files + Multiple Excel Spreadsheets
  • Multiple MS Access Databases
  • Multiple Foxpro/DBF Files
  • Oracle
  • MS SQL Server 6.5, 7.0, 2000, 2005, 2008, CE, Express
  • Firebird/Interbase
  • MySQL
  • PostgreSQL
  • Any ODBC source
  • Any JDBC source
  • OLE DB
  • POP3
  • IMAP4
  • XML Files
  • RSS
  • QVX/QVD Files
  • Google Spreadsheet
  • Sales Force
  • BrightPearl
  • HL7
  • BDE
  • HTML Forms Processing
  • File System 

5. Which target databases are supported?

Answer: Advanced ETL Processor works directly with following data targets

  • Multiple Data Targets
  • Sql scripts execution before and after loading
  • Delimited or Fixed width Text file
  • MS Access Database
  • Microsoft Excel File
  • DBF/Foxpro File
  • Oracle database
  • MS SQL Server 6.5, 7.0, 2000, 2005, 2008, CE, Express
  • MySql
  • PostgreSQL
  • Firebird/Interbase
  • Any ODBC compliant database
  • OLE DB
  • SMTP Server
  • File System
  • XML Files
  • HL7
  • BDE
  • QVX/QVD Files
  • Insert Scripts

Oracle

There are two ways of loading available:

  • Oracle direct path loading
  • Conventional path

SQL server

This software uses the same API as Microsoft DTS service.

6. What about data validation?

Answer: Latest version of Advanced ETL Processor has 191+ Validation Functions including String Validation, Number Validation, Date Validation, Time Validation, Validation Against list of values, Regular Expressions: Post codes, Phone Numbers, VAT Numbers ETC.
In fact you can create your own validation rules using regular expressions

7. What kind of data transformations is supported?

Answer: There are currently 200+ Data Transformation Functions such as String Transformation, Number Transformation, Date Transformation.
It is also possible write your own transformations

8. Is it possible to sort and group data?

Answer: Yes, Advanced ETL Processor supports Sorting, Grouping, Deduplication,
Translating coded values (e.g., if the source system stores 1 for male and 2 for female, but the warehouse stores M for male and F for female)
Deriving a new calculated value (e.g., sale amount = qty * unit price)
Joining together data from multiple fields
Summarizing multiple rows of data (e.g., total sales for each store, and for each region)
Generating surrogate key values
Transposing or pivoting (turning multiple columns into multiple rows or vice versa)
Splitting a column into multiple columns (e.g., putting a comma-separated list specified as a string in one column as individual values in different columns)

9. Is it possible to load data from one file into two different tables?

Answer: Yes, it is possible.

10. What is the quickest way to learn how to use Advanced ETL Processor.

Answer: We have number of on-line ETL tutorials designed to get you started fast, plus we have pdf documentation (316 pages).

11. Can I use Advanced ETL Processor. to transfer data from one Oracle Version to Another Oracle Version?

Answer: Yes, you can use Advanced ETL Processor to load data from one Oracle version to another Oracle Version.

12. Is there any limit on number of rows, which Advanced ETL Processor. can process?

Answer: No, Advanced ETL Processor can be used to load millions of rows.  There are also no limitations in The Trail version. In fact our ETL software was optimised to use minimum amount of memory with maximum performance

13. Is there any way for filtering source records?

Answer: Yes, you can filter rows while loading from MySQL, Oracle, MS-Access, MS-SQL Server, Foxpro Database source to any other Target Database or Text file.

You can write where clause or you can use Advanced ETL Processor validation rules .

14. I have several Files with same structure is it possible to load them in one go?

Answer: Yes, Advanced ETL Processor can load data from multiple text files, tables, MS Access Databases and Excel Files

15. I am planning to load data from table with several millions of rows. I tried several tools and noticed that they will use all the memory and eventually fail. Is there such problem with Advanced ETL Processor?.

Answer: No, Amount of memory used by Advanced ETL Processor does not depend on number of records processed.

16. Is it possible to truncate table before loading data into it?

Answer: Yes you can run a sql script before and after loading data.

17. Is it possible to add data into existing text file?

Answer: Yes you can specify weather you want to add data to the existing file or overwrite it.

18. Is there any way to run Advanced ETL Processor script from the command line?

Answer: Yes,  it is possible, just run AdvancedETL.exe yourscriptname

19. What kind of Date Formats are supported?

Answer: Date Formats in Advanced ETL Processor are user definable, for more information please consult user manual.

20. Is it possible to run customised data transformation in Advanced ETL Processor?

Answer: Date Formats in Advanced ETL Processor are user definable, for more information please see the list of date formats supported.

21. How do I resolve data quality issues in Advanced ETL Processor?

Answer: Advanced ETL Processor generates sophisticated transformation log and rejected records file.

22. Is it possible to update data using Advanced ETL Processor?

Answer: Yes, Advanced ETL Processor can update, delete and insert data based on update key, for more information please consult user manual.

23. Is it possible to load data from pivot tables using Advanced ETL Processor?

Answer: Yes, Advanced ETL Processor can load data from pivot tables, for more information please consult user manual.

24. Does Advanced ETL Processor work on Windows Vista and Windows 7?

Answer: Yes, It does.

25. I want to transform  XML/CSV/MSSQL/MYSQL into MYSQL, What edition do i need (for dev and production)?

Advanced ETL Processor Professional is a recommended product for development environment
Advanced ETL Processor Enterprise for production

26. When transferring data from MSSQL/MYSQL into MYSQL I want to be able to get only the changes. my database is about 3 GB each table. I want to make sure that i can sync only changes and not get over the internet the entire table each sync 

Answer: If you have "last_modification_datetime" field in your tables you should be able to do it

27. What kind of support do you provide rather than the forums?

Answer: We will do everything we can to assist you if you have a problem. We will connect remotely if necessary

28. What is the difference between standard license and site license?

Answer:

  • Standard license = one user.
  • Site license = unlimited number of users within one or group of buildings within one city

29. I have number of  customer's photos I want to load into the database, can I do it with Advanced ETL Processor?

Answer: Yes, you can. Advanced ETL Processor can use file system as data source and data target. This one ot the unique benefits of using our software. Enormous amout of testing was done to make it work.

30. Can I use Advanced ETL Processor to generate Insert statements?

Answer: Yes, you can. In fact you can use any data source to generate insert statements including text files

31. DTS and SSIS do not support secure FTP (FTPS), what about your ETL software?

Answer: Yes, It does support secure FTP and it also supports secure Email.

32. What is the best way to learn ETL software?

Answer: Using online ETL flash manuals is the best way to learn

33. I have deadline tomorrowm can someone help?

Answer: Please contact us and we will do our best to assist you.

Visit Support Forum Learn More Download Buy It
"The DBSL Integration solution eliminated our data access bottle neck that previously impeded company growth. We are now able to provide solutions to long standing problems areas such as automated order processing and business reporting limitations. Additionally the solution allows for new opportunities to simply hook-on to our existing data sources. From development through testing the DBSL support  team continues to be helpful, resourceful and responsive to our company needs."

John Kil,
IT Manager

Our customers

BP

BBC

HSBC

Orange

CAT

SEPA
emc
NHS
Australian Rugby Union
xerox
Databases we work with