CODEWITHSUNDEEP
informatica-powercenter
thotwielder
thotwielder

Reputation: 1707

informatica execute sql in sql transformation

Background: I am really new. Informatica Developer for PowerCenter Express Version: 9.6.1 HotFix 2

I want to execute a t-sql statement as one step in a work flow: 

    truncate table dbo.stage_customer

I tried create a mapping, add a sql transformation on it. Input above query in sql query window. I added the mapping to a workflow of just start, the mapping, and the end. When I validate the flow I got this error:

    The group [Input] in transformation xxx must have at least one port

I have no idea what ports are needed since this (the truncate statement) basically doesn't need input or output.

Upvotes: 0

Views: 3003

Answers (3)

Jim Macaulay
Jim Macaulay

Reputation: 5165

As Aswin suggested use the built in option in the session property.

But in the production environments user may not have truncate table access for the table in a database. In this case, informatica workflow will fail if you check the truncate target table option. It is good to have a stored procedure to truncate the target table and use that stored procedure in informatica mapping to avoid workflow failures in case of user having no truncate access to the database.

Upvotes: 1

Shrikant Soni
Shrikant Soni

Reputation: 2327

Use your query " truncate table dbo.stage_customer" in Pre-SQL command

Upvotes: 1

Aswin
Aswin

Reputation: 16

if you would like to truncate a target table before loading why don't you use the in-built option present in session properties?

goto workflow manager-> open session->mapping tab->click on target table listed left side->choose the property "Truncate table option" just enable it

to answer you question, I think you have to connect at least one input and output port into SQL transformation (because it is not unconnected). Just create dummy ports and try again

try this article - click here

Upvotes: 0

Related Questions