DataPump Join Problems......

[mdyoung]

First things first....Software versions:
Monarch Pro v8.01
Monarch DataPump v8.0
SQL Server 2000 (latest SRs)

What I'm trying to accomplish:
I have a text file that downloads daily from one of our systems which contains HL7 interface messages. There are only three pieces of data that I want to pump into a SQL database table: MessageID, MessageDate, & ProcessID (primary key in the db table). Since the text file may contain the ProcessID more than once (hence, the ProcessID could cause more than one HL7 message), I only want to pump the first occurrence of the ProcessID to the db table as long as the ProcessID doesn't already exist in the table. Basically, the final results in the db table should only contain the ProcessID once, along with the MessageID and MessageDate of when it first generated an HL7 message. I hope you caught all that because I almost confused myself writing this.

What I've done so far:
Using Monarch Pro, I've successfully created the model and project to pull only the entries that I need exported to the db table. This was done by using a couple of filters and an external database lookup which is joined by the ProcessID field. The model works great! I've setup a new DataPump process to grab the information based on my Monarch project and export it to the SQL table.

So what's my problem?
The exporting keeps failing. The Job Log gives me the following description as to why it failed:
The join data source is either missing or invalid or wrong password. Use the join database wizard to select a new data source.Finally....My questions:
What am I doing wrong? It can't be the "wrong password" thing because I saved that info within the project, and I DID use the "join database wizard" in Monarch. Also, I can't figure out how to include the "join data source" within DataPump. I thought DataPump used the information contained in the Monarch project file. I've even tried running the process after removing the primary key restraint from the db table, just to eliminate the "no duplicate entry" rule from the equation. Can DataPump handle the external lookups? If so, how the heck do I modify the join properties in DataPump? All help is greatly appreciated.

Thanks,

MDY

[Gareth Horton]

Hi

Just to clear things up, the external lookup info is stored in the model, not the project file.

Is the model file a mod or xmod?

If it is a mod, then you should definitely save it as an xmod with 8.01.

Make sure you go into Security Settings in Monarch and check either Always or Prompt for
"Save passwords from imported databases in imported databases in projects and models"

You could try deleting the external lookups and recreating them, then saving the model.

If this still doesn't work, you could open up the XMOD in notepad and add "PWD=<password>" to the contents of the <db_version> tag for the required lookup. In either case use a semicolon (";") to separate the "PWD= password" setting from other settings if necessary. This has the unfortunate side effect of leaving the password in plaintext in the XMOD, but will definitely work if all else fails.


Gareth

Originally posted by mdyoung:
First things first....Software versions:
Monarch Pro v8.01
Monarch DataPump v8.0
SQL Server 2000 (latest SRs)

What I'm trying to accomplish:
I have a text file that downloads daily from one of our systems which contains HL7 interface messages. There are only three pieces of data that I want to pump into a SQL database table: MessageID, MessageDate, & ProcessID (primary key in the db table). Since the text file may contain the ProcessID more than once (hence, the ProcessID could cause more than one HL7 message), I only want to pump the first occurrence of the ProcessID to the db table as long as the ProcessID doesn't already exist in the table. Basically, the final results in the db table should only contain the ProcessID once, along with the MessageID and MessageDate of when it first generated an HL7 message. I hope you caught all that because I almost confused myself writing this.

What I've done so far:
Using Monarch Pro, I've successfully created the model and project to pull only the entries that I need exported to the db table. This was done by using a couple of filters and an external database lookup which is joined by the ProcessID field. The model works great! I've setup a new DataPump process to grab the information based on my Monarch project and export it to the SQL table.

So what's my problem?
The exporting keeps failing. The Job Log gives me the following description as to why it failed:
[/size]quote:[/size]The join data source is either missing or invalid or wrong password. Use the join database wizard to select a new data source.Finally....My questions:
What am I doing wrong? It can't be the "wrong password" thing because I saved that info within the project, and I DID use the "join database wizard" in Monarch. Also, I can't figure out how to include the "join data source" within DataPump. I thought DataPump used the information contained in the Monarch project file. I've even tried running the process after removing the primary key restraint from the db table, just to eliminate the "no duplicate entry" rule from the equation. Can DataPump handle the external lookups? If so, how the heck do I modify the join properties in DataPump? All help is greatly appreciated.

Thanks,

MDY [/size]

[mdyoung]

Thanks, Gareth! I figured out the real problem with this and like most situations, the problem laid between the chair and keyboard. After looking at the xmod's code, I found out that I was using a different ODBC connection on my PC than what the server was using. Once I changed my connection to what the server had, everything worked fine. Lesson learned. Thanks again, Gareth, for all your help.