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:-
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.
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 :
SET PRODUCT_TYPE = 'PGB';
hope it works...
check the session log properly ...that would give you better idea ..wat else can be reason ..
Thanks for that info, I'll give it a go.
Will I need to put BEGIN TRANSACTION at the beginning as well (I'll try that as well just in case)?
I tried adding in a Commit statement, but it failed again. I have attached the log from the session, and it appears that the UPDATE command is not recognised.
Not sure why this is. Any ideas?
My SQL statements are as follows:-
SET PRODUCT_TYPE = 'PGB';
Note that there are Carriage Return characters in between each line, in case that is what is causing the problem, but looking at the log, it seems it doesn't understand the UPDATE command. Is there something I need to put in to tell informatica that the following lines are T-SQL?
Any help you could give would be really appreciated.
Unfortunatley, my level of user did not allow me to download a log file (the option to view or downlaod the error log file was not available on screen), but one of my colleagues has managed to do that for me. I'll examine the log, and if I need further help, I may post it up on the board to see if you can help me.
Thanks for the advice
I have posted the log from my last failure (see my reply to Roshan earlier in the thread).
It appears the UPDATE command is not recognised.
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.
Thanks very much for your response. I really do appreciate your help on this.
The only place I appear to be able to put SQL commands is in the Data Sync Task on the final screen (Schedule) - see attached DataSyncTask.JPG.
I looked on the Field Mapping screen (the screen before on the Data Sync Task), but couldn't find anything on there for adding a SQL statement (see attached FieldMapping.JPG).
Maybe I am looking in the wrong place?
Any further help you could give would be very much appreciated.
Once again, thanks very much fro replying. I really appreciate you taking the time to help me.
I have attached a screen snapshot of how to start the Data Sync Task Wizard.
You basically go to the Data Services tab, choose Data Synchronization, and click on the New button. This will then start the Wizard.
The Data Target is an SQL Server 2000 database in my case, and there didn't appear to be anything on the screen where you configure the Data Target (step 3) to allow for a Post Processing SQL statement. The only place it appears that you can add Pre or Post Processing tasks is on the Schedule step (step 6) of the Wizard, and indeed the help file suggets you can either put in SQL conmmands (appropriate to the Target database) or Operating System commands.
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.
Thanks for the reply. That makes sense. I thought I could just run a TSQL SQL statement after my task has finished. My reply thread to Sathish has further details of what I've done.
Thanks for the reply, appreciate the help you guys are giving me.
Unfortunately, this doesn't appear to be an option in the Target section of the Data Syncronisation Task Wizard (step 3). The only option to put in Pre or Post Processing commands is on the Schedule section of the Data Syncronisation Task Wizard (step 6).
Although the Help suggests you can put either SQL statements (relative to the Target database) or operating System commands, it looks like you can in fact only put in OS commands.
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
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.
Thanks for the reply. I really appreciate you taking thte time to help me.
Does this mean that you cannot simply put in T-SQL commands in the Post Processing box of step 6, as the help file seems to suggest you can?
If I can't, I guess we will have to install SQL Server 2000 on the workstation that holds the secure agent, so that we get access to things like isql, osql and dtsrun.
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]