How do you combine multiple result sets in SSRS?

Question

What's the best way to combine results sets from disparate data sources in SSRS?

In my particular example, I need to write a report that pulls data from SQL Server and combines it with another set of data that comes from a DB2 database. In the end, I need to join these separate data sets together so I have one combined dataset with data from both sources combined on to the same rows. (Like an inner join if both tables were coming from the same SQL DB). I know that you can't do this "out of the box" in SSRS 2005. I'm not excited about having to pull the data into a temporary table on my SQL box because users need to be able to run this report on demand and it seems like having to use SSIS to get the data into the table on demand will be slow and hard to manage with multiple users trying to get at the report simultaneously. Are there any other, more elegant solutions out there?

I know that the linked server solution mentioned below would technically work, however, for some reason our DBAs will simply not allow us to use linked servers.

I know that you can add two different data sets to a report, however, I need to be able to join them together. Anybody have any ideas on how to best accomplish this?

Answer 1

We had to do something similar (i.e. inner join 2 data sources from different servers). I believe the best way is to write your own custom Data Extension. It's not very difficult and it would give you the ability to do this and more.

Answer 2

You could add the DB2 database as a linked server in sql server and just join the two tables in a view/sproc in sql. I've done it, it's not hard and you'll get data in realtime.