Execute informatica repository sql from informatica workflow

1
votes

I am new to Informatica. I am using Informatica 10.1.0 and I have created a workflow like below.

enter image description here

How can I make this workflow to execute the below informatica repository sql and fail the workflow is the count is greater than 0

select count(*) as cnt 
from REP_TASK_INST_RUN
where workflow_run_id = (select max(workflow_run_id) from OPB_WFLOW_RUN where WORKFLOW_NAME = 'wf_Load_Customer_Transactions')
and RUN_STATUS_CODE <> 0
4
sqlinformaticainformatica-powercenter
You need to create a >mapping< and a >session< to fetch data from database. In the example you've created a >command< task that can be used to execute shell commands. It seems you'd need some training on basics, try to get familiar with Informatica. Come back with some specific questions if you need help. - Maciejg
Just to let you know, using Command, we can even execute a stored procedure with "sqlplus". I have got the solution now. Will post as an answer :) @Maciejg - Sarath KS
Sure you can use sqlplus - but why bother using Informatica at all, in this case? :) Informatica is not a "sql-statement-exectutor" tool. And that is for a reason. - Maciejg
Using the sql, I need to understand if any job in another Informatica workflow failed (if you look at my sql, u will understand). Based on the result, I want to fail my workflow. Thats the requirement. - Sarath KS
Perfect! Create a valid mapping that will do that. With a REP_TASK_INST_RUN created as a Source, etc. Use Expression Transformation to invoke ABORT function if the value of 'cnt' port will be 0. - Maciejg

4 Answers

3
votes

You have shared the view of a workflow manager. in the Informatica Designer, you can create a mapping with the source as your table. In the Source Qualifier, add a dummy query and then load this data into a designated target. Post that you can create the workflow for your mapping and run it. https://www.guru99.com/mappings-informatica.html The above link should be a good reference.

Once you have a functional workflow, you may add a control task for the above check in Control task to make the workflow to fail if count of target rows <1.

1
votes

Design an informatica Mapping- - SQ contains the query you have provided and output of SQ will be passed to an expression. Create a mapping variable which stores this value. - with in the workflow using the post session workflow variable assignment- assign the mapping variable to workflow variable. - create an assignment task which checks the value of this workflow variable- if the count >0 , use the control task to fail the workflow.

0
votes

One way would be to create a mapping with your query inside of a SQL Transformation. Set it up to write to either a flat file or create a table in the DB. Add a filter to write the count to target only if it's greater than 0.

Then in the workflow, setup a session and link it to a Control Task that will fail if $TgtSuccessRows is < 1.

0
votes

You can create a dummy session to put your query inside the session, then link with the next workflow. The linkage u can put $count=0. Then the next wkf session will run when the count is 0.