Is it possible to fetch records from more than on DB for a SSIS report?
I've different databases configured. Consider as DB1 contains records from 2010-01-01 to 2010-12-31. DB2 contains records from 2011-01-01 to 2011-12-31. DB3 contains records from 2012-01-01 to 2012-12-31.
In UI having dropdown to select the any one of the database. Based on selection the respective DB will be hit to fetch the records and the result will be shown in SSRS report.
What I would like to do is that I want to get records from more than one databases at a time and show the report. Is is possible or achievable?
I know by joining more databases and make it as single DB it is possible. But I look for other opions because I'm not supposed to do that.
Please do suggest. Thanks in advance.
In UI having dropdown to select the any one of the database. Based on selection the respective DB will be hit to fetch the records and the result will be shown in SSRS report.
What I would like to do is that I want to get records from more than one databases at a time and show the report. Is is possible or achievable?
I know by joining more databases and make it as single DB it is possible. But I look for other opions because I'm not supposed to do that.
Please do suggest. Thanks in advance.
ASKER
But how the reportserver would access more than one database to fetch result? That is the challenge. Please do suggest.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Let me put this way.. Using repoting services how to achieve it? One RDL file could refer one data source which would execute one SP in particular database. Am I right?
How to refer more than one datasources to fetch records from more than one database?
I hope there is some tweaking in Reporting services side is required. Could you please direct me how to achieve that?
Thanks.
How to refer more than one datasources to fetch records from more than one database?
I hope there is some tweaking in Reporting services side is required. Could you please direct me how to achieve that?
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks.
Is there any changes you can make to dropdown?
I think if the dropdown only shows one database and both databases are on he same server, your only option is to create a job that runs daily that will pull data from tables in DB2 and save it to a new table in DB1, so you can get it later from one location.
If DB2 is on a different server you will need to create a linked server to access it