Is there a way to avoid the target table in informatica (powercenter)?

Question

In informatica mapping design, there must be a target table, but in my design, I only use informatica to call store procedures, and after they were called, all work has been done, so I don't need a target table to be inserted or updated. I used a non-exist table as the target table, and one nonsense field as the input port(cause there must be at least one input port!), then unchecked or the option(insert, update,delete) in the session configuration, so that the informatica would not generated DML SQL statements, avoiding "no table" errors. But then informatica treat the input row as reject row and try to write it into a bad file. And cause I unchecked the insert option, the session log showed that there was an error that it couldn't be insert into the bad file! Strangely, this error never showed in the monitor, and all session run successfully! It only appeared in informatica's meta table.

Is there a better way to avoid this problem, although it has no effect to my result? Is there a possibility to use a non-exist table and do nothing to it (include reject the input rows)?

Answer 1

No, you always need a target for the mapping to be valid. But I would rather work with a flat file target instead of a database table, you'll have much less work to do.

If you're on Linux / Unix, you can even route the file to /dev/null (use folder:/dev/, file:null) so the file is not actually written to the filesystem.

And using one dummy port is the right way. As you have said, you need at least one port, even if you don't really use it.

Answer 2

I had run into this same issue when i wanted to just execute a stored procedure and nothing else.

I solved this by creating a dummy source object that had one port and a dummy target with one port of the same datatype. In the source qualifier I added a SQL statement select 1 from dual (since it's Oracle).

I then added a filter object that was set to false. Then I connected the single port from the source/qualifier through the filter and finally to the target.

When the mapping is run, the source qualifier will return 1 row of one value, this will pass through to the filter but nothing will come out of the filter because the filter is set to false. This mapping will always be successful and valid because all ports are connected a nothing makes it to the "dummy" target thus no bad file logs or failure, etc.

Let me know if you need any clarification and I can update this answer.

Answer 3

As odd as this may sound (Unix systems): neither source, nor target need to exist.

Source (flat file): /dev/null, column DUMMY
Target (flat file): /dev/null, column DUMMY

And you don't need to use any databases for the session to succeed, nor use any filters. It runs.

Answer 4

Use a filter transformation just before the target and put filter condition 'FALSE'

No rows will go to the target