?
Solved

Is it possible to fetch records from more than on DB for a SSIS report?

Posted on 2014-01-23
6
Medium Priority
?
663 Views
Last Modified: 2016-02-10
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.
0
Comment
Question by:Easwaran Paramasivam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 35

Expert Comment

by:YZlat
ID: 39802692
Is database on the same server?

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
0
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 39802750
But how the reportserver would access more than one database to fetch result? That is the challenge. Please do suggest.
0
 
LVL 11

Assisted Solution

by:Murfur
Murfur earned 800 total points
ID: 39802766
Yes, you can - use the UNION statement as that combines the result of two queries into one result set e.g.

database 1, table
ID	Value
1	111111
2	333333
3	555555

Open in new window


database 2, table
ID	Value
1	222222
2	444444
3	666666

Open in new window


SELECT * FROM db1.table
UNION ALL
SELECT * FROM db2.table
ORDER BY value;

Open in new window


query result
ID	Value
1	111111
1	222222
2	333333
2	444444
3	555555
3	666666

Open in new window


Dropping the optional ALL will remove duplicates from the result set
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 39803500
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.
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 1200 total points
ID: 39805863
How to refer more than one datasources to fetch records from more than one database?

The method as explained by Murfur is your only option if you want to display the data in the same tablix.  This is what you need to consider:

A dataset cannot reference another dataset
A dataset references only and exactly one data source
A tablix references only and exactly one dataset (*)

So your best option is to ensure that your one dataset returns all data from all databases as required, either by putting a UNION ALL in the dataset query or in a SP somewhere.

You can of course put as many tablixes as you like under each other, each with their own dataset.  But I don't think that's what you're after here...

(*) you can use the lookup functions to get around this limitation but that has it's own limits (I don't think they apply in your situation, good to know they exist though): Looking Up Data On Different Sources
0
 
LVL 16

Author Closing Comment

by:Easwaran Paramasivam
ID: 39805879
Thanks.
0

Featured Post

Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My client sends a request to me that they want me to load data, which will be returned by Web Service APIs, and do some transformation before importing to database. In this article, I will provide an approach to load data with Web Service Task and X…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question