ETL and Data Integration tools - Modern ETL software for everyone

Hello Support

The application I have been working on for the Advanced ETL Enterprise Processor will take a daily extract from a database, extracting the data as CSV flat files from 21 different tables in the database, then zip up and the resulting CSV files and FTP it to a site in Europe. This daily extract is being used for a data warehousing application to centralise and aggregate data on a daily basis.

Currently the area I am having difficulty finding a solution concerns a suitable approach to setting up the 21 export data objects so that it is easy to enable the use of the Set variable object to control the export parameters for output files and manipulation of the date that the SQL procedures use to filter the table records that are collected with each processing run.

I have got a set-up for the output files that seems to be working fine. However, I am having lots of problems trying to get a variable set up that enables the package to set up a date (format mm/dd/yyyy) that can be referenced within the SQL procedures that are in each of the 21 export objects. In effect I am trying to set up a global variable that can be referenced within an export object SQL procedure - am I trying to do the impossible?

Should I be looking at setting up some sort of SQL temporary table to set up the required date for the extracts? It looked like the Set Variable object could do the job, but may be not? I am not that experienced with SQL – your advice/assistance on this area would be much appreciated.

Effectively the daily extract run will take place about 02:00 to 03:00 at the start of the new day – this means that the date that is required to be set up will be the current date minus 1 day. I tried using the DecDateS date function but could not get it to work inside a SQL proc.

I have scoured the site and could not find a solution to my date problem.

John

1 Try this

begin
SetVariable('extract_date',FormatDateTime('dd/mm/yyyy',now-1));
Result:=true;
end;

Result:=false; => script failed
Result:=true; OK

2
select
CheckLog.AlertFlags,
CheckLog.Allowed,
CheckLog.ClubNumber,
CheckLog.CustomerID,
CheckLog.Date,
CheckLog.ID,
CheckLog.StationID
from
CheckLog
where
cast (floor(cast(Date as float)) as datetime) = '05/31/2010' <=== this part depends on sql server regional settings and it might fail or it might return wrong data

this one is better CONVERT(datetime,'05/31/2010',101)

or if you want to use variables CONVERT(datetime,'extract_date',101)

Mike