Aggregating in Monarch is done in the Summary tab - just define a summary with AccountCode as the key field and Amount as the measure with the default Sum(Amount) as the Calculation.
You can connect SQL Server Integration Studio to Monarch but not via a port number. Monarch can be automated via VB or COM methods and called from a script in SSIS. For some good background material on the automation of Monarch, www.******************** is a good starting point. #
However, since Monarch 11.5, desktop automation is not enabled by default, but requires a special licence key, and costs more.
If you want to automate Monarch tasks, Automator is the server version of Monarch, and this has a fully documented API and can be called nicely from SSIS.
Thank you for your descriptive response. It helped much.
I have another problem now. I detail it below;
I have an xml file which I load in Data Prep studio. From this file, I require 3 columns namely AccountCode, ReferenceCode and Amount. After that I save the model and the project file. Then I open the table in Data watch classic view and so I change there field properties from memo to character, character and numeric. Then I click on Summary tab and create a new summary. I choose AccountCode & ReferenceCode as Key and Amount as Measure (same as you advised in your reply)
I can see the summary of all account codes & reference so I then save the model and project files.
Now, the problem is when I open the model file in automator, and provide it the souce xml file, the automator only shows the total and not the individual AccountCode & ReferenceCode & Amount as shown below
Olly, How can I achieve the above output in the Automator?
Thanks for your help.
The summary export in the project should be defined as being in the drill state "Most detailed" - but that may not solve this. I'm not sure that Automator is going to pick up nicely on the Data Prep stage - so my approach would be to open the XML in Classic straight away and use templates to trap the data you need.
Can you email me the source XML? Feel free to redact it or just send a fragment if that's easier.