Capture the file that gives the error in SAP Data Services

dataservices job design
1

Hi ,

I have multiple files as source. I cannot predict the number of the files. All file structure is same.

I am using wild character to read the files. We have file name in the target tables. So it will give the file name in the target.

In the control table I need to load the file names & directories.

I have few queries in the requirement.

How can I capture the file names before loading into the table.

As of now I am using below command to capture the file name.

$G_FILE_NAME = exec(‘cmd’,‘dir ‘||’"’|| $G_REVINATE_PROPERTY_SRC_PATH||’\’||$G_FILE_LIST||’" '|| ‘/B’,8);

print($G_FILE_NAME);
$G_FILE_NAME = ltrim_blanks(rtrim_blanks(word_ext($G_FILE_NAME,2,’:’)));
print($G_FILE_NAME);

IF (wait_for_file($G_SRC_MAIN_FOLDER||’\’||$G_REVINATE_SRC_PATH ||’\’||$G_PROPERTY||’\’||‘competitors*.csv’,0,0)=1)
begin
$G_IS_FILE_AVAILABLE =‘Y’;

SQL(‘DS_XXXXX_ADM’,‘INSERT INTO ADM.SYS_FILE (FILE_ID,ETL_BATCHID,JOB_RUNID,FILE_LOCATION,FILE_NAME,FILE_PROCESS_DATETIME,TYPE,STATUS) VALUES ({$G_FILE_ID},{$G_ETLBATCH_ID},{$G_JOB_RUN_ID},{$G_REVINATE_PROPERTY_SRC_PATH},{$G_FILE_NAME},{$G_StartDate},‘csv’,‘STARTED’)’);
end
else
begin
$G_IS_FILE_AVAILABLE =‘N’;
SQL(‘DS_XXXXX_ADM’,‘INSERT INTO ADM.SYS_FILE (FILE_ID,ETL_BATCHID,JOB_RUNID,FILE_LOCATION,FILE_NAME,FILE_PROCESS_DATETIME,TYPE,STATUS) VALUES ({$G_FILE_ID},{$G_ETLBATCH_ID},{$G_JOB_RUN_ID},{$G_REVINATE_PROPERTY_SRC_PATH},{$G_FILE_LIST},{$G_StartDate},‘csv’,‘Not Available’)’);
end

if we have multiple files it is capturing the in the single variable.

How can I capture each file in the single row of the table?

Second issue. If job fails due to any one of the file then how can I capture that file problematic file name if we use the wild character for file list.

Thanks & Regards,

Ramana.

Ramana :india: (BOB member since 2009-04-30)

2

The source file object in the Dataflow has an option to provide the file name as a column. That’s the easiest way to do this.

eganjp :us: (BOB member since 2007-09-12)