Solved

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

Posted on 2014-01-23
6
593 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

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…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

776 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