A customer has to load a bunch of flat files every night.
The file structure is the same only the last 8 chars of a 12 char file name change.

I have created a stored procedure that reads all the filenames and writes it to a file and then loops through each file name and feeds the name to a Data Flow, which in turn calls the source MFD, but can’t give a variable for the Dataset name

I have seen a very similar case “VARIABLE MFD FILE NAME for WebFOCUS posted by gregson06” where the suggestion was to move the DATASET=.. statement out of the mfd, but to where ???. the Data Flow ???? and how ????

Any help would be very much appreciated.

Thanks
Alkis

Hi

im not 100% sure of what your trying to do , but i reccomend using a stored procedre at the start of the process flow to set a variable from a db field to use for the file name.
I have done this for some of my dynamic file name requirements

TABLE FILE getprcparameters
PRINT filename
ON TABLE HOLD AS HLD FORMAT DFIX DELIMITER ,
END
-RUN
-READ HLD,&&filename
-TYPE &&filename

hope this helps

Do a search on how to use FILEDEF and use that in your stored procedure. You can use a variable as part of the FILEDEF statement in the stored procedure.

Here's a technique for processing multiple flat files as input to a DM Flow. As Jessica posted, you'll want to use a FILEDEF command for input file you want to process and leave the DATASET parameter in the master as a default just for testing. I think you should be able to adapt it to meet your needs.

Please open a hottrack case a new feature request for proecessing multiple flat files in the same format to register your need for this, hopefully it can be included in a future release.

Multiple flat files as input to a DM Flow
What do you when you have multiple flat files, all in the same format, that you want to process with DataMigrator? Start by creating a synonym (master file description) that describes one of the files. In this example we will call it SOURCE. Then create a DataMigrator data flow that processes the file. For testing purposes put a dataset name in the master, but note that you will override it at run time
In this example, for Windows, the application directory “files” contains input files with an extension of txt. We want to run the DM flow called pfile for each of them. Create a stored procedure like this (line numbers are for comments below):

quote:

1. !DIR /B C:\ibi\apps\files\*.txt > C:\ibi\apps\files\list.ftm
2. FILEDEF LIST DISK C:\ibi\apps\files\list.ftm
3. -RUN
4. -LOOP
5. -READ LIST, NOCLOSE &FILENAME
6. -IF &IORETURN NE 0 GOTO DONE;
7. FILEDEF FILENAME DISK C:\ibi\apps\files\file.ftm
8. -RUN
9. -WRITE FILENAME &FILENAME
10. -RUN
11. EX CMASAP REQ_NAME=pfile, CM_ASYNC=OFF
12. -GOTO LOOP
13. -ENDLOOP
14. -DONE
15. -CLOSE LIST
16. -EXIT

Notes:
1 Use DOS command dir /b to get a list of just the file names of all files in the directory files. Use output re-direction to send the list to the file list.ftm.
2. 5 Issue a FILEDEF for the file created using the ddname LIST; read a line from the file, containing a filename,
7, 9 Issue a FILEDEF and then write out one file name to file file.ftm
11. Use CMASAP to run the DM Flow called pfile
12,14 Loop until the end of the list file is reached.

Then create a stored procedure and in Process Flow, call it before the Data Flow. This stored procedure is called for each input file. It reads the file FILE.FTM created by the stored procedure above, and issues a FILEDEF for the ddname of SOURCE.

quote:

1. FILEDEF INFILE DISK FILES/FILE.FTM
2. -READ INFILE &FILENAME
3. FILEDEF SOURCE DISK &FILENAME
4. -RUN

Hi
Thanks Darron and Jessica for your input.

I ll try to describle what I am doing with some detail, hoping that its clear.

I am trying to load multiple files with the same DataFlow. That means that the DATASET name can not be constant but passed as a variable.
Since cannot use DATASET=&FILENAME in the MFD i have used a FILEDEF but dont seem to be passing the variable to the MFD. Here is what I have done.
I created an MFD called FD_CTLF, and a DataFlow that processes this.
I have created the following stored procedure that :
1. -DOS DIR C:\FD_FTP\*.G4 /O-N /B >C:\FD_FTP\CTLF_LIST.FTM
2. FILEDEF FILELIST DISK C:\FD_FTP\CTLF_LIST.FTM
3. -RUN
4. -LOOP
5. -READ FILELIST, NOCLOSE &FILENAME
6. -IF &IORETURN NE 0 GOTO DONE;
7. FILEDEF FILENAME DISK C:\FD_FTP\CTLF_FN.FTM
8. -RUN
9. -SET &FILENAME = 'C:\FD_FTP\' || &FILENAME;
10. -WRITE FILENAME &FILENAME
11. -TYPE <<<<<< FILENAME is &FILENAME
12. -RUN
13. EX CMASAP REQ_NAME=CTLF_DF, CM_ASYNC=OFF
14. -GOTO LOOP
15. -END LOOP
16. -DONE
17. -CLOSE FILELIST
18. -EXIT

