CODEWITHSUNDEEP
informatica
jshen
jshen

Reputation: 11907

Informatica ETL: How to determine the target database name from the exported xml file

I have an XML file from informatica representing an ETL job. It has source tags for the originators of data and target tags for the destination of data.

The source tags show a DBDNAME attribute (i think this is typically the name of the database) and a NAME attribute (I think this is the table or view name). 

<SOURCE BUSINESSNAME ="" DATABASETYPE ="Oracle" DBDNAME ="DBA QA" DESCRIPTION ="" NAME ="GL_LINES" OBJECTVERSION ="1" OWNERNAME ="DBASTA" VERSIONNUMBER ="4">

However, the target tags do not have a DBDNAME attribute

<TARGET BUSINESSNAME ="" CONSTRAINT ="" DATABASETYPE ="Oracle" DESCRIPTION ="" NAME ="LT_LOAD_CTL" OBJECTVERSION ="1" TABLEOPTIONS ="" VERSIONNUMBER ="2">

How do I determine the destination database name?

Note: The dtd is below. 

<!DOCTYPE POWERMART SYSTEM "powrmart.dtd">

Upvotes: 2

Views: 1622

Answers (3)

Maciejg
Maciejg

Reputation: 3353

Well... you can't. Informatica uses a Connection. This object is defined on Integration Service server and contains database sever and name. It is not stored in the Workflow XML file.

Edit: You can use pmrep command line utility to get connections and details. Create a batch or run from console the following set of commands:

First, connect to the repository:

pmrep connect -r RepositoryName -d DomainName -s SecurityDomain -n UserName -x UserPasswd

Once this is successfull, you can fetch all connections:

pmrep listconnections > ConnectionList.txt

If you need details of any particular connection, run:

pmrep getconnectiondetails -n ConnectionName -t ConnectionType

Keep in mind that still, in case of ODBC connections, you will get just the Connections String name, no server or DB details. It is a good practice to create ODBC with a naming pattern like ServerName_DBName to make this easier - although sometimes just the source name is used.

Make sure to run the cleanup once you're done:

pmrep cleanup

Upvotes: 2

prashant sugara
prashant sugara

Reputation: 321

In xml you can have target database name by filtering tags SESSIONEXTENSION with attribute TRANSFORMATIONTYPE ="Target Definition".

Inside this tag you will have a sub tag CONNECTIONREFERENCE having the realtional connection name with key CONNECTIONNAME if you have used object as a connection type.

If you have used Connection variable as connection type you will have that in key VARIABLE.

Upvotes: 1

Leo
Leo

Reputation: 908

you can try below Xpath expression to fetch Connection Name for all Target Definition in the workflow XML:

/POWERMART/REPOSITORY/FOLDER//SESSION/SESSIONEXTENSION[@TRANSFORMATIONTYPE ="Target Definition"]/CONNECTIONREFERENCE/@CONNECTIONNAME

if a connection variable is used, try below:

/POWERMART/REPOSITORY/FOLDER//SESSION/SESSIONEXTENSION[@TRANSFORMATIONTYPE ="Target Definition"]/CONNECTIONREFERENCE/@VARIABLE

Test the expression by uploading your xml in below url:

https://www.freeformatter.com/xpath-tester.html

Upvotes: 1

Related Questions