Exporting large amount of data

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 US. 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.

George

There are several ways of doing it.

Assuming that your source database type is SQL server
you can use something like this

select * from table_name where last_modification_date >=getdate()-1

Or if you want to pass variable into stored procedure

Add script object to the package
Use this as a script

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

than in sql script
type

exec my_procedure my_date

I think tab delimited files are better than CSV
People can type comma into database field but they cant enter tab

I will be able to give you a better advice if you email us couple of screenshots and repository backup

I hope that helps,
Mike