top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Truncate target Table based on rows in a Target Flatfile

0 votes
395 views

I have a workflow which loads data from a Flat file to a Stage Table after a few basic checks on a few columns. In the mapping, each time my check fails (meaning if the column has an invalid value) , I make an entry to a ErrorFlatFile with an error text.

Now , I have two targets in my mapping. One being the Stage table and the other is the Error Flat File.

What i want to achieve is this ? Even if there is one entry in the ErrorFlatFile (indicating there is an error in the source file ) , I want to truncate the Target Stage Table.

Can someone Please help me with how i can do this at the session level.

posted Sep 1, 2014 by Sunil

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

1 Answer

0 votes

You would need one more session. Make a dummy session (one that reads no data) and add a Pre or Post-SQL statement:

TRUNCATE TABLE YourTargetStageTableName

Create a link from your existing session to the dummy one and add the condition like:

$PMTargetName@numAffectedRow > 0

replacing TargetName with the name of your ErrorFlatFileName. The second session should only be executed in case when there was an entry made to the error file. If there will be no errors, it should not be executed.

answer Sep 2, 2014 by Shweta Singh
Similar Questions
+1 vote
Informatica: Converting a table into multiple target tables based on one column.

In my source table data is 

s_name,p_name,value
s1 ,   p1,     10
s1 ,   p2,     xyz
s1 ,   p3 ,    abc
s2 ,   p1 ,    20
s2 ,   p2 ,    xyz
s2 ,   p3 ,    abc

I want two target tables, first table is based on s_name s1, second table based on s_name s2. Both table contains contains p_name and value.

The target table data like as

Table s1

p_name,value 
p1,    10
p2,    xyz
p3,    abc

Table s2 

p_name,value 
p1,    20
p2,    xyz
p3,    abc
+1 vote
How to truncate a string in informatica based of size instead of length

How can i truncate a string in informatica based of size instead of length. My problem is i had to populate a VARCHAR2(4000) field but there are some special character in the data which have size of 2 byte.

So if there are 4000 character in a string and 10 are special character with 2 byte , then overall size becomes 4020 byte which is out of range of VARCHAR2. I had tried using VARCHAR2(4000 char) but even this is not working. I don't want to use CLOB.

What i am trying to achieve is that the string should have only 4000 BYTE of data. i.e in case of above example string should have only 3990 character and 4000 BYTE.

...