5 Replies Latest reply on Dec 22, 2020 7:05 AM by Nico Heinze

    Override the value of Persistent Variable

    Saurabh Shrivastava Guru

      Hello All,


      I have a requirement where I have to generate a .xlsx file appended with Previous day date.

      (I have generated the .csv file in output and then converted it into .xlsx file via ssconvert utility)

      Here is what I have done...

      1. Declared a variable $$FILE_NAME_MPNG

      2. Calculating its value in expression transformation like this (Filename should have one day previous date)

      variable port: v_fileName --> 'sales_transfers_template_' || TO_CHAR(ADD_TO_DATE(SYSDATE,'DD',-1),'YYYYMMDD')


      Created another port of String type as below



      3. At Session level, in Target properties I have specified output type as Command and in command section I have written this

      cat - > $$CarSales_TGT_DIR/sales_transfers_template_`date -d ''yesterday'' '+%Y%m%d'`.csv


      4. Created a workflow variable $$FILE_NAME


      5. In Component Section --> Post Session Success Command, I have sepcified below command

      unix2dos $$CS_TGT_DIR/$$FILE_NAME_MPNG.csv && ssconvert $$CS_TGT_DIR/$$FILE_NAME_MPNG.csv $$CS_TGT_DIR/$$FILE_NAME_MPNG.xlsx


      6. In postsession_success_variable_assignment, specified below


      All I want is that the value of $$FILE_NAME_MPNG should be calculated via the logic specified in mapping and then in post session success command, its value should be replaced in unix2dos and ssconvert command so that the output .xlsx file will be generated.

      But the problem here is that, today I executed the session and If I check the persistent value it would be like below


      So When next day I execute my session then It will create the file with name sales_transfers_template_20201221.xlsx but this file is already created today. If I execute the session one more time then it will create the required file sales_transfers_template_20201222.xlsx


      Can anyone Please suggest me that what configuration I need to do so that whenever I run the workflow/session, the value of $$FILE_NAME_MPNG should be taken via the logic that has been there in mapping and NOT from the persistent value ?

      Or any other approach is also appreciated.

        • 1. Re: Override the value of Persistent Variable
          JanLeendert Wijkhuijs Seasoned Veteran


          You can also create a flatfile name in an expression in a mapping.

          On the Columns tab while editing a target in the Target Designer you have the option "Add FileName column to this table".In an expression prior to the target you have all the PowerCenter logic available to create a filename.

          I would go for this option and do a conversion to .xlsx afterwards.



          • 2. Re: Override the value of Persistent Variable
            Saurabh Shrivastava Guru

            Hello JanLeenDert,


            Thanks for your reply.

            Problem is not to create the .csv file , problem is something else as specified

            • 3. Re: Override the value of Persistent Variable
              Nico Heinze Guru

              May I suggest that you move the whole logic for the file name to a workflow variable instead of a mapping variable. Mapping variables have their gotchas (as you surely know), in particular the aggregation type. Workflow variables don't have an aggregation type, so working with them in my opinion is in many places (not always, of course) easier than with mapping variables.


              Of course I don't know whether this is feasible for you in this case, so you may want to initialise the mapping variable at the beginning of the session. What about initialising the mapping variable at the beginning of the session with a pre-session variable assignment?


              To be honest I don't really understand your issue in this case. If you could please provide another example, things might become clearer.




              • 4. Re: Override the value of Persistent Variable
                Saurabh Shrivastava Guru

                I have mentioned the logic to determine the value of mapping variable $$FILE_NAME_MPNG in mapping(in expression transformation). I ran my workflow today(Dec 22) and on the corresponding session, I can see the value of persistent variable $$FILE_NAME_MPNG as sales_transfers_template_20201221.


                Next, when I will execute my workflow tomorrow (Dec 23), then the same value of persistent variable $$FILE_NAME_MPNG ie: sales_transfers_template_20201221 will be used in below post session success command:

                unix2dos $$CS_TGT_DIR/$$FILE_NAME_MPNG.csv && ssconvert $$CS_TGT_DIR/$$FILE_NAME_MPNG.csv $$CS_TGT_DIR/$$FILE_NAME_MPNG.xlsx


                This I do not want.

                So when tomorrow(ie: Dec 23) I execute my workflow then instead of using the stored or saved persistent variable value (sales_transfers_template_20201221), it should use the value generated by logic specified in mapping and should use the value --> sales_transfers_template_20201222.


                If you can suggest how do I bring the whole logic of filename and Mapping Variable into the workflow variable then it would help.

                • 5. Re: Override the value of Persistent Variable
                  Nico Heinze Guru

                  Hand over the new value of the mapping to the WF variable as you already do.

                  Then use this workflow variable in a stand-alone command task instead of a post-session command.

                  This way the "new" (updated) value of the mapping variable will be used in the command task and not the old value before it's being updated in the repository.