Here you will find the latest news and articles about our ETL Software

Our WIKI has all the information you need, if you are stuck, please post your question on our support forum and we will do our best to assist you

Here is a basic example of creating a temporary folder

The Script calculates directory name and saves it into a variable. The variable than used to create the directory

temporary folder package

temporary folder script

begin
SetVariable('',GetTemporaryDirectory('c:\tmp'));
Result:=True;
end;

temporary folder file operation

Full List of "temporary" functions:

GetWindowsTemporaryDirectory

The GetWindowsTemporaryDirectory function returns windows temporary path.

GetTemporaryFile(Extension)

The GetTemporaryFile function returns a temporary file path. The file is located in windows temporary directory

GetTemporaryFileInDirectory(Directory,Extension)

The GetTemporaryFileInDirectory function returns temporary file path. The file is located in the directory

GetTemporaryDirectory(Directory)

The GetTemporaryDirectory function returns a temporary directory name in a specified directory

WIKI ARTICLEs:
Download Free Trial Learn More About Our ETL Software

What Directory Monitor For

Directory Monitor is a windows service which tracks folder changes, such as; file creation, removal, modification or rename. It is able to handle multiple locations at once. There is the ability to watch network shares in addition to local folders. Directory monitor does not execute anything, we have a separate windows service for it, called execution agent. When change happens, directory monitor inserts a record into queue table. There is no need to restart Directory monitor service when user amends the settings.
 

How Directory Monitor Works

Database

Holds the overall status of directory monitor: is it enabled or not and settings for individual threads events to monitor for: filter, waits

Users

Create, amend and delete monitors settings.

Management thread

Responsible for creating, updating and destroying monitor threads.

Monitor thread

Monitors single directory for changes.
When an event happens, it inserts a record into queue table.

File usage thread

Creates and runs file usage tasks
When create event happen it might take some time to release the file.
(It might take 5 minutes to copy a file)

File usage task

The task is just a procedure which checks if the file is open. For example, one task checks filea.txt, another check filleb.txt and so on. Threads are very heavy and there is OS limitation how many threads we can create. Creating thousands of threads is definitely a stupid idea. The task is very light and while one task is waiting for x milliseconds another one is being executed. The way it works, it checks if the file is open if it is, it sleeps for some time and checks it again. Once the file is closed or a timeout happens it inserts a record into queue table. Checking is done by opening a file for writing.

Download Free Trial Learn More About Our ETL Software

Advanced ETL Processor - 6.3.2.0 is ready for the users.

The most important change that 64-bit version can now execute both 32bt and 64bit scheduled actions, here a screenshot of the execution log:

 Runningboth64bitAnd32bitActions

Other changes are:

  • Improved: Directory Monitor
  • Added: New events to watch to Directory Monitor: add, delete, modify and rename files
  • Added: Wait and Timeout option to Directory Monitor
  • Improved: UnInstall process
  • Fixed: Salesforce Bulk API load issues
Download Free Trial Learn More About Our ETL Software

Background:

This article was created as an answer to one of our ETL forum posts, we hope it will help a lot of users in a similar situation.

"The scenario is based on files landing in a monitored directory. At this stage, I am not worried about volume or frequency. But will be handling each file individually for internal reasons. Each file has a unique name but a common format of xxx-yyyymmddhhmmssnnnn.ext

"xxx" represents a client prefix (eg. abc, bcd, cde). The format of each file for the same client will be the same. But the format is different for each client. The file extension '.ext' will generally be .csv, .xml, or even .xls.

The file transformations between clients will be different. However, the output is always the same format and the target is a SQL Server database table.

I am developing each of the transformations in isolation, but with the same basic structure.

My idea is to have a single monitor looking at a single folder for *.* That then executes a single Package based on the creation of a file. The Monitor passes the file to the Package that then does some environment management, put the source file in a staging location, then based on the file prefix conditionally (Probably through a CASE action) call the associated transformation and load to the SQL server and then clean up."

NOTES:

The monitor (Directory monitor) is a windows service which is constantly monitoring a directory for changes. When it happens it adds predefined action to the execution queue. Monitor passes the file name as a variable into the action, this variable name is <Directory Monitor File Name>.

Solution:

Example of data warehouse workflow

"Get ClientID" script: extracts first three characters of the file name. The Result is stored in '<ClientID>' variable.

var
FileName: String;
ClientID: String;
begin
FileName := GetVariable('<Directory Monitor File Name>');
ClientID:=UpperCase(SubString(FileName,1,3));
SetVariable('<ClientID>',ClientID);

Result:=true;
end;

Check Client directory: checks if client directory exist and if it does not it creates it together with subdirectories.

 Every transformation has an ID. We use this ID to execute specific transformation for a specific customer.

Table Transformations holds mapping between ClientID and TransformationID:

CREATE TABLE [dbo].[Transformations]
( [ClientID] [char](3) NOT NULL, [TransformationID] [int] NULL, 
CONSTRAINT [PK_Transformations]
PRIMARY KEY CLUSTERED ( [ClientID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]) ON [PRIMARY]
GO 

Loookup

"Insert Into Transformations" sql script inserts a record for missing clients:

insert into Transformations(
[ClientID],
[TransformationID])
values ('<ClientID>',0)

GO

Once file is ready for ready processing it is moved into Staging Area.

Get transformation ID: executes the following SQL:

 Select TransformationID
from Transformations
where ClientID='<ClientID>'

The result is saved into <TransformationID> variable.

Execute Transformation script:

begin
ExecuteObject(GetVariable('<TransformationID>'));
Result:=true;
end;

Upon completion the file is moved into Processed Area.

Directory Structure:

 DirectoryStructure

Variable as Mask:

Mask

Download Free Trial Learn More About Our ETL Software

We are happy to announce that the latest version of Advanced ETL Processor supports the creation of Tableau Hyper files

What’s Hyper?

Under the hood, Hyper is the technology that now powers Tableau’s data engine. The data engine is what handles opening, creating, refreshing, and querying your extracts.

https://www.tableau.com/about/blog/2018/1/extracts-hyper-speed-what-you-need-know-79397

A full list of changes:

  • Added:  Support for Hyper Tableau files (64-bit version)
  • Added:  Upload type option to Publish Tableau File action dialogue
  • Added:  "If File Exists" option to Publish Tableau File action dialogue.
  • Added:  Support for hyper,tdsx,twb,twbx files to Publish Tableau File action
  • Improved: Publish Tableau File action uses rest now
  • Improved: Third party components update

Create Hyper Files

About Tableau:

Tableau is a business intelligence software that allows anyone to connect to data in a few clicks, then visualize and create interactive, shareable dashboards with a few more. It's easy enough that any Excel user can learn it, but powerful enough to satisfy even the most complex analytical problems. Securely sharing your findings with others only takes seconds.

The result is BI software that you can trust to actually deliver answers to the people that need them.

Video Tutorial

Download Free Trial Learn More About Our ETL Software

We have 187 guests and no members online