3 Replies Latest reply on Oct 1, 2018 3:47 AM by Nico Heinze

    How to dynamically change the Informatica connection attribute?

    New Member

      Hello Folks,

      Could someone help me with the below requirement?

      The naming convention of our SQL server DBs are <DB_Name>_<Current_Date>.

      We have a rule defined at DB level to automatically create a replica of existing DB with latest Current_Date, when the DB size reaches to a limit.

      Example:

      <DB_Name>_20150422 changes to <DB_Name>_20150423

       

      I don't want to side track, but we have a process to capture these new DB names in a SQL server table, so it isn't difficult to capture the latest DB name.

       

      But the real problem is this.

      I am sourcing from this DB and with a new Database created, my ETL should source from the latest version of the DB dynamically.

      So is there a way we can automate the process to dynamically change the Connect string inside the Informatica connection to point to the new DB?

       

      Thanks,

      Bharath

        • 1. Re: How to dynamically change the Informatica connection attribute?
          Guru

          Instead of using a fixed relational connection, use a connection variable (sort of a "session parameter" in the form $DBConnection_:::); then you only have to provide a parameter file with the correct connection name, and these connections can be created "on the fly" using the command-line utility "pmrep createConnection" to point to the correct database instance.

           

          That means: you need e.g. a batch file, command script, PowerShell script, or something similar which does these things:

          1. Create a relational connection to the correct DB instance,

          2. Create a parameter file for the workflows to start,

          3. Start these workflows with the newly created parameter file(s) using "pmcmd startWorkflow ... -pf ..."

           

          It may be possible to create only one relational connection and to update this using "pmrep updateConnection" to point to the correct database instance, but this will incur a point of potential failure: if you update a connection which is used by e.g. five workflows while these workflows are running, then the next session in each of these workflows would use the updated connection details, meaning they would all of a sudden point to a different database instance than before.

          From this point of view it may be safer to create new connections for each new database instance. Even though that means you have to clean up "old" relational connections regularly.

           

          Regards,

          Nico

          • 2. Re: How to dynamically change the Informatica connection attribute?
            Arpit Pandey New Member

            Hi Nico,

             

             

            I am having around 10 servers and all these servers will have the same table structure I want to change servers dynamically over entry of new data in a particular table and load data from different sources into an fixed target table, how we can change these using parameter file, I don't have idea much about parameter file.

             

            There is table which will have serve name over entry of new server name workflows run and load data into target table how connections can be changed dynamically.

             

            One more thing Informatica is installed over UNIX how we can use PMCMD commend to start workflow if there is any new entry in the table.

             

            Thanks in advance.

            • 3. Re: How to dynamically change the Informatica connection attribute?
              Nico Heinze Guru

              In order to "switch" between various servers, the idea of switching the connection dynamically probably is the easiest approach (but definitely not the safest one in terms of auditing actions). So first you have to decide whether you will opt for "auditing safety" (then you have to create connections "on the fly" whenever you need a new one) or for "ease of maintenance" (by changing attributes of existing connections every time you have to switch to a different server).

              Once you (resp. your organisation) has made this decision, you can go on to implement it.

              The "auditing safe" approach requires that you invoke pmrep to create new connections, the "easy" approach requires you to create parameter files "on the fly" and to parameterise the connections in your sessions.

               

              Setting connections to connection variables (special session parameters) is explained in the Workflow Basics Guide (part of the PowerCenter documentation set).

              pmrep and pmcmd are explained in detail in the Command Reference Guide (part of the Informatica platform documentation set).

              Parameter files are explained in detail in the Advanced Workflow Guide (part of the PowerCenter documentation set).

               

              Please accept that I can't write complete introductions into these fairly wide topics here. In the end this would be a waste of time; the PowerCenter manuals (and, for that part, the Command Reference Guide) contain fairly good examples for most topics, no need to repeat them here.

               

              Regards,

              Nico