5 Replies Latest reply on Jan 11, 2019 3:50 AM by ashish chaurasiya

    Preprocessing and postprocessing SQL commands

    Richard Watkins New Member

      Hello,

       

      According to documentation, I am able to run operating system commands or SQL commands via preprocessing and postprocessing options in Data Synchronization tasks. I haven't been able to find many examples online, especially for using SQL commands.

       

      I've tried SQL as basic as "SELECT 42 FROM dual", thinking perhaps the preprocessing and postprocessing commands would have automatic connections to the source and target, respectively, and that Informatica Cloud would be able to parse the difference between SQL commands and operating system commands. However, I would get errors like "CMN_1949 Error: [Pre/Post Session Command] Process id xxxxx. The shell command failed with exit code 1." (The process id would vary depending on what I tried.)

       

      I have three questions:

      (1) Is it possible to run SQL commands through preprocessing and postprocessing without wrapping them in operating system commands?

      (2) If yes to (1), what is the syntax?

      (3) If yes to (1), what endpoints can I connect to in the preprocessing and postprocessing fields? Can both fields hit both the source and target, and if so, what's the syntax for switching between endpoints?

       

      Thanks!

        • 1. Preprocessing and postprocessing SQL commands
          Ramesh Kalluri Guru

          Hi Richard,

           

          (1) Is it possible to run SQL commands through preprocessing and postprocessing without wrapping them in operating system commands?

          Yes, you can execute SQL commands through Pre/Post session command tasks.

          (2) If yes to (1), what is the syntax? You need to OSQL/SQLCMD utilities to call SQL commands through command task.

          (3) If yes to (1), what endpoints can I connect to in the preprocessing and postprocessing fields? Can both fields hit both the source and target, and if so, what's the syntax for switching between endpoints? We can't get the return values to mapping if you call SQL commands through command task. We should use DDL(create/alter) or DML(insert,update,delete) statements but not select statement.

           

          Thanks,

          Ramesh.

          • 2. Preprocessing and postprocessing SQL commands
            Phani Reddy Baddam Seasoned Veteran

            Ramesh,

                           I wonder how we can use OSQL/SQLCMD utilities in cloud.

            Does secure agent manage this option. Any specific steps will be helpful.

             

            Thanks,

            Phani

            • 3. Preprocessing and postprocessing SQL commands
              Anil Kumar Borru Guru

              You can run preprocessing and postprocessing commands to perform additional tasks. The Data Synchronization service runs preprocessing SQL commands before it reads the source. It runs postprocessing SQL commands after it

              writes to the target.

              You can use the following types of commands:

              ♦ SQL commands. Use SQL commands to perform database tasks.

              ♦ Operating system commands. Use  shell and DOS commands to perform operating system tasks.

              If any command in the preprocessing or postprocessing

              scripts fail, the Data Synchronization service fails the task.

              Rules and Guidelines for Preprocessing and Postprocessing SQL Commands

              You can run SQL commands before or after a task. For example, you can use SQL commands to drop indexes on the target before the task runs, and then recreate them when the task completes.

               

              Use the following rules and guidelines when creating the SQL commands:

               

              ♦Use any command that is valid for the database type. However, the Data Synchronization service does not allow nested comments, even if the database allows them.

              ♦Use a semicolon (;) to separate multiple statements. The Data Synchronization service issues a commit after each statement.

              ♦The Data Synchronization service ignores semicolons within /* ...*/.

              ♦If you need to use a semicolon outside of comments, you can escape it with a backslash (\).

              ♦The Data Synchronization service does not validate the SQL.

              1 of 1 people found this helpful
              • 4. Re: Preprocessing and postprocessing SQL commands
                Mothilal Rajesh New Member

                Give a good example . I want to create an index after Post-processing Commands.

                here is my MSSQL Command

                 

                 

                 

                USE DBA

                create index ind_name on actcustorder_summary(Name)

                GO

                 

                How do  I pass this command in Post processing command ?

                • 5. Re: Preprocessing and postprocessing SQL commands
                  ashish chaurasiya Active Member

                  Does any one knw how to process salesForce Data through pre processing Command.

                   

                  Table is truncating again and again Informatica Intelligent cloud services