advanced etl processor professional user manual

Advanced ETL Processor

Pro&Ent

User manual

Copyright DB Software Laboratory 2008-2012

www.etl-tools.com

Advanced ETL Processor Professional & Enterprise User Manual

Copyright 2012 DB Software Laboratory Page 2 of 418

Contents

1. Introduction ........................................................................................................................... 13

2. What can Advanced ETL Processor do? ............................................................................ 15

2.1 Case Study: Automatic Processing of Financial Data ..................................................... 17

3. Requirements ......................................................................................................................... 19

4. Key features ........................................................................................................................... 21

4.1 Extraction Process ........................................................................................................... 21

4.1.1 Multiple Data Formats .............................................................................................. 21

4.1.2 Multiple Databases and Table Processing ................................................................ 21

4.1.3 Other Database Features ........................................................................................... 21

4.2 Summary of the Extraction Process: ............................................................................... 22

4.3 Validation Process ........................................................................................................... 22

4.4 Summary of Validation Processes: .................................................................................. 23

4.5 Transformation Process ................................................................................................... 23

4.6 Powerful Data Transformation ........................................................................................ 23

4.7 Summary of Transformation Processes: .......................................................................... 24

4.8 Loading Process ............................................................................................................... 25

4.9 Summary of the Loading Process: ................................................................................... 25

4.10 Automation Process ....................................................................................................... 27

4.11 Summary of the Automation Processes: ........................................................................ 27

5. Advanced ETL Processor Architecture .............................................................................. 28

6. Demo Data ............................................................................................................................. 30

7. Options .................................................................................................................................. 32

8. User Interface ........................................................................................................................ 37

8.1 System Menu ................................................................................................................... 37

8.2 Advanced ETL Processor Graphical User Interface: Main window ............................... 37

8.2.1 Quick Access Tabs ................................................................................................... 37

8.2.2 Objects tree toolbar ................................................................................................... 38

8.2.3 Objects tree description ............................................................................................ 38

8.2.4 Objects tree list ......................................................................................................... 39

8.2.5 Objects Groups ......................................................................................................... 41

9. Directories and Connections .................................................................................................. 44

9.1 Directories ....................................................................................................................... 44

9.2 Database Connections ..................................................................................................... 45

9.2.1 Oracle Connection .................................................................................................... 46

9.2.2 JDBC Connection ..................................................................................................... 47

9.2.3 Microsoft SQL Server Connection ........................................................................... 48

9.2.4 ODBC Connections .................................................................................................. 49

9.2.5 ODBC Connection Strings ....................................................................................... 51

9.2.6 MySQL Connections ................................................................................................ 52

9.2.7 PostgreSQL Connection ........................................................................................... 54

9.2.8 Interbase/Firebird Connection .................................................................................. 56

9.2.9 Ole DB Connection .................................................................................................. 58

9.2.10 SQLite Connection ................................................................................................. 59

9.2.11 Microsoft SQL Server CE Connection ................................................................... 60

9.3 POP3 Connection ............................................................................................................ 61

9.4 IMAP4 Connection .......................................................................................................... 62

9.5 SMTP Connection ........................................................................................................... 63



9.6 IP Socket connection ....................................................................................................... 64

9.7 FTP Connection ............................................................................................................... 65

9.8 Google Spread sheet connection...................................................................................... 68

9.9 SalesForce connection ..................................................................................................... 69

9.10 BrightPearl connection ................................................................................................. 70

10. Processing Data ................................................................................................................... 71

10.1 Screen Overview ........................................................................................................... 71

10.2 Transformation Properties ............................................................................................. 73

10.3 Template tab .................................................................................................................. 74

10.4 Execution Log Tab ........................................................................................................ 75

10.5 Rejected Records Tab .................................................................................................... 76

10.6 Creating new transformation and working with Objects ............................................... 77

10.7 Working with Reader .................................................................................................... 79

10.7.1 Universal Data Reader ............................................................................................ 79

10.7.2 Data source is a Text File. ...................................................................................... 80

10.7.3 Data source is a XML file ....................................................................................... 86

10.7.4 Data source is Excel file ......................................................................................... 88

10.7.5 Data source is Qvx file ........................................................................................... 90

10.7.6 Data source is Windows Event Log ....................................................................... 91

10.7.7 Data source is MS Access ...................................................................................... 92

10.7.8 Data source is a DBF File ....................................................................................... 99

10.7.9 Data source is a ODBC Connection ..................................................................... 102

10.7.10 Data source is a JDBC Connection .................................................................... 106

10.7.11 Data source is an Ole DB Connection ................................................................ 107

10.7.12 Data source is a MS SQL Server ........................................................................ 108

10.7.13 Data source is MS SQL Server CE ..................................................................... 109

10.7.14 Data source is an Oracle Database ..................................................................... 110

10.7.15 Data source is an MySql ..................................................................................... 111

10.7.16 Data source is an PostgreSQL Database ............................................................. 112

10.7.17 Data source is an Interbase or Firebird Database ............................................... 113

10.7.18 Data source is SQLIte ......................................................................................... 114

10.7.19 Data source is a File System ............................................................................... 115

10.7.20 Data source is a POP3 Server ............................................................................. 116

10.7.21 Data source is a IMAP4 Server .......................................................................... 117

10.7.22 Data source is a TCP/IP Server .......................................................................... 118

10.7.23 Data source is Google Spread Sheets ................................................................. 119

10.7.24 Data source is RSS feed ..................................................................................... 120

