5 Replies Latest reply on Aug 17, 2021 11:47 PM by JanLeendert Wijkhuijs

    Mapping variable used in Post SQL Target Property not working

    sdepriest Guru

      I've set a value of a mapping variable named $$TotalRecords during a mapping run. For the purpose of this question, the value of $$TotalRecords is 3.  I want to use that variable value as a value in an INSERT statement used in the Post SQL property of a target in a session.  The INSERT statement runs successfully, but the value being returned for $$TotalRecords is zero.  See screen shots below.

       

      Here is the INSERT statement and where I specify to use $$TotalRecords.

      Insert statement using mapping variable

       

      The $$WF_TotalRecords workflow variable is used to reset and override the mapping variable to zero at the beginning of each session run.

      session log excerpt

       

      At the end of the session log, I can see the full INSERT statement and zero is displayed for $$TotalRecords, and I'm expecting this to be 3.

      The assignment of the mapping variable to the workflow variable is noted, and the persisted value of 3 is displayed for the mapping variable.

       

      post sql statement in session log

      Why isn't 3 being used in the INSERT statement in the POST SQL property?

       

      I feel like this is a basic understanding question, but let me know if I should tweak my configuration somehow.  Thanks.

       

      Sarah

        • 1. Re: Mapping variable used in Post SQL Target Property not working
          JanLeendert Wijkhuijs Guru

          Hi,

           

          According to the documentation it isn't possible the way you want to use it.

          See documentation on mapping variables.

           

          In order to get the desired result I think you should change the approach a little.
          At the end of the session you can pass the mapping variable to the workflow variable again.You can use the same mapping in a second session in which you can override the source filter

          with: 1=2 (so no data ingested) pass the workflow variable on to the session and perform the SQL in the second session.

          I'm not an expert on variables so hopefully someone else has a better suggestion.

           

          Regards,

          JanLeendert

          • 2. Re: Mapping variable used in Post SQL Target Property not working
            Nico Heinze Guru

            Unfortunately I have to second JanLeendert's advice. Mapping variables are updated (not only in the repository but also for following steps) only after all steps in the session have been processed. All of them.

            Which means that the value used for the post-session SQL is still the initial value of that mapping variable.

             

            There's no (direct) way around that, this "works as designed" (that's the term used by Informatica support engineers when they find out that some seemingly strange behaviour has been implemented correctly according to internal requirements documentation).

            So JanLeendert's suggestion is the safe way to go.

            Regards,
            Nico
            • 3. Re: Mapping variable used in Post SQL Target Property not working
              sdepriest Guru

              Nico and JanLeendert,

              Thanks for the responses. Well that's a bummer.

              I can see the correct value being assigned to the $$TotalRecords map variable during the debugger run and yeah, I have a subsequent link between sessions that use that value also, and that works fine also.

               

              The Post SQL Expression Editor lets you choose mapping variables to use in the SQL statement... why would they be there if they aren't the final value?

               

               

              • 4. Re: Mapping variable used in Post SQL Target Property not working
                sdepriest Guru

                All, I'm going to rollback to my prior version and go with adding in the target to the map and do the INSERT inside the map.

                 

                Thanks.

                 

                Sarah

                • 5. Re: Mapping variable used in Post SQL Target Property not working
                  JanLeendert Wijkhuijs Guru

                  Hi Sarah,

                   

                  I think that is the best idea.
                  You keep it visible in the mapping what happens and that's in my humble opinion where the logic should be. Especially when another developer needs to figure out what happens where.

                   

                  Once I have seen a SQL-query in a source qualifier simply to join 2 tables and name the fields needed.
                  In the session the same developer did an override of the SQL just to add a join condition and a filter condition.
                  I really wondered why this developer didn't simply add a user defined join in the source qualifier and a source filter.

                  Regards,

                  Jan Leendert