top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

using informatica for one to one mapping for more than one source with different source structure

0 votes
828 views

How to use a workflow for one to one mapping for more than one source with source name as parameter with different source structure in Informatica power Center. I have a data migration where i need to unload 800 tables into delimited text files. Is there a way we can give the table name in parameter while running the workflow and the data will be unloaded to the output text file. Need help.

posted Jun 26, 2014 by Amit Sharma

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

1 Answer

0 votes

In Informatica PowerCenter, you'll need a separate mapping for each structure. Eg. if each one of 800 tables mentioned has unique structure and / or datatypes, you'll need 800 mappings. If the structure is shared across all 800 tables, one mapping can handle it. And if you've got 4 different structures, than 4 mappings will do.

If you do have 800 different structures, than it would be great to automate it. If I understand correctly, it would need to work like this:

http://powercenternotes.blogspot.com/2013/05/stage-mapping-generator.html,

but in the reverse direction (Table-to-File, rather than File-to-Table).

answer Jun 30, 2014 by Shweta Singh
Similar Questions
+1 vote
Informatica Cobol file with more than two 01-level groups

I am using COBOL file as source where 01 level groups are two. Following are the details.

In Output file first and last row are required and middle two rows are extra. Am I missing any setting or there is some other error.

Input data File:

2014001100450005000000001141107TD2798600000200120011201400090029+000000000024850+000000000000000+000000000000000000CATALOG    SCTEST TEST                     12 MAIN ST                         HINGHAM                  MA **********-111-111100000000000000000000000000000000000000000000000040000000000000001        0000 00000002786354800000000000000064486448
2015001000440007123456789123456789ABCD301088+123456789+1234567891234123456789ABCDEZZ1234ABCD12341234567890ABCDEFBCD**********ABCDEF12341234

OutPut file:

2014,11,45,5,2014001,100450005,1,141107TD27986,14,1107T,D27986,141107,TD,279,86,000,002,12,11,2014,9,29,248.50,0.00,0.000000000,CATALOG, , ,  ,SC,TEST TEST ,                    ,12 MAIN ST                         ,HINGHAM                  ,MA ,020430000,2043,0,020430,000,111-111-1111,0,0,0,0,0,0,4,0,0,0,1,        ,0, ,27863548,0,6448,6448
,2014,11,45,5,2014001,100450005,1,141107TD27986,14,1107T,D27986,141107,TD,279,86,000,002,120011.20,14000900.29,0,24,24,24,24,24,850+0,0,0,00000000,0,+000000000000000,000CATALOG    SCTES,,
2015,10,44,7,2015001,440007,123456789,123456789ABCD,12,34567,89ABCD,123456,78,9AB,CD,301,088,123,4567,8901,2345,6789,,,,1234567,8,9,0A,BC,DEF1234123,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,
2015,10,44,7,2015001,440007,123456789,123456789ABCD,12,34567,89ABCD,123456,78,9AB,CD,301,088,1234567.89,1234567.89,1234,123456789,123456789,123456789,123456789,123456789,ABCDE,Z,Z,1234ABCD,1234,**********ABCDEF,BCD**********ABCDEF,1234,1234

Desired Output:

2014,11,45,5,2014001,100450005,1,141107TD27986,14,1107T,D27986,141107,TD,279,86,000,002,12,11,2014,9,29,248.50,0.00,0.000000000,CATALOG, , ,  ,SC,TEST TEST ,                    ,12 MAIN ST                         ,HINGHAM                  ,MA ,020430000,2043,0,020430,000,111-111-1111,0,0,0,0,0,0,4,0,0,0,1,        ,0, ,27863548,0,6448,6448
2015,10,44,7,2015001,440007,123456789,123456789ABCD,12,34567,89ABCD,123456,78,9AB,CD,301,088,1234567.89,1234567.89,1234,123456789,123456789,123456789,123456789,123456789,ABCDE,Z,Z,1234ABCD,1234,**********ABCDEF,BCD**********ABCDEF,1234,1234

 Source File:
        environment division.
        select SAHDR-SAADMIN assign to "fname".
        data division.
        file section.
        fd  SAHDR-SAADMIN.
        01  STSHDR-RECORD.
        05  SAHDR-KEY.
        10  SAHDR-FISCAL-POSTING-DATE.
            15  SAHDR-FISCAL-YEAR            PIC  9(04).
            15  SAHDR-FISCAL-MONTH           PIC  9(04).
            15  SAHDR-FISCAL-WEEK            PIC  9(04).
            15  SAHDR-FISCAL-DAY             PIC  9(04).
        10  SAHDR-RELATIVE-DATE
                REDEFINES SAHDR-FISCAL-POSTING-DATE.
            15  SAHDR-DAY-IDNT               PIC  9(07).
            15  SAHDR-FILLER                 PIC  9(09).
        10  SAHDR-STORE-NUMBER               PIC  9(09).
        10  SAHDR-TRANSACTION-NUMBER-KEY     PIC  X(13).
        10  SAHDR-TRANSACTION-NUMBER
                REDEFINES SAHDR-TRANSACTION-NUMBER-KEY.
            15  SAHDR-REGISTER-NUMBER        PIC  X(02).
            15  SAHDR-TRANS-NUMBER           PIC  X(05).
            15  SAHDR-TRANS-SORT             PIC  X(06).
        10  SAHDR-MO-TRANS-NUMBER
                REDEFINES SAHDR-TRANSACTION-NUMBER-KEY.
            15  SAHDR-MO-ORDER-DATE          PIC  X(06).
            15  SAHDR-MO-DEPT-CLERK          PIC  X(02).
            15  SAHDR-MO-ORDER-BATCH         PIC  X(03).
            15  SAHDR-MO-ORDER-SEQ-NUMBER    PIC  X(02).
        10  SAHDR-RECORD-TYPE                PIC  X(03).
            88  SAHDR-HEADER-RECORD            VALUE '000'.
        10  SAHDR-TRANS-TYPE                 PIC  X(03).
            88  SAHDR-SALE-TRANSACTION         VALUE '001'.
        05  SAHDR-DATA.
        10  SAHDR-TRANSACTION-DATE.
            15  SAHDR-CALENDAR-MONTH         PIC  9(04).
            15  SAHDR-CALENDAR-DAY           PIC  9(04).
            15  SAHDR-CALENDAR-YEAR          PIC  9(04).
        10  SAHDR-TRANSACTION-TIME.
            15  SAHDR-REG-TRANS-HOUR         PIC  9(04).
            15  SAHDR-REG-TRANS-MINUTE       PIC  9(04).
        10  SAHDR-TOTAL-TRANS-AMOUNT         PIC +9(13)V99.
        10  SAHDR-CONVERSION-TRANS-AMOUNT    PIC +9(13)V99.
        10  SAHDR-CONVERSION-RATE            PIC +9(09)V9(9).
        10  SAHDR-TRANS-ORIGIN               PIC  X(07).
            88  SAHDR-POINT-OF-SALE            VALUE 'POS    '.
            88  SAHDR-MAILORDER                VALUE 'CATALOG'.
        10  SAHDR-TRANS-VOID-DURING-FLAG     PIC  X(01).
        10  SAHDR-TRANS-POST-VOID-FLAG       PIC  X(01).
        10  SAHDR-TRANS-ERROR-CODE           PIC  X(02).
            88  SAHDR-NO-ERRORS                VALUE '00'.
        10  SAHDR-RFS-LOCATION-TYPE          PIC  X(02).
        10  SAHDR-CUSTOMER-NAME.
            15  SAHDR-CUSTOMER-FNAME         PIC  X(10).
            15  SAHDR-CUSTOMER-LNAME         PIC  X(20).
        10  SAHDR-CUSTOMER-ADDRESS           PIC  X(35).
        10  SAHDR-CUSTOMER-CITY              PIC  X(25).
        10  SAHDR-CUSTOMER-STATE             PIC  X(03).
        10  SAHDR-CUSTOMER-ZIPCODE           PIC  X(09).
        10  SAHDR-USA-ZIPCODE
                REDEFINES SAHDR-CUSTOMER-ZIPCODE.
            15  SAHDR-CUSTOMER-FIRST-FIVE    PIC  9(05).
            15  SAHDR-CUSTOMER-LAST-FOUR     PIC  9(04).
        10  SAHDR-CANADA-ZIPCODE
                REDEFINES SAHDR-CUSTOMER-ZIPCODE.
            15  SAHDR-CANADA-CUST-ZIP        PIC  X(06).
            15  FILLER                       PIC  X(03).
        10  SAHDR-CUSTOMER-PHONE             PIC  X(12).
        10  SAHDR-SALESPERSON.
            15  SAHDR-CASHIER                PIC  9(07).
            15  SAHDR-HEADER-SALESPERSON     PIC  9(07).
        10  SAHDR-EMPLOYEE-SELLING-NUMBER    PIC  9(09).
        10  SAHDR-EMPLOYEE-PURCHASE-NUMBER   PIC  9(09).
        10  SAHDR-SHIPPING-RECORD-NUMBER     PIC  9(09).
        10  SAHDR-ADMIN-COUNTER              PIC  9(04).
        10  SAHDR-ITEM-COUNTER               PIC  9(04).
        10  SAHDR-REGTOT-COUNTER             PIC  9(04).
        10  SAHDR-STRTOT-COUNTER             PIC  9(04).
        10  SAHDR-TAXRCD-COUNTER             PIC  9(04).
        10  SAHDR-TENDER-COUNTER             PIC  9(04).
        10  SAHDR-USERID                     PIC  X(08).
        10  SAHDR-EMP-DEPT                   PIC  9(04).
        10  SAHDR-ERROR-CODE                 PIC  X(01).
        10  SAHDR-CUSTOMER-ID                PIC  9(15).
        10  SAHDR-LOYALTY-ID                 PIC  9(15).
        10  SAHDR-TRANS-TIME.
            15  SAHDR-REG-TRANS-HR           PIC  9(04).
            15  SAHDR-REG-TRANS-MIN          PIC  9(04).
        01  STSADMIN-RECORD.
        05  SAADMIN-KEY.
        10  SAADMIN-FISCAL-POSTING-DATE.
            15  SAADMIN-FISCAL-YEAR          PIC  9(04).
            15  SAADMIN-FISCAL-MONTH         PIC  9(04).
            15  SAADMIN-FISCAL-WEEK          PIC  9(04).
            15  SAADMIN-FISCAL-DAY           PIC  9(04).
        10  SAADMIN-RELATIVE-DATE
                REDEFINES SAADMIN-FISCAL-POSTING-DATE.
            15  SAADMIN-DAY-IDNT             PIC  9(07).
            15  SAADMIN-FILLER               PIC  9(09).
        10  SAADMIN-STORE-NUMBER             PIC  9(09).
        10  SAADMIN-TRANSACTION-NUMBER-KEY   PIC  X(13).
        10  SAADMIN-TRANSACTION-NUMBER
                REDEFINES SAADMIN-TRANSACTION-NUMBER-KEY.
            15  SAADMIN-REGISTER-NUMBER      PIC  X(02).
            15  SAADMIN-TRANS-NUMBER         PIC  X(05).
            15  SAADMIN-TRANS-SORT           PIC  X(06).
        10  SAADMIN-MO-TRANSACTION-NUMBER
                REDEFINES SAADMIN-TRANSACTION-NUMBER-KEY.
            15  SAADMIN-MO-ORDER-DATE        PIC  X(06).
            15  SAADMIN-MO-DEPT-CLERK        PIC  X(02).
            15  SAADMIN-MO-ORDER-BATCH       PIC  X(03).
            15  SAADMIN-MO-ORDER-SEQ-NUMBER  PIC  X(02).
        10  SAADMIN-RECORD-TYPE              PIC  X(03).
            88  SAADMIN-ADMINISTRATIVE         VALUE '301'.
        10  SAADMIN-TRANSACTION-TYPE         PIC  X(03).
            88  SAADMIN-POST-VOID              VALUE '088'.
        05  SAADMIN-DATA.
        10  SAADMIN-AMOUNT                   PIC +9(07)V99.
        10  SAADMIN-CONVERSION-AMOUNT        PIC +9(07)V99.
        10  SAADMIN-POS-EXPENSE-NUMBER       PIC  9(04).
        10  SAADMIN-TRANS-ID-NUMBER          PIC  9(09).
        10  SAADMIN-CHARGE-ACCOUNT-NUMBER      REDEFINES
            SAADMIN-TRANS-ID-NUMBER          PIC  9(09).
        10  SAADMIN-GIFT-CERTIFICATE           REDEFINES
            SAADMIN-TRANS-ID-NUMBER          PIC  9(09).
        10  SAADMIN-MDSE-CREDIT-NUMBER         REDEFINES
            SAADMIN-TRANS-ID-NUMBER          PIC  9(09).
        10  SAADMIN-COUPON-NUMBER              REDEFINES
            SAADMIN-TRANS-ID-NUMBER          PIC  9(09).
        10  SAADMIN-ORIGINAL-TRAN-NUMBER     PIC  X(05).
        10  SAADMIN-VOID-DURING-FLAG         PIC  X(01).
        10  SAADMIN-POST-VOID-FLAG           PIC  X(01).
        10  SAADMIN-USERID                   PIC  X(08).
        10  SAADMIN-EMP-DEPT                 PIC  9(04).
        10  SAADMIN-GIFT-CARD-NUMBER         PIC  X(16).
        10  SAADMIN-TOKEN-NUMBER             PIC  X(19).
        10  SAADMIN-TRANS-TIME.
            15  SAADMIN-REG-TRANS-HR         PIC  9(04).
            15  SAADMIN-REG-TRANS-MIN        PIC  9(04).
        working-storage section.
        procedure division.
        stop run.
