top button
Flag Notify
Site Registration

Can I set a Parameter based on the output of a Stored Procedure in Informatica PowerCenter?

+1 vote
188 views

In Informatica's PowerCenter, is it possible to set a parameter's value based on the output of a stored procedure? The parameter I want to set is a parameter I defined in a flat-file data object.

posted Feb 5, 2015 by Amit Sharma

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

Informatica uses two kinds of objects:
•Parameters - these cannot be modified
•Variables - these can be modified during the execution of a mapping using SETVARIABLE() function.

You can define a variable, run stored procedure somewhere in the mapping, connect the output of Stored Procedure to Expression Transformation and add an output or variable port tu execute SETVARIABLE($$YourVariable, sp_output) function. At the end of a successful session, the Integration Service will save the final current value of a mapping variable to the repository.

answer Feb 6, 2015 by Shweta Singh
Similar Questions
0 votes

I am using a stored procedure as a source in my Informatica mapping, and I have defined the SQL query in the source qualifier as

exec dbo.GET_ATTRIBUTES($$fromDate, $$toDate)

where $$fromDate and $$toDate are mapping parameters I have defined in a parameter file. I have tried a number of different ways of going about this and none seem to work, as the SQL query fails to validate.

So, my question boils down to this, is there a way to call a stored procedure while passing in two mapping parameters?

Thanks in advance

0 votes

I have a directory of files where each file is in one of three file formats. We can use the filename to determine the file format for mapping data. I would like to loop through all the files in the directory and based on the filename direct it towards the associated session mapping, delete the file on success, update the control table and then move to the next file. I'd like the execution to be deterministic. What are some of my options?

+1 vote

I get this error at the moment of create domain on informatica Powercenter 8.6.1 silent install :

Informatica PowerCenter 8.6.1

Cannot create domain.

The installer could not create the domain. Correct the error below and select
Retry. You must successfully create the domain to continue the installation.
DEFINE_NODE_STDOUT:
DEFINE_NODE_STDERR:java.lang.UnsatisfiedLinkError: pmjrepn (A parameter must be a directory.)
at java.lang.ClassLoader.loadLibraryWithPath(ClassLoader.java:952)
at java.lang.ClassLoader.loadLibraryWithClassLoader(ClassLoader.java:921)
at java.lang.System.loadLibrary(System.java:452)
        at com.informatica.powercenter.sdkint.repository.ILocaleManager.<clinit>(ILocaleManager.java:38)
at java.lang.J9VMInternals.initializeImpl(Native Method)
at java.lang.J9VMInternals.initialize(J9VMInternals.java:187)
        at
com.informatica.pcsf.infacmd.InfaCmdMainLoop.mainLoop(InfaCmdMainLoop.java:100)
at com.informatica.pcsf.infacmd.InfaSetupMain.main(InfaSetupMain.java:34)
Exception in thread "main" java.lang.UnsatisfiedLinkError: pmjrepn (A parameter
must be a directory.)
at java.lang.ClassLoader.loadLibraryWithPath(ClassLoader.java:952)

Any ideas?

0 votes

I have a stored procedure being called from an informatica mapping. There are no manual commits inside the procedure and the procedure is created with 'COMMIT ON RETURN NO'

I can see the updates done inside the procedure being committed. I think this could be because of the auto commit setting in the Informatica Server. (And I assume the 'commit after end of file' option in the task properties does not apply to stored proc calls)

If this is the case,assuming I do not want to commit anything even if procedure completes without errors, where do I find the autocommit setting (BD2 Driver configuration/Informatica configuration) and change it?

...