Solved

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

Posted on 2014-01-23
6
566 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
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 200 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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 300 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now