Visual Importer ETL support full range of file operations:
File operation example
In some ETL scenarios, when processing files, it is necessary to move the already processed files to a different location. With Visual Importer ETL you can accomplish that in a single step using the File System Task. In this example first step checks if we have something to load, than data is loaded into SQL server, New archive directory is created and files are moved into it. On success DBA is notified on error IT support team.
Create directory is using <archive> directory location, plus it creates new directory every day by using functions in directory name as follows:
GETSYSTEMVARIABLE('SYSTEM_DATE') returns current date in ‘YYYYMMDDHHNNSS’ format
If we want only part of date we can use LeftString(String,Count):String for example
LeftString(GETSYSTEMVARIABLE('SYSTEM_DATE'),8) will return only date part.
Now more complicated example for loading yesterday’s data:
LeftString(DecDateS(GETSYSTEMVARIABLE('SYSTEM_DATE'),'YYYYMMDDHHNNSS', 'DAY',1),8)
Note:
For complete reference of available functions please consult chapter 16 of documetation.
GETSYSTEMVARIABLE('SYSTEM_DATE') returns current date in ‘YYYYMMDDHHNNSS’ format
If we want only part of date we can use LeftString(String,Count):String for example
LeftString(GETSYSTEMVARIABLE('SYSTEM_DATE'),8) will return only date part.
Now more complicated example for loading yesterday’s data:
LeftString(DecDateS(GETSYSTEMVARIABLE('SYSTEM_DATE'),'YYYYMMDDHHNNSS', 'DAY',1),8)
Note:
For complete reference of available functions please consult chapter 16 of documetation.
Directory properties <archive> is replaced with c:\archive