10.7.25 Data source is SalesForce ................................................................................... 121

10.7.26 Data source is BrightPearl .................................................................................. 122

10.8 Working with Validator ............................................................................................... 125

10.8.1 Debugging Validation........................................................................................... 128

10.9 Working with Transformer .......................................................................................... 131

10.9.1 Auto mapping ....................................................................................................... 134

10.10 Working with Grouper .............................................................................................. 135

10.11 Working with Sorter .................................................................................................. 136

10.12 Working with Deduplicator ....................................................................................... 138

10.13 Working with UnPivot .............................................................................................. 139

Configuring the Unpivot Transformation ........................................................................ 141

10.14 Working with Pivot ................................................................................................... 143



10.15 Working with Clone rows object ............................................................................... 148

10.16 Working with Fields Selector object ......................................................................... 149

10.17 Working with Union All object ................................................................................. 150

10.18 Working with Writer ................................................................................................. 151

10.18.1 Target type is a Text File .................................................................................... 151

10.18.2 Generating Insert Statements .............................................................................. 155

10.18.3 Target type is a XML file ................................................................................... 157

10.18.4 Target type is Excel file ...................................................................................... 158

10.18.5 Target type is QlikView Qvx File ...................................................................... 162

10.18.6 Target type is MS Access ................................................................................... 163

10.18.7 Target type is a DBF File .................................................................................... 165

10.18.8 Target type is ODBC connection ........................................................................ 167

10.18.9 Target type is Ole DB ......................................................................................... 168

10.18.10 Target type is SQLIte ........................................................................................ 169

10.18.11 Target type is MS SQL Server .......................................................................... 170

10.18.12 Target type is MS SQL Server CE ................................................................... 172

10.18.13 Target type is Oracle ......................................................................................... 173

10.18.14 Target type is MySql ........................................................................................ 174

10.18.15 Target type is PostgreSQL ................................................................................ 175

10.18.16 Target type is Interbase/Firebird ....................................................................... 176

10.18.17 Target type is File System ................................................................................ 177

10.18.18 Target type is SMTP server .............................................................................. 179

10.18.19 Target type is IP socket ..................................................................................... 180

10.19 How to Update/Delete Records ................................................................................. 181

10.20 Generating primary keys and defaults ....................................................................... 183

10.21 Running SQL Scripts ................................................................................................. 184

11. Validation Rules ................................................................................................................ 186

11.1 Strings .......................................................................................................................... 187

11.1.1 Is Null ................................................................................................................... 187

11.1.2 Is Empty String ..................................................................................................... 187

11.1.3 Is Alpha ................................................................................................................ 187

11.1.4 Is Alpha Numeric.................................................................................................. 187

11.1.5 Is Hex .................................................................................................................... 188

11.1.6 Is Equal To ........................................................................................................... 188

11.1.7 Is Starts With ........................................................................................................ 189

11.1.8 Is Ends With ......................................................................................................... 189

11.1.9 Contains ................................................................................................................ 189

11.1.10 In List .................................................................................................................. 189

11.2 Numbers ...................................................................................................................... 193

11.2.1 Is Number ............................................................................................................. 193

11.2.2 Is Integer ............................................................................................................... 193

11.2.3 Is Positive ............................................................................................................. 193

11.2.4 Is Negative ............................................................................................................ 193

11.2.5 Is Number Between .............................................................................................. 194

11.2.6 Is Number Less Than ............................................................................................ 195

11.2.7 Is Number More Than .......................................................................................... 196

11.3 Date.............................................................................................................................. 196

11.3.1 Is Date ................................................................................................................... 196

11.3.2 Is Date Between .................................................................................................... 198



11.3.3 Is Date Less Than ................................................................................................. 199

11.3.4 Is Date More Than ................................................................................................ 199

11.3.5 Is Monday, Is Tuesday, Is Wednesday, Is Thursday, Is Friday, Is Saturday, Is

Sunday, Is Today, Is Yesterday, Is Tomorrow, Is Weekend, Is Weekday ................... 200

11.3.6 Is January, Is February, Is March, Is April, Is May, Is June, Is July, Is August, Is

September, Is October, Is November, Is December, Is Current Month, Is Last Month, Is

Next Month ...................................................................................................................... 200

11.3.7 Is 1st Quarter, Is 2nd Quarter, Is 3rd Quarter, Is 4th Quarter, Is Current Quarter, Is

Last Quarter, Is Next Quarter .......................................................................................... 201

11.3.8 Is Leap Year, Is Current Year, Is Last Year, Is Next Year ................................... 201

11.3.9 Is Current Week, Is Last Week, Is Next Week ..................................................... 201

11.3.10 Is Within Past Minutes, Is Within Past Hours, Is Within Past Days, Is Within Past

Weeks, Is Within Past Months ........................................................................................ 202

11.4 Time ............................................................................................................................. 203

11.4.1 Is Time .................................................................................................................. 203

11.4.2 Is Time Between ................................................................................................... 204

11.4.3 Is Time Less Than ................................................................................................ 205

11.4.4 Is Time More Than ............................................................................................... 205

11.4.5 Is Second .............................................................................................................. 206

11.4.6 Is Minute ............................................................................................................... 206

11.4.7 Is Hour 24 ............................................................................................................. 206

11.4.8 Is Hour 12 ............................................................................................................. 206

11.4.9 Is PM .................................................................................................................... 207

11.4.10 Is AM .................................................................................................................. 207

