Solved

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

Posted on 2014-01-23
6
614 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 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
I have a large data set and a SSIS package. How can I load this file in multi threading?
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…

756 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