QVD File Management in Incremetnal Loads?

[#1]

I am attempting to build an Incremental Load process for a reasonably High volume system which uses an Oracle Database as its source.

As such I have split the Load process into a number of steps, which can be outlined as followed:

Step 1 (Extract): Read data from the DB and fetch any changes since the last run; store into timestamped QVD file for each table

Step 2 (Staging): Read the available timestamped files for a table, append them into monthly partitioned QVD files for each table

Step 3 (Transform): Load the appropriate Monthly files into the Datamodel.

The steps are not coupled, meaning that Step 1 may generate a number of timestamped extracts for a certain table, which will then need to be processed into the monthly QVDs.

The partitioning is done on the date the data is relevant to, not the update date - ie an extract file may contain data that needs to be split across multiple monthly files, which is one of the reasons Step 1 and Step 2 are separate.

The question I have is how to manage the timestamped extract files once they have been loaded? I do not want to load them again, so need to somehow mark them as processed, or move them or (possibly) delete them from the filesystem.

I cannot see any mechanism in the script to make those sort of changes, and I cannot see any examples of anything that deals with files after they have been used.

Does anyone have any suggestions, comments, or possibly even directions to abetter way to do it?

Thanks

Mark

[datanibbler · Accepted solution]

Hi Mark,

once you have loaded a qvd, the data is in memory, so you might delete the qvd file without risking any damage since you can store the data to qvd again.

You can do this using the EXECUTE command. I have that in some app of mine, somebody here in the Community told me. The syntax is >> EXECUTE cmd.exe /C del [UNC_path_incl_filename_and_ending] <<

I'm sure there would be other alternatives, but if this does not for some reason take longer than it should to execute, then this is a possibility.

HTH

[#3]

That sounds like exactly what I need. Thanks!

I'll report back once I have experimented with it.

Cheers

Mark

[#4]

Works Perfectly - Thanks

Note1: I am using move rather than del, but it accepts two command line arguments without issue

Note2: Use of EXECUTE requires the document to have this explicitly allowed in security.