0 votes
How do I use a Single Mapping to Handle Multiple Files with Different but Similar Formats in Informatica PowerCenter?

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.

0 votes
Informatica: reduced performance for simplet one to one mapping when target change from Oracle to SQL Server

I have simple informatica(9.1) mapping(one to one) which loads data from flat file to RDBMS

it take 5 mins to load to Oracle db and 20 mins to load same file in SQL Server 2008 R2.

Can there be any source/pointers for performance improvement

0 votes
Informatica Repository Query to get Workflow, Session, Mapping and Source/Target of Mapping

For cleaning up unused IPC-Sources I need a Repository Query for getting Workflow, Session, Mapping and Source/Target of Mapping.I have startet by joining REP_LOAD_SESSIONS and REP_TBL_MAPPING on mapping_id but only a fraction of mappings seem to be present in the joined output. I can't find the right tables to join to get the job done. Any help will be greatly appreciated!

+1 vote
Port to Port mapping using Mapping Architect for Visio in Informatica

I have almost 40 mappings to be created with similar structure. Instead of creating them individually, I was considering that I generate a template in Visio and then generate the mappings using the mapping template.

However, the column names in my source and targets are not consistent. Can I get a drop down while generating the mappings as we get while generating SCD mappings using templates?

Thanks a lot.

...