11.5 Regular Expressions .................................................................................................... 208

11.5.1 Regular Expression ............................................................................................... 209

11.5.2 Is IP Address V4 ................................................................................................... 210

11.5.3 Is IP Address V6 ................................................................................................... 210

11.5.4 Is Email ................................................................................................................. 210

11.5.5 Is ISBN 10 ............................................................................................................ 210

11.5.6 Is ISBN 13 ............................................................................................................ 211

11.5.7 Is Credit Card ....................................................................................................... 211

11.5.8 Is URL .................................................................................................................. 211

11.5.9 Is UNC .................................................................................................................. 211

11.5.10 Is File Name........................................................................................................ 212

11.5.11 Is UK Post Code ................................................................................................. 212

11.5.12 Is UK VAT Number ........................................................................................... 212

11.5.13 Is UK Bank Sort Code ........................................................................................ 212

11.5.14 Is UK Vehicle Registration Number .................................................................. 213

11.5.15 Is UK Driver Licence .......................................................................................... 213

11.5.16 Is UK National Insurance Number ..................................................................... 213

11.5.17 Is UK Phone Number ......................................................................................... 213

11.5.18 Is UK Mobile Number ........................................................................................ 214

11.5.19 Is US Zip Code ................................................................................................... 214

11.5.20 Is US Social Security Number ............................................................................ 214

11.5.21 Is US Phone Number .......................................................................................... 215

11.5.22 Is Canadian Zip Code ......................................................................................... 215

11.5.23 Is Canadian Provincial Code .............................................................................. 215

11.5.24 Is Canadian Branch-Transit/ Bank code ............................................................. 215



11.5.25 Is Canadian Social Insurance Number ................................................................ 216

11.5.26 Is Australian Post Code ...................................................................................... 216

11.5.27 Is Australian Phone Number............................................................................... 216

11.5.28 Is Australian Mobile Number ............................................................................. 217

11.5.29 Is Argentinean Post Code ................................................................................... 217

11.5.30 Is Dutch Post Code ............................................................................................. 217

11.5.31 Is Dutch Phone Number ..................................................................................... 217

11.5.32 Is Dutch Car Number Plate ................................................................................. 218

11.5.33 Is Belgian Post Code........................................................................................... 218

11.5.34 Is Belgian VAT Number ..................................................................................... 218

11.5.35 Is French Post Code ............................................................................................ 218

11.5.36 Is French VAT Number ...................................................................................... 219

11.5.37 Is French Phone Number .................................................................................... 219

11.5.38 Is German Post Code .......................................................................................... 219

11.5.39 Is German VAT Number .................................................................................... 219

11.5.40 Is German Car License Plate .............................................................................. 220

11.5.41 Is German IBAN ................................................................................................. 220

11.5.42 Is Italian Post Code ............................................................................................. 220

11.5.43 Is Italian VAT Number ....................................................................................... 220

11.5.44 Is Italian IBAN ................................................................................................... 221

11.5.45 Is Italian Phone Number ..................................................................................... 221

11.5.46 Is Italian Mobile Number ................................................................................... 221

11.5.47 Is Spanish Post Code .......................................................................................... 221

11.5.48 Is Spanish VAT Number .................................................................................... 222

11.5.49 Is Spanish IBAN ................................................................................................. 222

11.5.50 Is Spanish Phone Number .................................................................................. 222

11.5.51 Is Swedish Post Code ......................................................................................... 222

11.5.52 Is Swedish VAT Number ................................................................................... 223

11.5.53 Is Swedish IBAN ................................................................................................ 223

11.5.54 Is Swedish Phone Number .................................................................................. 223

11.5.55 Is Swedish Person Number ................................................................................. 223

11.5.56 Is Austrian Mobile Phone Number ..................................................................... 224

11.5.57 Is Danish Post Code ............................................................................................ 224

11.5.58 Is Danish VAT Number ...................................................................................... 224

11.5.60 Is Danish Phone Number .................................................................................... 224

11.5.61 Is Danish Civil Registration Number ................................................................. 225

11.5.62 Is Finnish VAT Number ..................................................................................... 225

11.5.63 Is Greek VAT Number ....................................................................................... 225

11.5.64 Is Irish VAT Number .......................................................................................... 225

11.5.65 Is Luxembourg VAT Number ............................................................................ 226

11.5.66 Is Portuguese VAT Number ............................................................................... 226

11.5.67 Is Estonian Post Code ......................................................................................... 226

11.5.68 Is Estonian VAT Number ................................................................................... 226

11.5.69 Is Latvian VAT Number ..................................................................................... 227

11.5.70 Is Lithuanian VAT Number ................................................................................ 227

11.5.71 Is Slovenian VAT Number ................................................................................. 227

11.5.72 Is Slovakian VAT Number ................................................................................. 227

I11.5.73 s Slovakian IBAN .............................................................................................. 228

11.5.74 Is Polish VAT Number ....................................................................................... 228



11.5.75 Is Polish IBAN .................................................................................................... 228

11.5.76 Is Czech Republic VAT Number........................................................................ 228

11.5.77 Is Czech Republic IBAN .................................................................................... 229

11.5.78 Is Netherlands VAT Number .............................................................................. 229

11.5.79 Is Hungarian VAT Number ................................................................................ 229

11.5.80 Is Maltese VAT Number .................................................................................... 229

11.5.81 Is Algerian Post Code ......................................................................................... 230

