Skip navigation
Contact Us
20721 Views 23 Replies Latest reply: Feb 10, 2012 6:47 AM by Manoj Panda RSS
Matt Pinto Newbie 16 posts since
Feb 2, 2011
Currently Being Moderated

Feb 2, 2011 6:01 AM

Post-Processing SQL statements

Hi,

 

I'm fairly new to Informatica, and wondered if anyone could help m out.

 

I am trying to run a SQL statement (SQL Server 2000) as a Post-Processing command, but it seems to fail, and I'm not sure why.

 

The SQL statement is:-

 

UPDATE PLANN
SET PRODUCT_TYPE = 'PGB'

 

Where PLANN is a table in the destination database.

 

(I know this is probably easier to do elsewhere, but the actual SQL statement I want to run is a bit more complex than this, but I wanted to get to grips with a simple one first).

 

There doesn't apear to be any indication as to why it fails, but can anyone see why a simple statement like this might fail. I have even tried adding a semi-colon at the end, but it makes no difference.

 

Any help would be greatly appreciated.

 

Cheers

 

Matt

  • Roshan kumar Singh Newbie 2 posts since
    Aug 26, 2010
    Currently Being Moderated
    Feb 2, 2011 12:31 PM (in response to Matt Pinto)
    Re: Post-Processing SQL statements

    hi matt,

                try giving the table name along with schema name . and at the last give commit also . beacause you are running DML using infomtica ..so it will not commit automatically , so finally ur sql should be somethng like below :

     

    UPDATE schema.PLANN
    SET PRODUCT_TYPE = 'PGB';

    COMMIT;

     

    hope it works...

     

    check the session log properly ...that would give you better idea ..wat else can be reason ..

     

    thanks

    roshan

  • Sathish T Newbie 12 posts since
    Jan 14, 2011
    Currently Being Moderated
    Feb 2, 2011 1:24 PM (in response to Matt Pinto)
    Re: Post-Processing SQL statements

    Matt - Please provide session logs which would help understand the error better.

      • Sathish T Newbie 12 posts since
        Jan 14, 2011
        Currently Being Moderated
        Feb 7, 2011 12:16 PM (in response to Matt Pinto)
        Re: Post-Processing SQL statements

        Hi Matt,

         

        From the description and the log, looks like you are trying to run the SQL update from the 'post session success command'. This will work only for shell scripts and hence the error message. If you are trying to run a shell script which has the 'UPDATE' statement, then you will first have to establish a connection with the SQL server and then use the UPDATE statement (hope this is not what you want)

         

        If you are just trying to execute a SQL UPDATE statement after your workflow is completed, then you will have to edit session, go to mapping, select your target SQL table and in the properties, you will have the post SQL. You can add the UPDATE statement there which will get executed after the session is complete.

         

        Regards,

        Sathish

          • Sathish T Newbie 12 posts since
            Jan 14, 2011
            Currently Being Moderated
            Feb 8, 2011 11:56 AM (in response to Matt Pinto)
            Re: Post-Processing SQL statements

            Hi Matt - Not used the data synchronisation wizard however check out in the target screen if you can find one (hope your target is a database).

          • Sathish T Newbie 12 posts since
            Jan 14, 2011
            Currently Being Moderated
            Feb 8, 2011 11:58 AM (in response to Matt Pinto)
            Re: Post-Processing SQL statements

            Also let me know how do you launch this wizard so that I can try checking from my end and see if I can find out.

          • Manoj Panda Novice 25 posts since
            Feb 9, 2012
            Currently Being Moderated
            Feb 10, 2012 6:47 AM (in response to Matt Pinto)
            Re: Post-Processing SQL statements

            Hi Sathish ,

             

            just a quick question from where did you take the screen shots ? Are these Informatica screen shots ?

      • Currently Being Moderated
        Feb 13, 2011 11:58 PM (in response to Matt Pinto)
        Re: Post-Processing SQL statements

        Hi,

         

        I think update statement  required where condition......

         

        please check it....

  • Blue Ocean Novice 35 posts since
    Feb 1, 2011
    Currently Being Moderated
    Feb 7, 2011 12:14 PM (in response to Matt Pinto)
    Re: Post-Processing SQL statements

    Hi,

     

    From your log, the mistake you have maken is

     

    POST-SESS> CMN_1937 [Pre/Post Session Command] Starting shell command [UPDATE Sysbase.dbo.PLANN   SET PRODUCT_TYPE = 'PGB'; COMMIT;]

     

    Informatica is starting to call shell command not SQL statment. 

     

    Blueblue

      • Blue Ocean Novice 35 posts since
        Feb 1, 2011
        Currently Being Moderated
        Feb 9, 2011 7:14 AM (in response to Matt Pinto)
        Re: Post-Processing SQL statements

        Hi,

         

        You can try to use in your target-->Eidt-->Properties-->Update Override or Post SQL. Put your TSQL SQL statement and check result.

  • Elias Sha Expert 183 posts since
    Jun 3, 2009
    Currently Being Moderated
    Feb 10, 2011 9:21 AM (in response to Matt Pinto)
    Re: Post-Processing SQL statements

    First make sure Sql server 2000 command line utility (sqlcmd ?, check product documentation) needs to be present on the box where Informatica secure agent is installed.

     

    Can you successfully run the command outside of Informatica clouds task?

      • Run a sample test:  select * from table via sqlcmd to make sure you are connecting to your sql server

      • What type of authentication are you going to use?   Windows/ or sql?
      • since you have no where clause in your update statement you want to update all records.

     

    Once everything above is successful wrap the update command in .bat file and call that in Step 6 of the task.

    Does the windows account running the Informatica Secure Agent service has access to the directory and can execute permission on the .bat file.

  • Elias Sha Expert 183 posts since
    Jun 3, 2009
    Currently Being Moderated
    Feb 14, 2011 7:45 AM (in response to Matt Pinto)
    Re: Post-Processing SQL statements

    Matt,

     

    See the following docs:  https://community.informatica.com/docs/DOC-1680,

     

    Hope this is clear.

  • Anu C Newbie 7 posts since
    Aug 27, 2009
    Currently Being Moderated
    Apr 9, 2011 4:54 AM (in response to Matt Pinto)
    Re: Post-Processing SQL statements

    Hi, can anyone tell how to run an oracle sql statement from pre-post processing commands. I have the following statement in preprocessing and getting error. I have tnsnames.ora on the machine where secure agent is residing. The tnsnames.ora has an entry for the hostname and service_name that I am using in the sql statement. Am I missing anything else?

     

    sqlcmd -Q "insert into job_status(JOB_RUN_SEQ_NUM, JOB_NAME, START_TIME, JOB_STATUS) values(1923, 'CASE', SYSDATE, 'RUNNING')" -S "hostname" -d[service_name]

More Like This

  • Retrieving data ...

Bookmarked By (1)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points