Stop executing etl process

Quite often people ask us how to stop all ETL packages execution.

“When I stat Advanced ETL Processor it immediately runs transformation package. How can I avoid it?”

Most of the time, this is because Professional version is being used to execute the packages.

Better option is to use Enterprise version agent and run tasks in parallel.

To stop execution

  1. close Advanced ETL Processor/Visual Importer ETL
  2. click  Start => DB Software Laboratory => Advanced ETL Processor Enterprise=> Advanced ETL Processor Enterprise Options=>Execution
  3. and check ‘Do not execute any actions’

Options

This work for both Advanced ETL Processor and Visual Importer ETL

Related forum posts:

Automate File Processing

A Question from the Customer:

An external PLC generates txt files and those must be downloaded and converted into an SQL DB.
The PLC generates 4 files each day, at a size of 5MB pr. File = 20MB a day.
The file name is date and time named like this : 14022719.txt, syntax is YYMMDDHH
The files are generated at the same 4 hours each day, 01, 07, 13, 19
In the files, first data row is a time stamp YYMMDDHHMMSS
It is important that one record with a specific date/time is written only once in the SQL.
The files would always be the same size.

ETL Job:

Each day, The ETL converter should copy the file to a local directory via FTP; convert the file to SQL, then delete the file from the local dir. It could be 4 times a day, maybe one hour after the txt file is completed on the PLC.
We can not delete the file from the PLC, since others should also have access to the data.
The data on the PLC will be deleted after 10 days using FIFO

The problem:

How to I keep track of which files I have copied ?
Could create a TXT file on local drive and write the file names, compare with whats on the FTP drive ?
But how do we do this ? That is file comparison..can ETL do this ?
Is there any other way to do this ?
Keep in mind, it could be that the internet connection is off line for a day or 2, the server could be down,
The PLC could be down. In some situations you will need to copy 4 files, some times 6 files..depents..
Or you could have gaps in the time stamps of the file names from the PLC if it has been down for a while.
I know we would then also have gaps in the SQL, but that is OK.

The solution:

Read more...

Processing EDIFACT Messages

What is EDI/EDIFACT message?

EDI provides a technical basis for commercial "conversations" between two entities, either internal or external. EDI constitutes the entire electronic data interchange paradigm, including the transmission, message flow, document format, and software used to interpret the documents. EDI standards describe the rigorous format of electronic documents.

In essence EDI message is just a small text file which can be send via email,http or ftp

Message Example:

UNH+199700001+INVOIC:D:93A:UN:EAN007'BGM+380+424876'DTM+137:199701111045:203'RFF+ON:334411'RFF+AAK:23149'NAD+BY+7080001000004::9++Hans Hansen AS+Storgata 1+TRONDHEIM++7005'NAD+SU+7080000366767::9++Børsterud AS+Industriveien 1+OSLO++0580'FII+RH+60731108042'RFF+VA:FORETAKSREGISTERET NO123456789MVA'CTA+AD+Lise Hansen'NAD+IV+7080001000011::9++Hans Hansen Øst AS+Grenseveien 1+HEBEKK++1406'NAD+DP+7080001000028::9++Hans Hansen Midt AS+Heggeveien 1+HEIMDAL++7080'NAD+SF+7080000000065::9++Børsterud AS, Vareutlevering+Industriveien 1+OSLO++0580'PAT+1'DTM+7:19970110:102'PAT+3'DTM+13:19970630:102'TOD+3++EXW'LIN+1++7030432630011:EN'PIA+1+7200018:SA::91'IMD+FL++:::RULLESYSTEM'IMD+C++TU'QTY+47:100'QTY+59:12'ALI+++6'MOA+66:25000,00'MOA+203:22000,00'PRI+AAB:250,00'PRI+AAA:220,00'PAC+++CT:KARTONG'TAX+7+VAT+++:::23+S'ALC+A++++PAD:::TILBUDSRABATT'PCD+3:10'MOA+8:2500,00'ALC+A++++PDE:::PALLERABATT'MOA+8:500,00'RTE+1:5,00'LIN+2++7030439770710:EN'PIA+1+2844001:SA::91'IMD+FL++:::BØRSTER'IMD+C++TU'QTY+47:200'QTY+59:36'ALI+++6'MOA+66:2000000'MOA+203:17900,00'PRI+AAB:100,00'PRI+AAA:89,50'PAC+++CT:KARTONG'ALC+A++++PAD:::TILBUDSRABATT'PCD+3:10'MOA+8:2000,00'ALC+A++++QD:::KVANTUMSRABATT'QTY+1:50'PCD+3:2,0'MOA+8:100,00'UNS+S'CNT+2:2'MOA+66:45000,00'MOA+203:39900,00'MOA+260:5100,00'MOA+131:5100,00'MOA+NET:39900,00'MOA+125:39900,00'MOA+150:9177,00'MOA+86:49077,00'MOA+129:39900,00'TAX+7+VAT++39900,00+:::23+S'MOA+176:9177,00'ALC+A++++PAD:::TILBUDSRABATT'MOA+8:4500,00'ALC+A++++PDE:::PALLERABATT'MOA+8:500,00'ALC+A++++QD:::KVANTUMSRABATT'MOA+8:100,00'UNT+76+199700001'

Here is the message format description:

EDIFACT File Description

UNA:+.?

 

UNB+UNOA:1+9377778760643:ZZ+9399700000001:ZZ+050322:1237+000000001

Message header, with sender GLN, addressee GLN, date, time & interchange number

UNH+00000000000459+ORDERS:D:96A:UN:EAN008

Message Beginning

BGM+220+10910220+92

Purchase Order Number

DTM+137:20050322:102

Order Date

DTM+2:20050322:102

Requested Delivery Date

NAD+SU+1217::92

JB Supplier Code

NAD+ST+304::92

Ship to Store Code

NAD+IV+304::92

Invoice to Store Code

LIN+1

Item Line Number

PIA+5+711719347521:VN::91

Product Identifier

QTY+21:10.0000:EA

Quantity

PRI+AAA:40.0000::TU

Price Ex GST

UNS+S

Message trailer

CNT+2:1

Control Total (total number of item lines)

UNT+14+00000000000459

Message Trailer

UNZ+1+000000001

Message End

Every data part is delimited by single quote and prefixed with some characters and they indicate which kind of data it is.
for example LIN indicates line number

Processing EDI Data

Read more...

Library of objects

The ability to store transformation/package objects in the repository for reuse in other transformations was introduced in the latest release.
For example if there is a series of transformations (lookup, add, if null, if empty, etc) that are reused we could design once and reuse in other transformations. Works with both Visual Importer ETL and Advanced ETL Processor

Library Of Objects

LibraryOfObjects2

LibraryOfObjects3

 

More Articles...

We found Advanced ETL Processor remarkably easy to use and DBSL support team provides outstanding value and service.

David Thomson
Marketing Manager

Our customers

BP

BBC

HSBC

Orange

CAT

SEPA
emc
NHS
Australian Rugby Union
xerox
Databases we work with