First, how exactly did you create the FileName port in the flat file target definition?
Did you just enter this port manually? That's the wrong way.
Or did you create it using the button (when editing the flat file target definition in the Target Designer) with the dotted rectangle and the yellow F? That's the right way.
Second, that's strange. My guess is there is something wrong with the script; PowerCenter is (without dirty tricks) not able to create empty target lines, they are simply discarded from a flat file output.
Would you be so kind to post the script here that you're using?
1. I was doing it the wrong way. I probably missed the dotted yellow rectangle in the documentation and tutorials. Thank you.
2. I am defining a flat file target with a single column to which I assign a port with the following expression:
IIF(ISNULL(PREV_OFFICE) OR PREV_OFFICE != OFFICE,
'echo user ' || FTP_USER || ' ' || FTP_PASSWD || ' > ' || V_FTP_TMP_FILE || CHR(10) ||
'echo bin >> ' || V_FTP_TMP_FILE || CHR(10) ||
IIF( ISNULL(FTP_PATH), '', 'echo cd ' || FTP_PATH || ' >> ' || V_FTP_TMP_FILE || CHR(10) ) ||
'echo lcd ' || $$V_TMP_DIR || ' >> ' || V_FTP_TMP_FILE || CHR(10) ||
'echo put ' || FILE_NAME || ' >> ' || V_FTP_TMP_FILE || CHR(10) ||
'echo bye >> ' || V_FTP_TMP_FILE || CHR(10) ||
'ftp -n ' || FTP_SERVER || ' < ' || V_FTP_TMP_FILE || CHR(10) ||
'rm ' || V_FTP_TMP_FILE || CHR(10) ||
'cp ' || $$V_TMP_DIR || FILE_NAME || ' ' || $$V_FINAL_DIR || FILE_NAME || CHR(10),
'cp ' || $$V_TMP_DIR || FILE_NAME || ' ' || $$V_FINAL_DIR || FILE_NAME || CHR(10)
I expect to call the resulting flat file as a shell command when the task is over.
I am trying to mimic the behavior of a stored procedure we are replacing, so the final script is similar to what the store procedure does. However I am willing to use any other approach.
Ah, ok, you do write NULL records to the script. So it's no wonder that the script contains empty lines.
You might want to filter out the NULL records using a Filter transformation before writing them to the target flat file (the script you want to invoke).
BTW the script is short and concise, but it lacks error handling. That may cause trouble in future, so you might want to rethink this approach.
One potential solution would be to use a Java transformation to either FTP the file using some Java class performing FTP or to invoke single FTP invocations ("ftp -n ...") as single operating-system commands; then you can catch the standard output and error output of each FTP command in the Java code and react accordingly (e.g. by either moving away each transferred file to its archive directory if all went well or doing something else if anything went wrong).
I could post here the Java code for a Java Transformation for operating-system calls if you are interested. It's nothing too fancy or too difficult; Java just makes it fairly easy to "catch" the output of any OS command so that you can analyse it for error messages.