top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Can I have one workflow in informatica handle mappings for three different types of files based on the filename

0 votes
566 views

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?

posted Sep 3, 2014 by Sunil

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

1 Answer

0 votes

As I understand it you dont want to have three workflows but want the same session to handle all files? It is difficult to get the workflow to run different sessions based on filenames. The easiest way is to let the mapping handle this. If you list your filenames in a textfile and instruct the session to load using indirect file and name the textfile as source the source qualifier can give you the name of the file currently being loaded. Then the mapping can treat the flow differently based on that filename. Note that all files listed in the textfile will be processed at the same time.

answer Sep 8, 2014 by Amit Sharma
Similar Questions
0 votes

I have some files that I would like to consolidate into a single database table. The files have similar but different formats. The files look something like this:

FileOne:
•ColA : string
•ColB : string
•ColC : string

FileTwo:
•ColAA : string
•ColBB : string
•ColCC : string

FileThree:
•Col01 : string
•Col02 : string
•Col03 : string

The destination table looks like this:

TableDestination:
•ColFirst : string
•ColSecond : string
•ColThird : string

I want to develop a mapping that ETLs these three files into this one database, but because the column names are different, it looks like I'll have to develop three different mappings, or three different sources, or three different somethings. The problem is that my example is contrived: I actually have many different files that all have different formats and column names, but the data is all very similar.

I would like to develop a single mapping or workflow that can handle all of this by only adding a table that holds the column mappings. Such a table would look like this based on the sample files and sample table above:

TableMappings:
enter image description here

In this way, to edit a column mapping I only have to make an edit this this TableMappings table. I wouldn't have to make any changes at all to the mapping or workflow. Nor would I have to redeploy an application.

What would a mapping or workflow look like that could take advantage of something like this? I assume there'd be a flat file source that takes files from a folder. There would be something in the middle that uses this TableMappings table to map column names. Finally there would be a relational data object that represents my destination database table "TableDestination". I don't know how to put this together though.

+1 vote

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.

0 votes

When I look at many of the workflow last sessions runs in the workflow monitor I see the number of records that are picked by the transformation is different than the number of records that get updated or inserted in the target table.

For example my sql transformation picks 80,742 rows from the source table. but only 29,813 rows get loaded into the target table.

On further analyzing the workflow log file I can see it loaded both insertable records and updatable records:

WRT_8036 Target: W_SALES_ORDER_LINE_F (Instance Name: [W_SALES_ORDER_LINE_F]) WRT_8038 Inserted rows - Requested: 15284
 Applied: 15284 Rejected: 0 Affected: 15284 WRT_8041 Updated rows - Requested: 14529 Applied: 14529 Rejected: 0 Affected: 14529 

WRITER_1_*_1> WRT_8035 Load complete time: Wed Mar 19 04:41:24 2014

I am not able to figure out why would the workflows load lesser records than what source sql gives. and I would really appreciate some help in this matter.

Thanks

+2 votes

I have two sessions I a workflow like below

workflow1->session1->session2

I have a join_date column in a table in Mapping1 , in session1. I want to pick this join_date value and pass to mpping2/session2

If join date value changes in the table in session1 then the same value should pick and pass to session2. I will use this date value in a query in session2 .

Please suggest how to achieve this?

...