Recently I’ve been looking into a piece of software called ETL-Tools – Advanced ETL Processor Enterprise.
This software is designed to perform ETL operations, similar in which to Taskcentre does.
The software is quite well priced, with a single user licence being between $340 and $690 (£216 and £440). This is a purchase cost and includes a years worth of support. Annual maintenance is 20%, but is optional. A site licence is also available which allows unlimited users.
In this blog post, I’m going to cover creating an automated task to generate a PDF document with the sales totals for company, split by salesperson code. The report is designed to run daily and is sent to the sales manager in order to view the progress.
This is using the Nav demonstration database – Cronus, with Nav 2015, running on Server 2012.
For this task, we’re going to need 2 connections as follows:
- MS SQL Database – Connection to the Nav Database
- SMTP – In order to send the email
After opening Advanced ETL Processor, it’s a simple task of right clicking on the blank connections and choosing New. In the screenshot on the right, I’ve shown the Nav connection.
Creating The Report
On right clicking to create the report a wizard is shown. This wizard takes you through a few steps in order to create the basis of the report:
- Source Connection (In this case, it’s the connection we’ve created earlier)
- SQL Query
- Fields to add to the report
- Report Orientation
- Report Layout (Rows or Columns)
After completing the wizard, this opens up in design view for further editing and design:
For this task, I’ve used all default options, plus the following SQL:
Select [CRONUS UK Ltd_$Sales Header].[Salesperson Code], Sum([CRONUS UK Ltd_$Sales Line].Amount) AS Sum_Amount From [CRONUS UK Ltd_$Sales Header] Inner Join [CRONUS UK Ltd_$Sales Line] On [CRONUS UK Ltd_$Sales Header].[Document Type] = [CRONUS UK Ltd_$Sales Line].[Document Type] And [CRONUS UK Ltd_$Sales Header].No_ = [CRONUS UK Ltd_$Sales Line].[Document No_] Where [CRONUS UK Ltd_$Sales Header].[Document Type] = 1 Group By [CRONUS UK Ltd_$Sales Header].[Salesperson Code]
After creating the report, we need to create a package to perform the following steps:
- Run Report
- Save PDF to Disk
- Send Email with PDF Attachment
Creating the package is very simple, right click and choose add. We need to drag two “Blocks” onto the planner, “Report” and “Send Email” and join these up together:
The screenshots below shows the setup of these two steps:
The task can be run by either opening up the Package, right clicking and choosing “Run Package” (F3). Alternatively, under the schedule tab, it can be scheduled to run automatically.
In this example, I’ve set the email to be run manually and sent to my email address. Example in the screenshot below:
In due course I will do some further investigation into ETL Tools. Watch this space!
You should try Pentaho Data Integration. Great open source ETL Tool and no licence fees in the Community Edition.
Cheers… I’ll have a look into that 🙂