1. With the DOS DIR command I get a list of all the filenames to be loaded and send them to be written to a file called CTLF_FTM.
2. I start a LOOP where each filename in FILENAME is read and written out to a file called CTLF_FN.FTM
13 Using CMASAP I run the Data Flow
14-18 Loop until the end of the file list

Then I have created a stored proccedure which i call it from wshithin a Process Flow and before the Data Flow
This stored procedure is that reads the CTLF_Fn.FTM created before (contains the file name to be loaded) and issues a FILEDEF for the ddname of the MFD i.e FD_CTLF

FILEDEF INFILE DISK C:\FD_FTP\CTLF_FN.FTM
-RUN
-READ INFILE, &FILENAME.A26
-TYPE >>>>>>>>>>>>> Dataset Name (in FILENAME) : &FILENAME
FILEDEF FD_CTLF DISK &FILENAME
-RUN

My problem is that when the DataFlow runs, it dont seem to read the variable that contains the filename or it dont use it anyway.

Thanks again

This message has been edited. Last edited by: Al_Kal,

Hi Clif,
thats the technique I am using but I must be doing something wrong because the DATASET name in the synonym IS NOT OVERRIDEN and every time the DF runs is using the DATASET name in the synonym thus loading the same file as many times as there are files in the LIST files

Thanks

I've never kept the DATASET= command in my synonym when I'm using a FILEDEF. I either use one or the other. I'd recommend removing the DATASET= from the synonym and controlling your files just using the FILEDEF.

Al

What might be a coplete other solution is copying all the files you want to readin to one other file.

suppose the files you want to handle are in C:\tohandle\
file20040701.txt
file20040702.txt
file20040703.txt

etc

DOS copy file2004*.txt c:\filetoproces\sourcefile.txt
DOS delete file2004*.txt (else next time you will read the same files)

and now you can use this source file in one session.

Jessica
I removed the DATASET= from the synonym but the problem remains the same.

Frank
I cant use this method cause in the target table we must include the filename for each line imported.

Can somebody see what is wrong with my code and the FILEDEF does not pass the dataset name properly ???????

Thanks people

This message has been edited. Last edited by: Al_Kal,

Please check the syntax and punctuation of your -READ statement to make sure it matches that in the supplied technique. And confirm that your -TYPE statement prints out the dataset name.

The -TYPE statement does return the right dataset name.
If I use the -READ as in the supplied technique, I get some errors. I followed the suggestions of the errors (like puting a -RUN) to correct it and have a clear run.

The funny thing that the output comments that I get, look like it worked but its not

eg when I run the PF with just 1 filename I get the following output
10/18/2007 17:34:06 DMS (ICM18164) Request FDTT_DATA/CTLF_PF was created/updated successfully for User: Administrator.
10/18/2007 17:34:06 DMS 10/18/2007 17:34:06 (ICM18122) Request - CTLF_PF (Owner: Administrator) submitted.
10/18/2007 17:34:06 DMS 10/18/2007 17:34:06 (ICM18015) DEP_5: procedure ctlf_sp started.
10/18/2007 17:34:06 DMS 10/18/2007 17:34:06 >>>>>>>>>>>>> Dataset Name (in FILENAME) : C:\FD_FTP\Ctlf25072007.g4
10/18/2007 17:34:06 DMS 10/18/2007 17:34:06 (ICM18039) DEP_5 ctlf_sp Return Code = 0
10/18/2007 17:34:06 DMS 10/18/2007 17:34:06 (ICM18027) DEP_6: flow ctlf_df started.
10/18/2007 17:34:06 DMS 10/18/2007 17:34:06 Request FDTT_DATA/ctlf_df submitted. Parent Job CTLF_PF; Dependency DEP_6
10/18/2007 17:34:06 DMS 10/18/2007 17:34:06 Please, wait for request to complete.
10/18/2007 17:34:06 DMS 10/18/2007 17:34:06 (ICM18762) Job ID: 2007-10-04-15-41-04t3cp00000082_00000002
10/18/2007 17:34:06 DMS 10/18/2007 17:34:06 (ICM18763) Request FDTT_DATA/ctlf_df complete
10/18/2007 17:34:06 DMS 10/18/2007 17:34:06 (ICM18039) DEP_6 ctlf_df Return Code = 0

Normally for reusablity we'd reccommend process flows that contain procedures and separate data flows.
But in this case it's important lease ensure that the stored procedure that issues the FILEDEF is part of the SAME flow that uses it as a source. That is if you open the flow you see the data flow, you click on the process flow tab of the same flow and you see a green start, a teal stored procedure and a PURPLE data flow.
This should be easier. If you haven't already done so please open a hottrack requesting support for reading multiple flat files in the same format. And if you are still haveing problem upload your fex/etg and mas/acx files to hotline. THank you.

Cliff I have opened a case since 27-07-2007 and its still open


I found a work around.
I deleted the Process Flow and inserted the SP that reads the filename in to the Data Flow and it works fine.



Thanks everybody for your help