I have a table which I need to split dinamically into multiple plain text tables (tab separated).
My source has a field that I plan to use. As an example my table has the following columns:
ID, CATEGORY, NAME, OFFICE, POSITION, SALARY
Target files would have the columns
ID, Name, Category, Position, Salary
and I will have one file per office.
I am using a Sorter transformation to sort by OFFICE, CATEGORY, ID
Then a expression transformation where I add column PREV_OFFICE and FILENAME, and the variable V_OFFICE. They are defined as:
PREV_OFFICE := ISNULL(V_OFFICE, ' ', V_OFFICE)
V_OFFICE := OFFICE
FILENAME := 'Employees_' || OFFICE || '.xls'
Then a Transaction control transformation with Transaction Control Condition as:
IIF(PREV_OFFICE != OFFICE, TC_COMMIT_BEFORE, TC_CONTINUE_TRANSACTION)
And finally the target with fields
ID, Name, Category, Position, Salary, FileName
So far according to documentation.
However it is not writing down separate files for each Office.
These are my settings for the Session Task in WorkFlow manager:
Merge Type: No Merge
Append if Exists: unchecked
Create Target Directory: unchecked
Header Options: Output Filed Name
Header Command: --blank--
Footer Command: --blank--
Output Type: File
Output file directory: $PMTargetFileDir\
Output file: Employees.xls
Reject file directory: $PMBadFileDir\
Reject file: shortcut_to_employees_xls1.bad
All data, including the FileName field is written down in Employees.xls, (format is right), and no Employees_<office>.xls files are created.
I have checked and indeed PREV_OFFICE and OFFICE have the expected values (they are different when there is a new value for OFFICE).
On a second issue, each file should be sent to a different FTP location, using a table with the following columns:
OFFICE, FTP_SERVER, FTP_USER, FTP_PASSWD, FTP_PATH
Currently I am using a LookUp Tranformation to get these fields, and creating, when PREV_OFFICE != OFFICE, the shell script instructions for moving each file (and null otherwise). The instructions are okay, but I get too many blank lines. (BTW this proofs the PREV_OFFICE and OFFICE fields are right for the purpose)
I have attempted to filter out the undesired lines but the validation fails claiming that two flows would be governed by one Transaction Control.
