November 6, 2012 at 2:02 pm
Hi all,
Got the following situation:
Server A contains information about a particular field of a table that I want to populate for a report. Server B contains other information. I want to combine these two sets of information into a single table in a report.
The current solution I'm going with is to use SSIS to combine these two sets of information into a single database table, and then use SSRS to query this table for the report.
I don't really like this solution though - I personally hate to use static database tables to store snapshots, since it doesn't permit changes to the source data to be reflected in the report.
Does anyone have a better solution for me? And note that having a single stored proc / query using a linked server to join the two sources is not an acceptable solution for this problem.
March 5, 2013 at 7:34 pm
kramaswamy (11/6/2012)
Hi all,Got the following situation:
Server A contains information about a particular field of a table that I want to populate for a report. Server B contains other information. I want to combine these two sets of information into a single table in a report.
The current solution I'm going with is to use SSIS to combine these two sets of information into a single database table, and then use SSRS to query this table for the report.
I don't really like this solution though - I personally hate to use static database tables to store snapshots, since it doesn't permit changes to the source data to be reflected in the report.
Does anyone have a better solution for me? And note that having a single stored proc / query using a linked server to join the two sources is not an acceptable solution for this problem.
There's not really any other option. SSRS won't combine datasets for you so you need to combine them elsewhere such as in SSIS as you are doing or in a query. Why is the linked server option not acceptable? The only way to make sure the data is not a snapshot is do query it live.
If perhaps you don't have permission to create linked server objects on those two source database, what about using a third database as the launch point to query the other two? If you created a reporting/utility database like that you would have full permission to create whatever objects (linkedserver to the source databases) you like on there, of course being careful to ensure it is backed up with the rest of your backup policies.
Another possible option is to use multiple datasets in a tool like Excel PowerPivot and then mash them together in a pivot table. There's also other reporting tool options like Tableau that do multiple datasets very well but I suspect if you're on SQL 2005 then that's not an option.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply