Based on the customer feedback in the latest version of Advanced ETL Processor and Visual Importer ETL we have introduced Agent Email Notifications.
This option allows the controlling state of the repository connection.
For example, IT may decide to reboot the database server where the repository is hosted orRepository database may run of disk space.
When the agent is up and running it is constantly checking repository connection, In case of failure, it will send an email notification and abort all tasks currently being executed.
Once the connection to the repository is reestablished agent will send another email and start executing scheduled tasks again
Error Message: [DBNETLIB][ConnectionWrite (send()).]General network error. Check your network documentationComputer Name: COMPUTERUser Name: billOs Version: Windows 8.1 (Version 6.3, Build 9600, 64-bit Edition)Number Of Threads: 11Repository: MS SQL Server RepositoryType: MS SQL ServerServer: COMPUTER\SQLEXPRESS2008Database: REPOSITORYUser: sa
Error Message: Reconnected!Computer Name: COMPUTERUser Name: billOs Version: Windows 8.1 (Version 6.3, Build 9600, 64-bit Edition)Number Of Threads: 11Repository: MS SQL Server RepositoryType: MS SQL ServerServer: COMPUTER\SQLEXPRESS2008Database: REPOSITORYUser: sa
Agent log is located in the same folder as the agent itself
FTP protocol is used daily by most businesses around the world. Our ETL Software makes it easy to automate FTP uploads and downloads.
The users can create and delete directories on the FTP server, delete files, move files and produce a list of files. Here is a basic example of FTP workflow.
We also offer FTP monitor which constantly checks FTP servers for new files arrivals. When a new file is detected it executes a predefined action.
We are constantly working on making our software better. This dialogue is a fully functioning FTP client. It allows the users to see the files on the FTP server. But we did not stop there. The users can also download and upload files, create and delete remote directories.
A client makes a connection to the server on TCP port 21. This connection, called the control connection, remains open for the duration of the session, with a second connection, called the data connection, on port 20 opened as required to transfer file data. The control connection is used to send administrative data (i.e. commands, identification, passwords). Commands are sent by the client over the control connection in ASCII and terminated by a carriage return and line feed. For example "RETR filename" would transfer the specified file from the server to the client. Due to this two-port structure, FTP is considered out-of-band, as opposed to an in-band protocol such as HTTP.The server responds on the control connection with three-digit status codes in ASCII with an optional text message, for example, "200" (or "200 OK.") means that the last command was successful. The numbers represent the code number and the optional text represent explanations (i.e. <OK>) or needed parameters (i.e. <Need account for storing file>). A file transfer in progress over the data connection can be aborted using an interrupt message sent over the control connection.FTP can be run in active mode or passive mode, which control how the second connection is opened. In active mode, the client sends the server the IP address port number that the client will use for the data connection, and the server opens the connection. The passive mode was devised for use where the client is behind a firewall and unable to accept incoming TCP connections. The server sends the client an IP address and port number and the client opens the connection to the server.
FTP has no encryption tools meaning all transmissions are in clear text; usernames, passwords, FTP commands and transferred files can be read by anyone sniffing on the network. This is a problem common to many Internet protocol specifications written prior to the creation of SSL, such as HTTP, SMTP and Telnet. The common solution to this problem is to use either SFTP (SSH File Transfer Protocol) or FTPS (FTP over SSL), which adds SSL or TLS encryption to FTP.
A host that provides an FTP service may additionally provide anonymous FTP access. Users typically log in to the service with an 'anonymous' account when prompted for a username. Although users are commonly asked to send their email address in lieu of a password, no verification is actually performed on the supplied data.
Visual Importer ETL provides extended support for automation of compression and decompression operations, it supports a wide range of archive file formats such as
Data compression refers to the process of encoding large files in order to reduce the number of bytes. Smaller files occupy lesser memory space and are also easier to transfer over a network, which could be the Internet, intranet or a LAN. The automation of compression files can be of great help here.
Compression involves applying an algorithm to the required data that removes the redundant bits. Redundancy is very common in most types of computer files. Visual Importer compression mechanism gets rid of this redundancy thus compressing the file. Breaking them into smaller parts and sending them over a network can compress files that are extremely large. These files are then reassembled at the receiving end to get back the original data.
The latest version of our ETL Software support reading and posting Telegram messages using Bot API. This opens a new window of opportunity for chat automation.
Note: The user must talk to the Bot first
We would like to thank Vassalli from Galvolux for providing us with useful feedback
This article was created to assist our customers with data synchronization.
In our examples we will be synchronizing between different physical SQL servers, the same methodology can be used for any database
CREATE TABLE TBL_SOURCE(PRIMARY_KEY int NOT NULL IDENTITY (1,1),DATA varchar(50) NULL,UPDATE_FLAG varchar(5) NULL) ON[PRIMARY]GOALTER TABLE TBL_SOURCE ADD CONSTRAINTPK_TBL_SOURCE PRIMARY KEY CLUSTERED(PRIMARY_KEY) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY]GOCREATE TABLE TBL_TARGET(PRIMARY_KEY int NOT NULL, DATA varchar(50) NULL, UPDATE_FLAG varchar(5) NULL) ON[PRIMARY]GOALTERTABLE TBL_TARGET ADD CONSTRAINTPK_TBL_TARGET PRIMARY KEY CLUSTERED(PRIMARY_KEY)WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY]GO
Load new records only into the target tableOnce loading is completed source records must be marked as updated
Data is only inserted into the source tableThere are no changes to the source recordsThere are no deletions to the source records
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."
The monitor (Directory monitor) is a windows service that 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>.
is a starting point, it extracts the first three characters of the file name. The Result is stored in '<ClientID>' variable.
varFileName: String;ClientID: String;beginFileName := GetVariable('<Directory Monitor File Name>');ClientID:=UpperCase(SubString(FileName,1,3));SetVariable('<ClientID>',ClientID);
Checks if the client directory exists and if it does not it creates it together with subdirectories.
Every transformation has an ID. We use this ID to execute a specific transformation for a specific customer.
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
Sql script inserts a record for missing clients:
insert into Transformations([ClientID],[TransformationID])values ('<ClientID>',0)
Once the file is ready for ready processing it is moved into Staging Area.
executes the following SQL:
Select TransformationIDfrom Transformations where ClientID='<ClientID>'
The result is saved into <TransformationID> variable.
Upon completion, the file is moved into Processed Area.
Acts as a default error handler
Quite often it is necessary to make sure that only one copy of the package is being executed at a given time. For example, the package below is being executed every 2 minutes but it might take more than 2 minutes to complete.The package is creating text files it is not possible to open the same file twice for writing. So we have to check if the same package is being executed and if it aborts the execution.
This can be easily done by using SQL Data Check Action
Professional and Enterprise versions to follow
The File Operation Action can be used to perform different types of operations on files and directories such as create, move, delete, and also to set the attributes for files and folders. The different types of operations which can be performed by the File Operation Actions are shown in the below snippet.
One important benefit to note is that the File Operation Action can use a mask to work with multiple files so there is no need to use loops.
We do recommend using a filter during design to reduce the pressure on the server
When no user name/password are provided the current user is used
Insert into OBJECT_TYPES (OBJECT_TYPE,OBJECT_TYPE_NAME)Values('312','Active Directories')Insert into OBJECT_TYPES (OBJECT_TYPE,OBJECT_TYPE_NAME)Values('313','Active Directory')
INSERT INTO OBJECTS_TREE(OBJECT_ID, PARENT_ID, NAME, OBJECT_TYPE)Select OBJECT_ID*-6 as OBJECT_ID, PARENT_ID, 'Active Directory' as NAME, 312 as OBJECT_TYPEFrom OBJECTS_TREEwhere OBJECTS_TREE.OBJECT_ID=16
This is an important feedback message stored on the file which must be passed to the supplier
begin SetVariable('<FileData>',FileToString('C:\tmp\text.txt')); Result:=True;end;
Variables are used to pass parameters between various objects.For example, SQL script has failed and you would like to email the SQL to the developer.Insert <Sql Script> Into the email message and it will be replaced with actual SQL.
There are several ways to create variables1. Using script action2. Using transformation action3. Using calculation object within transformation action or package4. Using Set Variable Action5. Manually using Global Variables
I've been looking at your Advanced ETL Processor for taking data from Excel sheets into a MySQL database. We receive regular Excel sheets from about 20-25 suppliers quoting rates for services; each supplier has a different format (although some commonalities between them).
It is very interesting what you are trying to achieve and we would be happy to assist you in difficult cases. If some functionality is missing we will add it for you.
We are a small Accounting Company based in Melbourne. Over the last year, our customer base had grown twice and it is still growing. We spend a lot of time thinking about how we can optimise our business processes. The first thing we did create standard Excel templates for everything. For example, the 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 were constantly under the pressure and we had to employ agency staff on a temporary basis.
Not any more.
During routine "Google search" we discovered Advanced ETL Processor Ent which quickly becomes a cornerstone for automating our business processes.
By using Advanced ETL Processor Ent we were able to eliminate most of our manual tasks
It is like a spider that sits in the middle connected to all our systems.
Quite often it necessary to go through the list of values and perform a specific action for example load files from the directory and process them one by one.
Now it is possible to loop through the list of values
Looping through the list of the files:
Saving list of files into the file
Looping through the list of files created at the previous step
<Loop Value> holds current filename
I have 350+ text files in the same folder.
I would like to combine all 350 CSV files and line up the common columns but also include the extra columnsAlso, I would like to add a new column to each record which would be the file name.Also, a log of the operation that would identify the problem CSV (Ones unable to import/combine because if format errors)o add the file name and record name to the output file
The first thing we need to is to do create the list of fields this can be easily done with the help of validator, pivot and deduplication objects.
As usual, the list of fields is at the first line of the file so we need to generate file lines number and reject all lines except the first one.
How when we have the list of the fields we can process the data
The first thing we need to do is to place a list of fields next to the data
This can be easily done with keep value transformation function, plus we use Metadata function to get the source file name
Next step is to get rid of the first line of every file using validator + Is Equal Validation function
And the last step is to extract field values based on field name position
Feedback from the customer: Thanks for your help. Your solution was the only one to achieve this (tried all your competitors)
iPARS is designed to provide on-line real-time recording of Patient Acuity information by nursing staff onwards. The system architecture consists of the following 7 main elements:
iPARS is designed with the ability to extract Patient Ward Stay information from a variety of data sources. It can interface with text files, receive messages from HL7 and link to SQL\Oracle databases. The basic requirement of these data sources is the availability of the up to date Patient Ward Stay information.
The iPARS ETL Tool is based on the Advanced ETL Processor (AETL) software application supplied by DB Software Laboratory (http://www.etl-tools.com/etl-tools/advanced-etl-processor-enterprise/overview.html ). This system is configured to extract information from the ward stay text file. It is scheduled to run automatically every night at 4 o’clock. The information is loaded from the text file directly into the iPARS SQL database. The same application is used to receive and process all the HL7 messages.
Advanced ETL Processor Package example
The iPARS SQL database stores all the Patient Ward Stay information into DS_WARDSTAY table. The information is saved with Patient ID, Ward Code and Date as the primary keys. In addition to the Patient Ward Stay table, the SQL database contains the main WARD_STATS and BED_STATS table which holds Acuity data for each patient – ward -date-shift (3 shifts per day) combination. In addition to these main tables, the iPARS SQL database also holds a variety of lookup tables including Ward Lookup, Acuity Levels description, etc. Another very important part of the database is the security table USERS It holds the user name and password. Each user can access information only for a specific number of wards. The link between user name and ward is stored in USERS table as well.
The iPARS System Administrator unit is based on the Active Table Editor (ATE) software application supplied by DB Software Laboratory (
The iPARS Web form is designed using ASP .NET technology and is split into two separate parts. Part 1 is a user login facility that filters ward lists based on the user name. Part 2 enables the user to record Acuity information, ward data (number of admissions, absence, transfers, etc), patient notes, etc. At present, the form is designed and tested for PC technology, but in the future, the plan is to create additional versions for iPAD's and tablet PC's.
this element of the iPARS solution is still in the design stage. At present all the acuity reports and analysis are delivered using QlikView. This platform provides literally unlimited flexibility in designing analysis and will bring the system in line with the overall front end reporting technology to be deployed across the NHS Trusts.
Brings Patient Acuity information into the existing cost allocation process
Several of our customers have asked us recently how to run Advanced ETL Processor or Visual Importer ETL Package from Active Table Editor or any other application.
All of these customers use the agent for execution.
The agent reads the QUEUE table several times per minute if there is something to execute it runs it.
QUEUE table has a lot of fields and it is not very clear which values to use.
CREATE PROCEDURE [dbo].[EXECUTE_OBJECT]( @LogDirectory NVARCHAR(255) ='',@ComputerToExecuteOn NVARCHAR(255) ='', @ObjectID Int, @UseAgent Int = 1, -- 1 = True, 0 = False @ExecuteOnComputer Int = 1)AS BEGIN BEGIN TRAN-- Calculating IDDECLARE @ID Intselect @ID =max(ID)+1 from ID_GENERATOR where id_type=2update ID_GENERATOR set ID=@ID where id_type=2INSERT INTO QUEUE(QUEUE_ID,OBJECT_ID,OBJECT_TYPE,OBJECT_NAME,LOG_FILE,SUBMITED,STATUS,USE_AGENT,EXECUTE_ON_COMPUTER,COMPUTER_TO_EXECUTE_ON,COMPUTER_NAME,OSUSER )SELECT @ID as QUEUE_ID,OBJECT_ID, OBJECT_TYPE,NAME as OBJECT_NAME,@LogDirectory+'\Package_'+Cast(@ID as VARCHAR)+'.log' as LOG_FILE,getdate() as SUBMITED, 'P' as STATUS,@UseAgent as USE_AGENT,@ExecuteOnComputer as EXECUTE_ON_COMPUTER,@ComputerToExecuteOn as COMPUTER_TO_EXECUTE_ON,@@SERVERNAME as COMPUTER_NAME,user as OSUSERfrom objects_treewhere object_id=@ObjectIDCOMMITENDGO
CREATE PROCEDURE [dbo].[EXECUTE_OBJECT]
@LogDirectory NVARCHAR(255) ='',
@ComputerToExecuteOn NVARCHAR(255) ='', @ObjectID Int,
@UseAgent Int = 1, -- 1 = True, 0 = False
@ExecuteOnComputer Int = 1
AS BEGIN BEGIN TRAN
-- Calculating ID
DECLARE @ID Int
select @ID =max(ID)+1 from ID_GENERATOR where id_type=2
update ID_GENERATOR set ID=@ID where id_type=2
INSERT INTO QUEUE
SELECT @ID as QUEUE_ID,
NAME as OBJECT_NAME,
@LogDirectory+'\Package_'+Cast(@ID as VARCHAR)+'.log' as LOG_FILE,
getdate() as SUBMITED, 'P' as STATUS,
@UseAgent as USE_AGENT,
@ExecuteOnComputer as EXECUTE_ON_COMPUTER,
@ComputerToExecuteOn as COMPUTER_TO_EXECUTE_ON,
@@SERVERNAME as COMPUTER_NAME,
user as OSUSER
EXEC [EXECUTE_OBJECT] 'C:\TEMP','DBSLCOMPUTER',126,1,0
The rest of the parameters is self-explanatory
At the moment it can be only executed from the package script object.
Python integration is on very early stages and we still working on making it better.
Python is an interpreted, object-oriented, high-level programming language with dynamic semantics. It's high-level built-in data structures, combined with dynamic typing and dynamic binding, make it very attractive for Rapid Application Development, as well as for use as a scripting or glue language to connect existing components together. Python's simple, easy to learn syntax emphasizes readability and therefore reduces the cost of program maintenance. Python supports modules and packages, which encourages program modularity and code reuse. The Python interpreter and the extensive standard library are available in source or binary form without charge for all major platforms and can be freely distributed.