How do I execute a SQL Server stored procedure from Informatica Developer (10.1, not Power Center)

0
votes

I am trying to execute (call) a SQL Server stored procedure from Infa Developer, I created a mapping (new mapping from SQL Query). I am trying to pass it runtime variables from the previous mapping task in order to log these to a SQL Server table (the stored procedure does an INSERT). It generated the following T-SQL query:

?RETURN_VALUE? = call usp_TempTestInsertINFARunTimeParams (?Workflow_Name?, ?Instance_Id?, ?StartTime?, ?EndTime?, ?SourceRows?, ?TargetRows?)

However, it does not validate, the validation log states 'the mapping must have a source' and '... must have a target'. I have a feeling I'm doing this completely wrong. And: this is not Power Center (no sessions, as far as I can tell).

Any help is appreciated! Thanks

1
sql-serverinformatica
Developer is a client tool still using PowerCenter. And a mapping needs source and target - just as it says in the log. Can you show the mapping? - Maciejg
Conceptually I do understand that any mapping needs a source and a target, but if I only execute a stored procedure in that mapping (or an INSERT) to which I am passing parameters and I don't need the return value, then I really don't need either. I guess I'm thinking in SSIS terms. So let me rephrase the question, what do you use as a source and target around a SQL transformation? I created a flat file as a target where the SQLError and the RETURN_VALUE go, but what should I use as a Source when the actual source are user variables in the workflow? I don't know how to export a mapping, sorry. - MariusD
The whole idea that I have to create a source and target that I don't really use just to make the mapping work makes me think I'm getting this completely wrong... Thanks! - MariusD

1 Answers

1
votes

Now with the comments I can confirm and answer your question:

Yes, Soure and Target transformations in Informatica are mandatory elements of the mapping. It will not be a valid mapping without them. Let me try to explain a bit more.

The whole concept of ETL tool is to Extract data from the Source, do all the needed Transformations outside the database and Load the data to required Target. It is possible - and quite often necessary - to invoke Stored Procedures before or after the data load. Sometimes even use the exisitng Stored Procedures as part of the dataload. However, from ETL perspective, this is the additional feature. ETL tool - here Informatica being a perfect example - is not meant to be a tool for invoking SPs. This reminds me a question any T-SQL developer asks with his first PL-SQL query: what in the world is this DUAL? Why do I need 'from dual' if I just want to do some calculation like SELECT 123*456? That is the theory.

Now in real world it happens quite often that you NEED to invoke a stored procedure. And that it is the ONLY thing you need to do. Then you do use the DUAL ;) Which in PowerCenter world means you use DUAL as the Source (or actually any table you know that exists in the source system), you put 1=2 in the Source Filter property (or put the Filter Transforation in the mapping with FALSE as the condition), link just one port with the target. Next, you put the Stored Procedure call as Pre- or Post-SQL property on your source or target - depending on where you actually want to run it.

Odd? Well - the odd part is where you want to use the ETL tool as a trigger, not the ETL tool ;)