I am creating a BODS job where the SOURCE is a file in AL11 path. For now after the completion of the job i am sending mail alert to the Business users with the Success alert and the file name that got processed.
But i am having a requirement where i have to check the file structure also in the first hand , for say if the source file is having any extra column or less column than it should be, then i am supposed to reject the whole file and send it back to the user with a mail where the subject line will be " The File Has been rejected" along with the in the mail body the file name will be there.
Can any one help me how can i make this happen.
Using TRY-CATCH Block can it be done and inside catch put a script for file issue , then again from where i am going to fetch the file name?
Please help me out with a simple solution.
One approach would be to create a separate file format for validation which has just one field of type varchar and with a length large enough to contain the largest record you would expect (like varchar 1000)
Then use that file format as the source in a dataflow. In a query, create separate fields in the target schema and use the word_ext function in the field mapping to parse/extract each column from the input field (which contains the whole record).
For example, column one's mapping would be: word_ext(query.InputFieldName,1,'<YourColumnDelimiter>') where <YourColumnDelimiter> is your column delimiter (i.e. a comma or tab, etc).
Column two's mapping would be similar but with the number 2 instead. Repeat for the number of columns you expect.
If your input file has column headings in the first record, you could use this to test that you got the correct number of columns with the expected column headings.