How to call a unconnected stored procedure in Pre-SQL using Informatica?

Question

I have created a mapping to truncate and load data in destination table. I used to stored procedure transformation unconnected in my mapping. The stored procedure doesn't have output result it will have there input parameters (db name, schema name, table name) itself. I tried to write different commands to call the stored procedure

:SP.sp_name('db name', 'schema name', 'table name') 
CALL sp_namE('db name', 'schema name', 'table name'); 
CALL SP_NAME(); 

but none of them worked.

The mapping is as follows:

SOURCE > SQ > TARGET  UNCONNECTED sp transformation.

Answer 1

Calling SQL prodedure - or any other SQL statement - as Pre- or Post-SQL doesn't require a transforation in the mapping at all. You can put any SQL that will be executed before or after the mapping runs. It means it will run when mapping is not running at all.

Now, if you need to fetch some input paramenters from some source in order to call your SQL Procedure, then you need to call the SQL Procedure by using the SQL Transformation within the mapping, read the paramenters, and call the SQ. You might also need a False-Filter to eliminate any output from sending to your target.

Your final mapping would be like:

Source -> Source Qualifier -> SQL Transformation -> Filter -> Target

Answer 2

In unconnected stored procedure transformation you have an option to execute the procedure whenever you required like before source fetch, after source fetch, before target insert, after target insert and normal. Below are the options in the snipping of the properties, please call the procedure in such a way and select required property of stored procedure type

The second option is, if your source and target is a database, then call the procedure in a pre or post in either source or target in the session task properties.