11.5.82 Is Andorran Post Code ........................................................................................ 230

11.5.83 Is Andorran IBAN .............................................................................................. 230

11.5.84 Is Azerbaijani Post Code .................................................................................... 230

11.5.85 Is Bahraini Post Code ......................................................................................... 231

11.5.86 Is Bangladeshi Post Code ................................................................................... 231

11.5.87 Is Belarusian Post Code ...................................................................................... 231

11.5.88 Is Brunei Post Code ............................................................................................ 231

11.5.89 Is Chilean Post Code .......................................................................................... 232

11.5.90 Is Croatian Post Code ......................................................................................... 232

11.5.91 Is Cyprus Post Code ........................................................................................... 232

11.5.92 Is Cyprus VAT Number...................................................................................... 232

11.5.93 Is Egyptian Post Code ......................................................................................... 233

11.5.94 Is Ethiopian Post Code ....................................................................................... 233

11.5.95 Is Faroe Islands Post Code .................................................................................. 233

11.5.96 Is French Polynesia Post Code ........................................................................... 233

11.5.97 Is Guadeloupe Post Code .................................................................................... 234

11.5.98 Is Guinean Post Code ......................................................................................... 234

11.5.99 Is Guinea-Bissau Post Code ............................................................................... 234

11.5.100 Is Russian Post Code ........................................................................................ 234

11.5.101 Is Russian Phone Number ................................................................................ 235

11.5.102 Is Israeli Phone Number ................................................................................... 235

11.5.103 Is Israeli Mobile Phone Number ....................................................................... 235

11.5.104 Is Peru Phone Number ...................................................................................... 235

11.5.105 Is Peru Cellular Number ................................................................................... 236

11.5.106 Is Indian Post Code ........................................................................................... 236

11.5.107 Is Indian Phone Number ................................................................................... 236

11.5.108 Is Indian Mobile Number ................................................................................. 236

11.5.109 Is Indian Vehicle Registration Number ............................................................ 237

11.5.110 Is South African Phone Number ....................................................................... 237

11.5.111 Is South African Mobile Number ..................................................................... 237

11.5.112 Is South African VAT Number ........................................................................ 237

11.5.113 Is US or CA Zip code ....................................................................................... 238

12. Transformation Functions ................................................................................................. 239

12.1 Strings .......................................................................................................................... 239

12.1.1 Upper Case ........................................................................................................... 239

12.1.2 Lower Case ........................................................................................................... 239

12.1.3 Proper Case ........................................................................................................... 239

12.1.4 First Up ................................................................................................................. 239

12.1.5 Delete Spaces ........................................................................................................ 240

12.1.6 Trim ...................................................................................................................... 240

12.1.7 Left Trim .............................................................................................................. 240

12.1.8 Right Trim ............................................................................................................ 240



12.1.9 Sub String ............................................................................................................. 241

12.1.10 Replace ............................................................................................................... 242

12.1.11 Ensure Prefix ...................................................................................................... 243

12.1.12 Ensure Suffix ...................................................................................................... 243

12.1.13 Ensure No Prefix ................................................................................................ 243

12.1.14 Ensure No Suffix ................................................................................................ 244

12.1.15 Left Pad .............................................................................................................. 244

12.1.16 Right Pad ............................................................................................................ 244

12.1.17 Escape String ...................................................................................................... 245

12.1.18 UnEscape String ................................................................................................. 245

12.1.19 Delete .................................................................................................................. 245

12.1.20 Left ..................................................................................................................... 246

12.1.21 Right ................................................................................................................... 246

12.1.22 Left Delete .......................................................................................................... 246

12.1.23 Right Delete ........................................................................................................ 247

12.1.24 Replace Characters ............................................................................................. 247

12.1.25 Delete Characters ................................................................................................ 248

12.1.26 In Place Replace ................................................................................................. 249

12.1.27 After .................................................................................................................... 250

12.1.28 Before ................................................................................................................. 250

12.2 Numbers ...................................................................................................................... 251

12.2.1 Round ................................................................................................................... 251

12.2.2 Abs ........................................................................................................................ 252

12.2.3 Sign ....................................................................................................................... 252

12.2.4 Random Number .................................................................................................. 253

12.2.5 Add ....................................................................................................................... 254

12.2.6 Subtract ................................................................................................................. 254

12.2.7 Multiply ................................................................................................................ 255

12.2.8 Divide ................................................................................................................... 255

12.3 Date.............................................................................................................................. 256

12.3.1 Date Format .......................................................................................................... 256

12.3.2 Current Date ......................................................................................................... 257

12.3.3 Reformat Date ...................................................................................................... 258

12.3.4 Change Date ......................................................................................................... 259

12.3.5 Date part ............................................................................................................... 260

12.4 Miscellaneous .............................................................................................................. 261

12.4.1 Length ................................................................................................................... 261

12.4.2 Literal ................................................................................................................... 261

12.4.3 User....................................................................................................................... 262

12.4.4 Splitter .................................................................................................................. 262

12.4.5 Get Line ................................................................................................................ 263

12.4.6 Joiner .................................................................................................................... 264

12.4.7 Calculation ............................................................................................................ 265

12.4.8 Lookup .................................................................................................................. 268

12.4.9 Previous Value...................................................................................................... 272

12.4.10 Reader Parameter ................................................................................................ 273

12.4.11 Sequence ............................................................................................................. 274

12.4.12 Running Total ..................................................................................................... 275

12.4.13 Keep Value ......................................................................................................... 276



