How Do You Add Multiple Data Sources To A Report?
Dec 20, 2006I have a query that pulls information from 2 different servers. I have created two shared data source now I need to connect them to my dataset.
View 4 Replies
ADVERTISEMENT
Report Parameters And Multiple Data Sources
Nov 1, 2007Hi,
Am having difficulty with report/query parameters, where the report now regularly tells me that I must declare the scalar variable @Site.
I want to use 3 data sources :
1. to select a site from a list of sites - works fine and I can select at runtime
2. Once this is selected - I need to present a pair of dates to the user - min and max for data for the site - needs selection 1 to have been performed. Now I HAVE seen this work - once, the date selectors were greyed out until the site had been chosen, then they became available. Now I get the scalar variable error.
3. Finally I will pull the data with 3 parameters (site, startdate, enddate)
This was almost working, and the detail was produced for item 3, until I introduced the date selection option. Now neither item 2 or 3 will accept the users selected site - its from a drop-down.
All 3 queries are being performed by SQL SP's :
1. exec getsitelist - used to populate the dropdown to select @Site
2. exec getdates @Site - used to preset the start/end dates (NB I would really like calendar control here to select the date, with the value pulled from 1 to set the start point - but hey lets walk first ;-).
3. exec GetData @Site, @Start, @End
SOooo - can SSRS2005 even support dependent parameters of this type ??.
If so - whats the best way to create the parameters etc. ??. NB I can see all 3 parameter defs in 'Report Parameters'.
Many thanks - hopefully - for my sanity :-O.
Regards
Graham
Using Multiple Data Sources In Single Report.
May 7, 2008I am using SQL RS 2005. I have a report that is using multiple data sources from different offices. I can add these data sources in my report but is it possible that I can select a specific data source based on a selected parameter value. In this case user from each office, select their office location and be able to see the data from their office. I would appreciate any suggestions.
Sal
Single Report Multiple Data Sources
Jun 22, 2007I have multiple data sources that I would like to use for a single report. The data sources are separate due to security requirements. I have a table that I can get the Initial Catalog from. How do I get that field from the database into the data source connection string at run time?
I saw this link here but the light bulb didn't go off:
http://technet.microsoft.com/en-us/library/ms156450.aspx
"define a query that retrieves a list of data sources at run time"
Can somebody help me out here?
TIA,
Darren
Single Report With The Data From Multiple Data Sources
Jan 9, 2007Hi,
In my project i want a report. In that report data is getting from more than one data sources(systems). While creating data source view i used named query for both primary and secondary data source. But at the time of crating "Report Model" i am getting below error.
An error occurred while executing a command.
Message: Invalid object name 'Table2'.
Command:
SELECT COUNT(*) FROM (SELECT SerialNum, ModelNum AS com_model
FROM Table2) t
Is there any way to create a report with multiple data sources?
Reporting Services :: Report Builder 3 Using Data Sources From Multiple Servers?
Sep 5, 2013I have a report designed in RB3 that uses a data source from a SQL database that is on the report server. I want to add data to the report from an access database an a network drive.
I can add the second data source and create a data set to add data to the report. The dataset query returns data from the Access database but when I run the report I get the following error.
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'Feasibility'. (rsError OpeningConnection)
Also when I test the connection to the Access data base I get error ERROR (IM002) (Microsoft)(ODBC Driver Manger) Data source name not found and no default driver specified.
I noticed that Report Builder is connecting to the report server. If I disconnect from the report server I can a can connect to the Access data base but not the SQL database.
How can I get the report to run against both data sources?
This Feature Remote Access To Report Data Sources And/or The Report Server Database Is Not Supported In This Edition Of Report
Jun 16, 2006SQL server 2005 express reporting problem.
error message:
This feature "remote access to report data sources and/or the report server database" is not supported in this edition of reporting service
I got this error message when I try to connect to database hosted in another PC running SQL server 2000.
Is it true that SQlL server Express can only use Local Database Engine to host the database?
Remote Access To Report Data Sources And/or The Report Server Database Is Not Supported In This Edition Of Reporting Services.
Apr 24, 2006I'm using SQL Express with Advance Services & I get this error when I try to deploy my reports. Why do I get this error
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'EDPSYS'. (rsErrorOpeningConnection)
The feature: "Remote access to report data sources and/or the report server database" is not supported in this edition of Reporting Services. (rsOperationNotSupported)
Justin Song
Using Multiple Data Sources
Jan 24, 2008Not sure if this is possible, here is the scope:
I have a vb program that calls a sp. My vb program connects to DataSource1, Catalog1 and to DataSource2, Catalog2. My sp resides in Catalog1 using a table from catalog1 and a table from catalog2.
Is this possible to do?
Multiple Data Sources For A Dataset
Aug 23, 2007
Hey everyone,
I am trying to combine like data from two different data sources into a single data set. Is there anyway I can do this? It seems like I can only add one data set, but is there some sort of workaround I could use?
thanks,
Keith
Using Multiple Data Sources For Single..
May 7, 2008
Hi,
I am new to Reporting Servies. I need to create a report that is shared by different offices, each office has it own database. So I will need to include multiple data sources. I want users to select their office first (parameter) and then based on their selection they can see the report with the data source from their office. Can I do it in one report. Or do I have to create multiple version of same report for each office. I would appreciate any help.
Regards,
Hasn
Selecting A Single Data Source From Multiple Data Sources
Aug 4, 2006Hi,
I am pretty new to SSIS. I am trying to create a package which can accept data in any of several formats. i.e. CSV, Excel, a SQL Server database/table and import the data into my destination database.
So far i've managed to get this working OK. However I am now TOTALLY stuck. I'm currently trying to just concentrate on the data sources being a CSV (using a Flat File Data Source) and/or an Excel Spreadsheet.
I can get the data in and to my destination using a UNION ALL component and mapping the data sources to it so long as both the CSV file and the Excel spreadsheet exist.
My problem is that I need my package to handle the possibility that only the CSV file might exist and there is no Excel spreadsheet. In which case i'd like the package to ignore the Excel datasource completely. Currently either of my data sources do not exist I get errors and the package terminates.
Is there any way in SSIS that I can check all my data sources to see which ones exist (i.e. are valid). If they exist I want to use them. If it doesn't exist i'd like to disgard it (without error - as long as there is a single datasource the package should run)
I've tried using the AcquireConnection method in a script task on each of my connections, hoping that it would error if the file/datasource did not exist. It doesn't though (in the case of an Excel datasource it just creates a empty excel file for me).
The only other option I can come up with are to have seperate packages depending on the type of data we want to import and then run a particular package depending on the format of the source data. This seems a bit long winded. I am pretty sure I must be able to do what I want to achieve but I can't work out how.
I'll be grateful to anyone who can send me any tips/hints/links on how I can achieve this.
Many thanks
Rob Gibson
Stored Procedure For Multiple Data Sources
Jul 22, 2004Is there a best way of writing stored procedure to access data from multiple databases. we have an archive database and a live database. I need to retrieve the data from both and merge together, perform calculations and then display the data.
Thanks for any inputs.
Building An Application On Multiple Data Sources
Jul 6, 2006ASP.NET on SQL ServerI've been asked to quote for developing a system to expose data on aweb application. Most of the data will come from SQL Server DBslocated on a single box. However, some of the data will be sourcedfrom ORACLE which is located on a different box. It may be necessaryto create VIEWS and Stored Procedures joining these DBsDoes anyone have any pointers, clues, hints, tips or pitfalls that Imight consider while making my proposal? What sort of extracontingency should I allow for the connection to ORACLE? Should I doall the data retrieval on the DB server, or should I do it on the Webserver?Any thoughts would be appreciated.ThanksWilliam Balmer
View 2 Replies View RelatedReporting Services And Multiple Data Sources
Sep 19, 2005I have a group of reports that are the same for 3 different companies, the difference is the data connection. Is there a way I can change the data connection based on a variable passed in at the time of report execution?
View 12 Replies View RelatedInsert Data From Multiple Sources To A Single Table
Sep 24, 2015I am have a situation to insert data from multiple sources to a single table.
i.e., multiple and concurrent insert on same table
Will it lead to dead lock at any point? is there any possibility?
How insert will work ? What is the architecture ? Any references to read?
Data Flow Multiple Sources To Populate A Destination
Nov 28, 2007I have 5 or more tables to join to get a particular output which has to be sent to a destination table. In the 5 tables some are inner joins and some are left outer join. I am opting for stored procedure at this point. But I would like to know how can this be done in data flow transformations having multiple souce and merge joins or any other alternates. I tried using merge join, but this does not accept more than two tables.
I saw this simple post which kick started me to use ssis transformations to stored procedures. But I encounter issue.
http://www.mssqltips.com/tip.asp?tip=1322
error
"The destination component does not have any available inputs for use in creating a path".
Please advice alternates
MULTIPLE DATA SOURCES WITH SAME TABLE SCHEMAS IN SSIS
Apr 2, 2006
Hi,
I have the following scenario: N identical Databases (corresponding to different Fiscal Years, with names <Company Name>.<YEAR>). We want to consolidate the N DBs to a New Datawarehouse.
In SSIS we have designed a Dataflow that reads through a OLE DB Source (Connected to one of the N Databases) and maps to a OLE DB Destination (Connected to the NEW DB).
The question is, how we loop in SSIS through the N identical Connections, so to repeatedly execute the designed Dataflow, each time with a different Connection?
Thanks in Advance,
--Dimitris Doukas
Using Other Editions Of SQL Server For Report Data Sources And/or The Report Server Database Is Not Supported In This Edition Of Reporting Services
Sep 9, 2006Hi My project is in .NET 2003 i.e. framework 1.1 and database in SQLServer 2000. But the reports have been developed using SQLServer 2005 Reporting Services. Now when I am trying to deploy them through deployment project of .NET its giving me following error:"Using other editions of SQL Server for report data sources and/or the report server database" is not supported in this edition of Reporting Services. Now I am really confused with this. Can any one please guide me regarding this ASAP. Thanks, Falguni
View 4 Replies View RelatedUsing Object Data Sources In A Report - For Web Apps? (VS 2005)
Sep 4, 2005In VS 2005, using the ReportViewer control on a form in a Windows app, I've created an Object Data Source for the report by following the walkthrough in the documentation, it works.
View 4 Replies View RelatedReport Manager On 64 Bit Machine Does Not Recognize 32 Bit Data Sources
Aug 22, 2007I'm a report developer who has created some reportsd against sybase data on another server. I am developing on a 64 bit Server 2003 box using SQL 2005 Reporting services. I hace created 32 bit ODBC data dsns using the supplied utitlity (odbcad32.exe). As I am creating the reports I can pull data using the data sources without a problem, however when I deploy my datasource and report to Report manager and run I get the following:
An error has occurred during report processing.
Cannot create a connection to data source 'Chicago_Reporting'.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
This has been driving me crazy for 2 days now. Any help would be very much appreciated.
Thanks
Data Flow Task - Multiple Columns From Different Sources To A Single Table
Dec 19, 2006Hi:
I have a data flow task in which there is a OLEDB source, derived column item, and a oledb destination. My source is a SQL command, that returns some values. I have some values, that I define in the derived columns, and set default values under the expression column. My question is, I also have some destination columns which in my OLEDB destination need another SQL command. How would I do that? Can I attach two or more OLEDB sources to one destination? How would I accomplish that? Thanks
MA2005
Changing The Report Parameters Or Data Sources To The Values You Specified Is Not Allowed
May 24, 2007I have a report I want to modify and when I go to deploy it I get:
Changing the report parameters or data sources to the values you specified is not allowed. The report is configured to run unattended. Using the specified values would prevent the unattended processing of this report
Now I understand the error, it is because on the report server's web management interface I entered credentials for the report to run that point to a custom datasource and supplied the appropriate credentials to run the report.
However, I don't want to have to reconfigure that just to deploy a slightly modified version of the report.
How can I get around this? Or what is the proper work around? I just want to change the background color from dark gray to light gray on a header column....
Thanks!
Extract From Multiple Sources
May 15, 2007Hello Friends,
I need suggestion/help
I have to build a package using SSIS and the requirement is like this,
First I have to check the data source type(3 types for now)
then I have to select a query for each different source from a table (basically the connection string and query changes for each query)
and load the data into the dimension and fact tables.
I am not able to get the full picture of how I am going to achieve this, there is little complication to this one of the 3 sources has 15 servers from where i have to extract data
Hope I am clear enough
Appreciate any suggestions
Thank you
Combining Multiple Sources Into 1 Row
Apr 7, 2008
OK I have 4 differant Data Sources... One being a count of one DB, another count of another DB, another count of another and then another process from a script component. Each source returns 1 row of data with 1 column each except the Script Component. It returns 3 columns... Now I need to take each of the row's returned and combine them to a single row (line) and inset them into another table just as one single eatry. I am using a Union All and when it runs I see the 4 Data Sources say 1 Row... But after it hits the Union All it does 4 rows... What am I doing wrong or am I using the wrong component? Please if anyone can help that would be wonderful.
Multiple Sources && Destinations...
Apr 13, 2006Hello,
I am working on a typical data conversion project where we are migrating data from an old data model to a new data model, using SSIS. Both the DBs are in SQL.
Now we have a situation where say there are 25 source tables and 20 odd target tables.
For transporting data, we are using OLEDB Source & OLEDB Destination transforms. However, each transform maps to one view or one table. As a result, the Data Flow is really messed up with 45+ transforms in it. Is there an elegant way of doing this ? With say just one datasource or maybe fewer transforms?
Thanks,
Satya
Multiple Sources And One Target
Aug 23, 2006I'm thinking aboug using Service Broker to aggregate transactions from 12 remote SQL Servers to a central SQL Server. Service Broker is new to me, so I don't know what the proper setup would be.
We have five different kinds of transactions that can be sent from any of the remote servers. These remote servers truly are remote, connecting to the central server over a WAN. We don't want problems with one server to interfere with any other server. We also don't want problems with one kind of transaction to interfere with the processing of any other kind of transaction.
If were only talking about one remote server, I'm guessing I'd want to create 5 different services - one for each kind of transaction. Does that mean I'd also need to create 5 different routes on both the remote server and the central server?
And how would I scale this out to a dozen remote servers? Can I stick with 5 services on the central server, or would I need to define 5 different services for each remote server, so 60 total? And would that be 60 different routes defined on the central server? Based on my limited knowledge of routes and how they're tied to services, I think that would mean 60 routes, but maybe I'm missing something.
Please let me know. Thanks.
Binding Multiple Sources To Datagridview
May 20, 2008Hello, I have a datagridview setup right now to display data from one table in my database. However, I am going to need it to pull certain columns from certain tables and put them all into one datagrid. The three tables are demographics, financial, and procedures. Can someone give me an example on how I'd go about doing this? I'm using vb.net.
The procedures table is a list of options that will be displayed in a combo box, depending on the option selected, other cells in the datagrid will be filled with certain data, this data is in the procedures table as well. All of this data will be alongside a customer id located in the demographics table, and will all be recorded into the financial table.
Multiple Sources - Single Destination
Jan 17, 2007There are two EXCEL sources one destination table. Each record in the destination should be populated with 2 columns from one source and 2 from other source.
Source 1:
ID Name
1 abc
Source 2:
Address Location
232/2 xyz
Destination:
ID Name Address Location
1 abc 232/2 xyz
I tried using UNION ALL transformation but it fetches 2 seperate records (assuming that one record in each source). How to achieve this without using SCRIPT component?
TIA
How To Optimize Data Import With Huge Volumes And Joins Across Data Sources Not All SQL Server Based?
Jun 7, 2006I need to periodically import a (HUGE) table of data from an external data source (not SQL Server) into SQL Server, with the following scenarios:
Some of the records in the external data source may not exist in SQL.Some of the records in the external data source may have a different value at different imports, but this records are identified univocally by the same primary key in the external datasource and in SQL Server.Some of the records in the external data source may be the same in SQL.
Due to the massive volume of the import, I would like to import only the records which are different from what I have in SQL Server (cases 1 and 2 above). In fact case 2 is the most critical.
I thought of making a query with a left outer join between the data in the external data source table (SOURCE) and the data in the SQL Server table (DESTIN). The join is done on the respective primary keys (composed keys of up to 10 columns) and one of the WHERE conditions will be that the value in SOURCE is different from the value in DESTIN.
The result of this query would be exactly what I need to import.
How to do this in SSIS??? I couldn't figure out how to join tables in different data sources yet.
In fact I cannot write a stored procedure to do that, since one of the sources is in a datasources not SQL Server.
I have seen the Lookup transformation in this article http://www.sqlis.com/default.aspx?311 but this is not exacltly what I want to do.
Another possibility is to use the merge join, but due to the sorting I believe its performances would be terrible!
Thanks in advance for your suggestions!
Best Practice - Data Sources And Data Views Vs OLE DB Source
Feb 26, 2008Hi, i'm wondering which is the best way to search data in a SQL Server.
I reach data using Data Sources and Data Views and also with OLE DB Source with a Data access mode: Named query.
I have to write the data into a Flat File. So, does any one knows which is the best practice for this? Or any one of the two are good choices?
Thanks for your help.
Beli
Importing Data From Various Data Sources With Non Standard Formats
Mar 19, 2007Hi all :)
I'm wondering if SSIS will be the solution to the problem I'm working on.
Some of our customers give us an Excel sheet with data they want to insert or update in the database.
I've created a package that will take an Excel sheet, do some data conversion so the data types match up and after that I use a Slowly Changing Data component to create the insert/update commands.
This works great. If a customer adds a new row to the Excel sheet or updates an existing row changes are nicely reflected in the database.
But now I€™ve got the following problem. The column names and the order of the columns in the Excel sheet are not standard and in the future it could happen a customer doesn't even use an Excel sheet but something totally different.
Can I use SSIS for this? Is it possible to let the user set the mappings trough some sort of user interface? I€™ve looked at programmatically creating the package but I€™ve got to say that€™s quit hard to do€¦ It would be easier to write the whole thing myself than to create the package trough code ;)
If not I thought about transforming the data in code before I pass it on to the SSIS package in something like XML. That way I can use standard column names and data types.
So how should I solve this problem? Use SSIS or not?
Thnx :)
Wouter de Kort
Different Data Sources
Feb 21, 2008Hi i have data on a Server in a different database which i like to access from within my ssis job.
I just need to look up information from one table on this database so i can references it. Is there a way of doing this is SSIS. Rather then me having to load the data from one database to another as the data may change.
i tried having 2 sources of data feed into a look up but that does not work..