Future plans for ETL mapping generation
After the OWB exercises (although they’re not fully complete yet) I will have a go at generation Informatica mappings. Informatica does not supply its own scripting component as OWB does but it does have (limited) capabilities for mapping generation. The way I see it there are three options:
1. Use the tools supplied (Data Stencils, the Excel plug in)
2. Hack into the repository
3. Parse XML
The third option would probably yield the best results because the tools currently do not cut it. They’re too inflexible in their current state. For instance you can’t update mappings, you have to manually add your sources and targets after mapping generation and there are no options for user input (prompts / decisions). As for the second option, well let’s just say there are warranty risks involved :-).
I’m thinking of creating some sort of web application to upload XML source definition files and follow basically the same steps as I did with the OWB demo. But this time with this application should export XML files which can be (automatically) imported to Informatica using its command line options (pmrep). Perhaps the parsing and creation of these XML files can be done using php or Java or something like that. If anyone has any samples of this I would be very grateful! It will probably be a time consuming exercise so don’t expect anything soon. I’ll keep posting progress. One thing I do learned today is that you need the Informatica supplied DTD file to link to an XML. A DTD specifies the exact version and this has impact on way the XML files are structured. Before I do any of this however I need to complete the architecture sections in this guide. They already have become outdated to some extent!