12.4.14 Checksum ........................................................................................................... 277

12.4.15 GUID .................................................................................................................. 278

12.4.16 Range Lookup..................................................................................................... 278

12.4.17 Set Variable ........................................................................................................ 279

12.4.18 Get Variable ........................................................................................................ 279

12.4.19 Command Line parameter .................................................................................. 280

12.5 XML and HTML Functions .................................................................................... 281

12.5.1 Encode XML Element .......................................................................................... 281

12.5.2 Decode XML Element .......................................................................................... 281

12.5.3 Encode XML Attribute ......................................................................................... 282

12.5.4 Decode XML Attribute ......................................................................................... 282

12.5.5 Encode XML String.............................................................................................. 282

12.5.6 Decode XML String ............................................................................................. 282

12.5.7 HTTPEncode ........................................................................................................ 283

12.5.8 HTTPDecode ........................................................................................................ 283

12.5.9 Encode Base 64 .................................................................................................... 284

12.5.10 Encode Base 64 .................................................................................................. 284

12.5.11 XML XSLT Transformation .............................................................................. 285

12.6 File name functions ..................................................................................................... 286

12.6.1 Unix Path To Dos Path ......................................................................................... 286

12.6.2 Dos Path To Unix Path ......................................................................................... 286

12.6.3 File Name ............................................................................................................. 286

12.6.4 Directory Name .................................................................................................... 287

13. Date formats ...................................................................................................................... 288

14. SQL Scripts ....................................................................................................................... 289

14.1 SQL Scripts Tool Bar .................................................................................................. 291

15. Reports ............................................................................................................................... 292

15.1 Report Designer ........................................................................................................... 292

15.1.1 Report Title ........................................................................................................... 293

15.1.2 Page Header .......................................................................................................... 293

15.1.3 Group Header ....................................................................................................... 293

15.1.4 Code ...................................................................................................................... 293

15.1.5 Data....................................................................................................................... 293

15.1.6 Report tool bar ...................................................................................................... 294

15.1.7 Report Properties .................................................................................................. 294

15.1.8 Save Report to the Repository .............................................................................. 294

15.1.9 Report Connection ................................................................................................ 294

15.1.10 Show Only Connections for Current Project ...................................................... 294

15.1.11 Manage Versions ................................................................................................ 294

15.1.12 Add Versions ...................................................................................................... 295

15.1.13 Creating Basic Report ......................................................................................... 295

15.1.14 Report in Report Designer .................................................................................. 301

16. Email Templates ................................................................................................................ 303

17. Packages ............................................................................................................................ 305

17.1 Package screen overview ............................................................................................. 307

17.2 Joining Actions ............................................................................................................ 308

17.3 Package Tool bar ......................................................................................................... 309

17.4 Debugging Package ..................................................................................................... 310

17.5 Using Variables ........................................................................................................... 312



17.6 Working with filenames and directories ...................................................................... 315

17.7 Set Variable Action ..................................................................................................... 316

17.8 Script Action ................................................................................................................ 318

17.9 Transformation Action ................................................................................................ 321

17.10 SQL Script Action ..................................................................................................... 322

17.11 SQL Check Action .................................................................................................... 323

17.12 Package Action .......................................................................................................... 324

17.13 FTP Action ................................................................................................................ 325

17.14 ZIP Action ................................................................................................................. 327

17.15 Receive Email POP3 Action ..................................................................................... 329

17.16 Receive Email IMAP4 Action ................................................................................... 332

17.17 Send Email Action ..................................................................................................... 335

17.18 External Application Action ...................................................................................... 337

17.19 Check file action ........................................................................................................ 338

17.20 File Operation Action ................................................................................................ 339

17.21 Compare Files Action ................................................................................................ 340

17.22 Backup Repository Action ......................................................................................... 341

17.23 Export Data Action .................................................................................................... 342

17.24 Show Message and Ask Question Action ................................................................. 346

17.25 Check Disk Space Action .......................................................................................... 348

17.26 HTTP Download Action............................................................................................ 349

17.27 Check Connection Action.......................................................................................... 350

17.28 PING Server Action.................................................................................................. 351

17.29 Report Action ............................................................................................................ 352

19. Lookups ............................................................................................................................. 354

19.1 Tool bar ....................................................................................................................... 355

19. Scheduler ........................................................................................................................... 356

19.1 Tool bar ....................................................................................................................... 356

20. Execution Monitor ............................................................................................................. 361

20.1 Tool bar ....................................................................................................................... 363

20.2 Stopping execution ...................................................................................................... 363

21. SQL .................................................................................................................................... 364

21.1 SQL Toolbar ................................................................................................................ 364

21.2 Side toolbar .................................................................................................................. 365

22. Version Control ................................................................................................................. 366

23. Using Command Line interface ......................................................................................... 367

24. Scripting Language ............................................................................................................ 370

24.1 The Basic Structure ..................................................................................................... 370

24.2 Variables ...................................................................................................................... 371

24.3 Logical Operations ...................................................................................................... 376

24.4 Repeating sets of commands ....................................................................................... 380

24.5 Functions ..................................................................................................................... 383

24.6 Script Examples ........................................................................................................... 384

24.7 Passing variables between objects ............................................................................... 385

24.8 Supported Functions List ............................................................................................. 386

24.8.1 String Functions.................................................................................................... 386

24.8.2 Conversion Functions ........................................................................................... 391

24.8.3 File System Functions .......................................................................................... 393

