Solved

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

Posted on 2014-01-23
6
631 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Format Date fields 11 64
Consume a webservice via VB in Visual Studio 2015 3 45
CSS question 16 63
.NET universe documentation poster 2 24
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
I have a large data set and a SSIS package. How can I load this file in multi threading?
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

734 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