3 Replies Latest reply on Jan 23, 2019 3:33 AM by Nico Heinze

    Dinamically separate target in different flat files not working

    Carlos Eugenio Thopson New Member

      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 writen 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.

       

      Is there a better way to achieve this?

        • 1. Re: Dinamically separate target in different flat files not working
          Nico Heinze Guru

          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?

           

          Regards,

          Nico

          • 2. Re: Dinamically separate target in different flat files not working
            Carlos Eugenio Thopson New Member

            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,

              IIF(USES_FTP=$$V_YES,

                '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)

              ),

              NULL

            )

             

            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.

            • 3. Re: Dinamically separate target in different flat files not working
              Nico Heinze Guru

              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.

               

              Regards,

              Nico