24.8.4 Date and Time Functions ...................................................................................... 397



24.8.5 Numeric Functions ............................................................................................... 401

24.8.6 Miscellaneous Functions ...................................................................................... 403

24.8.7 Procedures ............................................................................................................ 405

24.8.8 Math functions ...................................................................................................... 406

25. Execution Agent ................................................................................................................ 411

Parallel Execution ................................................................................................................ 412

26. Repository database ........................................................................................................... 413

Transferring data between repositories ................................................................................ 413

Creating new repository ....................................................................................................... 413

27. Support Procedure ............................................................................................................. 415

28. License Agreement ............................................................................................................ 416



Copyright

Copyright 2012 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 Advanced ETL Processor 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 Advanced ETL Processor Professional and Enterprise version

5.2.0.0



1. Introduction

The Advanced ETL Processor is a Codeless ETL Tool. The Advanced ETL Processor is

designed to automate all of your business processes. It saves you time and money so you can

concentrate on what is important for your business.

The Advanced ETL Processor is an end to end database extraction and importing tool. The

beauty of the system is that it saves the drudgery and manual tasks normally required for tasks

of this type, such as the writing of code, and all the transformations, validations and general

checks normally performed. Using traditional methods, operations of this type can only be

performed in stages, and not as one smooth operation!

For example, the traditional method of importing data from one system to another is to write

specific code to extract data from the source database, e.g. an Oracle database, by creating a

CSV comma de-limited file, and then writing code in the new language or system, for instance

Microsoft Access, and then performing the import. However, the operation does not end there.

Any data imported has to be sorted, duplicated and loaded into the database using appropriate

primary and foreign key constraints. This is only possible by creating code designed to achieve

this process. You then need to manually send an e-mail to the administrator when the process

is complete. In other words, each stage cannot be left to run in an automated fashion and has to

be completed before proceeding to the next.

As you can see, the process is not straightforward. The Advanced ETL Processor automates

all these processes in a simple and transparent fashion, and all without writing any code

whatsoever.

As stated, the tool to handle any kind of database, including Oracle, Microsoft Access, SQL

Server, DB2, MySQL, PostgreSQL, Interbase, Excel spreadsheets, and a wide range of others.

It is an excellent tool for those organisations who work with data warehouses, and where this

involves working with a number of disparate databases.

Existing users find that the tool provides several benefits over existing tools such as Oracle

SQL Loader, BCP, DTS or SSIS, such as the ability to update records automatically via

utilisation of the primary key.

For example typical usage would be to download an Excel file from the FTP server, load into

a database run sum calculations, email results a to manager and log to the administrator.

The Advanced ETL Processor would automatically carry out all the following steps for this

task using the following process:

1. Download Excel file from the FTP server.

2. Extract required data using any appropriate filters (these can easily be configured in the

tool beforehand).

3. Validate date formats to ensure they are consistent with the target database.

4. Sort the data appropriately.

5. Migrate data into relevant tables, including any duplication where required.

6. Run any stored procedures or script associated with the backend process.



7. Notify the administrator when the process is complete.

The idea is to present the tool with a "roadmap" of what to do and a set of tools and

procedures to execute the end to end process in one swoop. Once you are ready to go, you

simply start the automation process and it completes all the necessary tasks required without

any further intervention by the user or developer. Unlike standard migrations, it may be

possible to leave the tool to do its job overnight, saving staff resources and daytime business

hours. If something went wrong Advanced ETL Processor writes detailed messages into the

log.

Since Advanced ETL Processor is a Codeless ETL Tool most of the tasks performed

without writing a single line of code



2. What can Advanced ETL Processor do?

The Advanced ETL Processor provides a mechanism by which a number of different

processes, protocols and systems can interact to provide the technical machinery to process a

number of tasks from start to finish.

Enterprise environments benefit from this type of system, such as Fortune 100 companies and

large corporations. However, it is equally useful for the small business environment.

Advanced ETL Processor can handle a number of different IT functions and services. For

example, it can process databases, files, email protocols such as smtp, pop3 and imap, ftp,

Microsoft Office documents, and numerous others.

The system works by working to a schedule and set of tasks created and engineered by the

end user. Some of these tasks may involve the running of a shell script; dos batch file or a sql

script/stored procedure. The system is set up like a "conveyor belt" of tasks, each one to be

completed before the next one can take place. However, it is also possible to set up tasks to run

concurrently, and indeed this would increase the efficiency of some of the tasks.

Although the system itself is "codeless" i.e. the end user does not need to enter any code, it

does not prevent the execution of code or having code written to perform certain complex

actions. However, any code written is not part of the Advanced ETL Processor, and can be

run independently if required. This provides even more flexibility.

When planning out tasks, it is probably best to specify the requirements beforehand. This can

be documented in Standard English pseudo code, or simply provided by a list of actions.

Here are some examples of what our customers use this automations tool for:

Automate Everyday tasks

Move data from one database system to another

Import sales into marketing system

Validate and correct key data

Remove duplicated data

Avoid re-keying data

Design, generate and email reports

Download files

Automatically respond to emails

Populate Data warehouses

Deal With Dirty Data

Extract Data from Emails

Send emails to customers



Check Data

Validate Post codes, Phone numbers, TAX Codes using regular expressions

Validate Date Formats

Validate Date and Time

Validate Numbers

Validate Strings

Transform Data

Sort Data

Group Data

Summarize Data

Pivot and UnPivot Data

Translate coded values.

