Workflow Soluiton

I have 2 queries from 2 different databases that return arrays (collections) of:

QUERY 1
Name of Software1, GUID of Collection1
Name of Software2, GUID of Collection2
Name of Software3, GUID of Collection3

QUERY 2
Name of Software1, Cost of Software1
Name of Software2, Cost of Software2
Name of Software3, Cost of Software3


The name is the same but the data lives in 2 different databases (specifically, our Client Facing NS and our Asset NS databases).

I want to COMBINE these 2 arrays (collections) so it looks like:

Name of Software1, GUID of Collection1, Cost of Software1
Name of Software2, GUID of Collection2, Cost of Software2
Name of Software3, GUID of Collection3, Cost of Software3


I've tried a Multiple Value Mapping with no luck.

Is there a component or method to combine these 2 arrays into a single array so I can output it into a Grid Component with all data intact?

You will want to create a link between the two databases.  Then when running your query on database A, the part of the query that references database B should contain the full path to the other database.

Something like:

Use master

sp_addlinkedserver 'servername'

Servername is the database server for database B.

Then in your query you reference the linked the server's database.  Or in your case I guess your Asset database.

You should be able to accomplish what's needed via SQL... something similar to the following on "database 1":

SELECT a.software_nm, a.collection_guid, b.software_cost
FROM BLAH a
LEFT OUTER JOIN otherdb.dbo.FOO b on
b.software_nm = a.software_nm

otherdb is "database 2". This assumes that database 2 can be contacted easily from database 1.

To do it via workflow:
1. Create a user-defined type consisting of a string/guid/decimal - floating point. This is done via an integration library.
2. Use an Add New Data Element to add an array of the previous.
3. Use a For Each Item in Collection component to loop through your 1st query results
4. Use an Add Item to Collection component to add an item to the array created in step 2, mapping in the values of the indexed item in the collection (might require some fenagling to get into the 2nd recordset)

Hope this helps.