Presql and postsql in Informatica is not getting executed

1
votes

PreSQL and postSQL in Informatica is not getting executed.

ISSUE DESCRIPTION :

I have table in Microsoft SQL server. I am trying to update/insert this table using Informatica powercenter session by calling SP through Stored Procedure Transformation. But its not happening. After further digging up, I got to know that reason behind this are triggers on table that we are trying to update/insert. There are couple of triggers defined on the table and it has got on insert and on update triggers also. So I thought of disabling all the triggers on the table in PreSQL and enable them back again in postSQL of the session that I am running. But its not working. However when I execute the trigger disable statement directly on DB through Microsoft SQL server client and run the session, session is updating/inserting the records.

Below are the Presql and postSQL commands used by me:

BEGIN TRANSACTION
ALTER TABLE schemaname.tablename DISABLE TRIGGER ALL
commit;

BEGIN TRANSACTION
ALTER TABLE schemaname.tablename ENABLE TRIGGER ALL
commit;

Please let me know if I am going wrong anywhere/if there is any possible resolution for this.

1
sql-serverinformaticainformatica-powercenter
What is the error you are getting for this ... - shanmugamgsn
@shanmugamgsn..I am not seeing any error in session log. However at database level I do not see the changes that i am expecting by pre sql and post sql. - Chkusi
ok. i guess this wont work. we face same kind of issue. we used pre sql to delete the table through a procedure. But that time we couldn't because it PRE SQL and POST SQL were handled in different instances while our normal workflow was in different session. but if we do same from SQL it ran fine. - shanmugamgsn
so in your case too may be same issue.. this PRE and POST sqls might be handled in different instances in workflow which may be required as single instance. - shanmugamgsn
Thanks for your inputs.. - Chkusi

1 Answers

0
votes

your sql gets parsed by powercenter before going to the db.

Check the server config - there should be some option to send unparsed sql.