Create surrogate keys

Split, merge Fields

Split data and load it into different tables

Add, replace, and delete strings

Load Data

Add Records

Update Records

Delete Records

Add New and Update existing Records

Run SQL scripts

Generate insert script

Parse XML

Now lets provide a case study and a list of actions to see how the system can work in

practice:



2.1 Case Study: Automatic Processing of Financial Data

We are small Accounting Company based in Melbourne. Over last year our customer base

had grow twice and it is still growing. We spend a lot of time thinking how we can optimise

our business processes. First thing we did created standard Excel templates for everything. For

example standard expenses form will consist of customer id, employee id, expense date,

category and amount. Using standard forms saved us a lot of time but once all the forms are

filled in they had to be processed manually. Our people ware constantly under the pressure and

we had to employ an agency staff on temporary basis.

Not any more.

During routine Google search we discovered Advanced ETL Processor Ent which quickly

becomes a corner stone for automating our business processes.

By using Advanced ETL Processor Ent we were able to eliminate most of our manual tasks

It is like spider which sits in the middle connected to all our systems.

For example, for expenses form the process is follows.

A customer emails us expenses form

Advanced ETL Processor Ent package connects to mail server, downloads attachment,

Loads Excel file into our accounting system,

Runs SQL script to validate submitted data,

Emails report back to the person who submitted expenses from

At the end of month summary report emailed to the company automatically.

Carl Barret,

Accountant



The Advanced ETL Processor can be used to automate the process of transferring and

transforming data from one database to another, or from several databases to several other

databases. The Advanced ETL Processor is a specialised tool designed for this task.

For the Advanced ETL Processor the above tasks are very simple. It can perform tasks

much more complex than this, but it does provide a flavour of what the processor can achieve.

Additional validation checks can be built into the process, and any problems can be reported to

an error log. The process can be made to halt on error, or it can continue, so that any problem

areas can be re-executed at a later time.

Once you are ready to go, it is a simple matter to start the Advanced ETL Processor and

allow it to perform its tasks. Stages in the process can be changed or amended for different

environments and systems. The processing stages can also be changed depending on what

happens during the process, so it can make intelligent decisions based on what the creator of

the process would like to happen during execution. Therefore what if situations can be

handled and alternative processing routes included.



3. Requirements

Below is the list of Software that must be installed before installation of Advanced ETL

Processor:

Software Version Notes

Microsoft Windows 98 or higher

MDAC 2.6 or higher Part of OS on W2K,

XP, Vista.

MS Access ODBC

driver

4.00.6364.00 or

higher

Part of OS on W2K,

XP, Vista.

Only to work with MS

Access 95-2003

Databases

MS Access 2007

ODBC driver

12.00.4518.1014 or

higher

Separate download Only to work with MS

Access 2007

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 Part of OS on W2K,

XP, Vista.

Only to work with

MS SQL Server

7/2008

Oracle Client 7.3.4 or higher Provided by Oracle Only to work with

Oracle

Databases/Connect to

Oracle repository

Interbase client GDS32.DLL Only to work with

Interbase or Firebird

Databases

Jet 4.0 Service pack 5 or

higher

Part of OS on W2K,

XP, Vista.

To connect to MS

Access repository

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-9B72-

EF94E038C891&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 9-

10g 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.

Note:

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.

Repository Scripts

Repository Creation scripts are provided for following databases:

MS Access

Oracle

MS Sql Server

Interbase/Firebird

MySQL

PostgreSQL

Note:

Once installation is completed you may find all the scripts in

C:\Users\Public\Documents\DBSL\Repository Scripts\Repository



4. Key features

The ability of the Advanced ETL Processor to work with a number of disparate systems

means that it is provided with a rich set of tools and functionality, which can be used in

isolation or combined in a powerful way with other toolsets, either within the processor or with

other third party tools. It is in effect, an "engineering" environment for the movement of data

to and from different sources.

We will now explain and define the variety of features which are provided as part of the

toolset. Let us first have a look at the data extraction process.

4.1 Extraction Process

4.1.1 Multiple Data Formats

The extraction process can handle a variety of data formats, including multiple delimited or

fixed width text files. The power of this system however, is in its ability to find files to load

using mask.

4.1.2 Multiple Databases and Table Processing

It can easily interpret and manipulate Microsoft Access data, from a number of different

databases. Again the end user can use a mask to find the tables to load the data from. It does

also apply to Excel and DBF/FoxPro files.

4.1.3 Other Database Features

The Advanced ETL Processor also has other useful database features, such as the ability to

connect to any Object Database Connectivity (ODBC) database. 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. The Advanced ETL Processor utilises this technology to great effect.

So what if you dont have an Oracle or MySQL database? What about SQL Server? No

problem, the Advanced ETL Processor can handle SQL Server data as efficiently as data

from any other type of database.



4.2 Summary of the Extraction Process:

Multiple Delimited or Fixed width Text files

Multiple Excel files + Multiple Excel Spread sheets

Multiple MS Access Databases + Multiple Tables

XML Files

Qvx QlikView files

Multiple DBF Files

Any ODBC compliant database

Any JDBC compliant database

Full support for OLE DB

Direct connection to Oracle, SQL Server, MySql, PostgreSQL, SQLite, SQL Server

Compact and Interbase/Firebird

Multiple Tables

File System

POP3 Servers

IMAP4 Servers

Windows Event Log

TCP/IP Socket

RSS

Google Spread Sheet

SalesForce

