Monarch Pro v8.01
Monarch DataPump v8.0
SQL Server 2000 (latest SRs)
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.
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.
The exporting keeps failing. The Job Log gives me the following description as to why it failed:
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.