How to use a stored procedure as a SOURCE in Informatica

In SSIS there is an ability to use a stored procedure as the source for an OleDB object.  The example would be for a stored procedure that returns some rows with the columns a, b, and c to return those rows via a stored proc, and then do something with them, say write to a file.

So where I work there is a push to start converting things to Informatica, and the training revolving around Informatica is based mostly on doing the "work" of a stored procedure inside of Informatica, IE Joins/merges, Unions, with at best some embeded sql or the use of views.

But no training on how to do what is trivial, simple, and generally good practice in SSIS to pull data from a stored procedure and then manipulate the data into a file, or another destination.

After some fiddling around with the Informatica powercenter designer I figured out a method for this.

Step 1.  Create a dummy Microsoft Sql Server source (or whatever database source you are using) and add the columns with the column definitions that you want to your source.

Lets say your source is:

create proc dbo.PersonCompany

SELECT
FIRSTNAME

,LASTNAME

,COMPANYNAME

from person join companyperson on person.personid = companyperson.personid 

join company on company.companyid = companyperson.companyid

Though this could be in a view, lets say that this was a far more complicated procedure and it eventually returned these results. 

The source we would create would have these columns with the appropriate lengths.

Step 2.  Create a mapping and place the source in the mapping.  This will generate a source and a mapping to a source qualifier.

In the source qualifier you can then override the sql statement to be "exec dbo.PersonCompany" and now it will use the stored procedure as a source.

After discussing this with our internal Informatica expert, he did note that this would be "hidden" visually during debugging, since you cannot add notes to the mapping in Informatica, and it would be harder to debug, but you have a lot of legacy SSIS packages that are being converted over to Informatica, it would be easier to convert them in place if you company has been using stored procedures heavily to manipulate and present data.