BrightPearl

4.3 Validation Process

The Advanced ETL Process has a robust validation process built in. The types and nature of

the validations taking place can be tweaked and configured by the user. A full range of

validation functions are included. Validations can be performed on the basis of data type, lists

of values, and regular expressions, which can be individually changed according to

requirements.



4.4 Summary of Validation Processes:

Validation:

195+ Validation Functions

String Validation

Number Validation

Date Validation

Time Validation

Validation Against list of values

Regular Expressions: Post codes, Phone Numbers, VAT Numbers ETC

4.5 Transformation Process

The process of transformation involves the manipulation of data so that it can be converted

from one format or data type to another. The Advanced ETL Processor provides a useful set

of built in transformation functions. Transformations can be performed on strings, numbers,

dates, coded values and whole chunks of data. The transformation functionality allows for the

sorting and grouping of data according to requirements. Data can also be duplicated as

required.

4.6 Powerful Data Transformation

In addition to the standard data transformation abilities, the processor can also perform

complex translation functions. An example would be if an integer variable = 1, then set a

text variable to yes. Once data is translated, it is possible to join data into a completely new

format or present it in a new way. The Advanced ETL Processor provides many flexible

alternatives for data manipulation, and these are not difficult to take advantage of.

The Advanced ETL Processor also provides the ability to derive calculated values, join data

together from multiple fields, summarise multiple rows at once, or can split or merge columns

at will.

The flexibility and power of the processor means that you can customize data transformation

and conversion functions according to your requirements with a click of the mouse. This saves

hours and hours of coding.



4.7 Summary of Transformation Processes:

Transformation:

82+ Transformation Functions

String Transformation

Number Transformation

Date Transformation

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)

Customised Transformation

Primary Key Generation

Running Totals

Powerful scripting language

System variables



4.8 Loading Process

The loading capability of the Advanced ETL Processor is superior to other basic tools such

as SQL Loader, because it provides the administrator with several options and ways of

providing database load capability without creating any code.

Other tools allow you to load data into a single database table at a time, under a single

instance. The ETL processor on the other hand allows you to specify multiple upload targets

which mean you can save time loading each individual table. Another useful feature is that you

can execute SQL scripts either prior to the load or after the load has completed. This is useful

for tidying up the data or providing a report on the result of the load process once it completes.

It does not matter if the file to be loaded is a fixed or variable length text file, the Advanced

ETL Processor can handle it. It works with Access, DBF files, Oracle, SQL Server and any

ODBC compliant database.

It is one of the most powerful database loading tools in the marketplace.

4.9 Summary of the Loading Process:

Loading:

Multiple Data Targets

SQL scripts execution before and after loading

Delimited or Fixed width Text files

MS Access Database

Microsoft Excel File

DBF File

Any ODBC compliant database

Any OLE DB compliant database

Oracle database

Microsoft SQL Server database

Microsoft SQL Server CE

SQLite

MySQL

PostgreSQL

Interbase/Frebird

File System

SMTP Servers

XML Files

QlikView Qvx files

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 and SSIS service.



4.10 Automation Process

The automation process of the Advanced ETL Processor is very powerful and generic.

If there is a process requiring automation there is a very good chance that the ETL Processor

can handle it. The ETL processor can automate a myriad of different technologies. In fact,

"automation ability" sits at the core of the Advanced ETL Processor.

This is a valid claim as the system caters for a number of database technologies and

protocols. The coding to perform this is built in; therefore there is no need for further

development by any programmers. The only actions required are that the definitions of the data

and the functionality are specified, and the system does the rest. As previously stated, the

automation functions allow you to set up a "conveyor belt" of actions. The administrator

simply sets the process in motion.

For instance, you may wish to run an SQL script, produce a report, copy a file, send a file

over to a server via FTP, backup the resulting data, and then inform the project manager of the

end result via email. The Advanced ETL Processor is an excellent automation system,

without the need for coding.

4.11 Summary of the Automation Processes:

Automation:

SQL Scripts

Email Templates

Report Designer

Integrated Scheduler

Logging

Email Notifications

POP3 Email Receiver

IMAP4 Email Receiver

File Operations

Check Files

Check Disk Space

Compare Files

Full range of FTP operations

ZIP

Applications

SQL Data Check

Automatic Backups

Mass Data Export

Http Download

Ping Server

Check Database Connection



5. Advanced ETL Processor Architecture

The following graphical depiction of a typical ETL installation shows how the software

interacts with other components and interfaces belonging to various databases. The processor

sits in the middle of the various databases and carries out its tasks, such as converting,

transforming and validating data from various sources.

As you can see that the Advanced ETL Processor uses native low level API's for specific

databases, such as the Oracle Call Interface (OCI), or, in order to handle the Microsoft SQL

Server database the BCP API. Other API's can also be "plugged in" such as the ODBC API

which allows MS Access, DBF or plain flat files to be processed.

Note:

One of major benefit of using native low level API' is a great performance boost it gives to

Advanced ETL Processor.



6. Demo Data

In order to provide a flavour of the types of data the Advanced ETL Processor can handle, a

number of different file types have been provided for demonstration purposes.

A range of different types of data files have been provided, in addition to SQL scripts to

create database tables in Oracle, and MS SQL Server.

Text files

Examples provided are using text files in

C:\Users\Public\D

advanced etl processor professional user manual

Documents

extraction process

loading process

powerful data transformation

demo data

automation processes

multiple data formats

user interface

db software laboratory