Hi,
I have a scenario where I am receiving a pipe delimited file. For purposes of this example, I am naming the file ABCTEST.txt. The file contains header records that are indicated by a '00' value in the first column. The detail records are indicated by a '01' value in the first column. The footer record is indicated by a '02' value in the first column.
The detail records will be loaded to a staging table and then processed via a stored procedure. Ultimately, these data records will process successfully or they will become error records. I am required to create a file containing the good records and a file containing the error records.
The file containing the good records must contain the header records from the original file (ABCTEST.txt) and the detail records that processed successfully. The file name for the good records must contain the original file name as part of the new file name, i.e., ABCTEST.good
The file containing the error records must also contain the header records from the original file (ABCTEST.txt) and the detail records that became error records. The file name for the error records must contain the original file name as part of the new file name, i.e., ABCTEST.err
We understand how we can load the detail records to the staging table, retrieve the good records, and retrieve the error records. We are having a hard time trying to figure out how we can create the separate files for the good records and the records in error.
My first thought was to create a transformation where I am reading the original source file, filtering on the value for the header records (’01) and writing to a text file. This works well. It creates a file with just the header records. We would then append the detail records to this file after the processing of the data. The only problem I am having is trying to maintain the original file name as part of the new .txt file I am creating/writing to via the writer in my transformation.
Is there a way to do this or do you have any suggestions?
Thanks for your help!
Steve
