Building An Application On Multiple Data Sources
Jul 6, 2006
ASP.NET on SQL Server
I've been asked to quote for developing a system to expose data on a
web application. Most of the data will come from SQL Server DBs
located on a single box. However, some of the data will be sourced
from ORACLE which is located on a different box. It may be necessary
to create VIEWS and Stored Procedures joining these DBs
Does anyone have any pointers, clues, hints, tips or pitfalls that I
might consider while making my proposal? What sort of extra
contingency should I allow for the connection to ORACLE? Should I do
all the data retrieval on the DB server, or should I do it on the Web
server?
Any thoughts would be appreciated.
Thanks
William Balmer
View 2 Replies
ADVERTISEMENT
Jan 24, 2008
Not 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?
View 2 Replies
View Related
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
View 4 Replies
View Related
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
View 1 Replies
View Related
Dec 20, 2006
I 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
View Related
Aug 4, 2006
Hi,
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
View 5 Replies
View Related
Jul 22, 2004
Is 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.
View 3 Replies
View Related
Sep 19, 2005
I 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 Related
Nov 1, 2007
Hi,
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
View 1 Replies
View Related
May 7, 2008
I 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
View 5 Replies
View Related
Jun 22, 2007
I 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
View 13 Replies
View Related
Jan 9, 2007
Hi,
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?
View 8 Replies
View Related
Sep 24, 2015
I 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?
View 1 Replies
View Related
Nov 28, 2007
I 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
View 3 Replies
View Related
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
View 3 Replies
View Related
Dec 19, 2006
Hi:
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
View 9 Replies
View Related
Sep 5, 2013
I 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?
View 2 Replies
View Related
Jan 20, 2000
During the set-up of my DB's and their tables, I was unable to setup a relationship between a table in one Database and another table in my other Database ( using the diagram ). Maybe my datastructure of multiple databases is not correct, or is their an option to set relations between multiple databases ?
PS in the future I planned to have some other databases on different servers.
View 1 Replies
View Related
May 15, 2007
Hello 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
View 1 Replies
View Related
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.
View 10 Replies
View Related
Apr 13, 2006
Hello,
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
View 1 Replies
View Related
Aug 23, 2006
I'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.
View 3 Replies
View Related
Mar 14, 2008
I am trying to figure out the best way to build a table that will hold information about our inventory of parts. We have several issues that I have been struggeling with trying to make this work. First capturing the basic part information is a no brainer, where I am having some questions is with how to assign categories, subcategories for each of these parts. The challenge comes because each part can belong to multiple categories and subcategories. For example we have 5 main items that would have categories, subcategories, parts. For example:
Item: Trailer
Model: model1, model2, model3
Category: Chassis
Subcategory: Upper frame, lower frame, electrical
Part: could be any thing that falls under one of the subcategories
Item: Trailer
Model: model1, model2, model3
Category: Gen set
Subcategory: engine, power, electrical
Part: could be any thing that falls under one of the subcategories
repeat...
My main items are trailer, shelter, power, heat, lights. Under the trailer and shelter items parts can belong all models or just a couple or just one model. My thought was to create a table for each item, ie: TrailerModels, ShelterModels which would have 100 plus columns, 1 column for each model, using a bit datatype to show which parts could belong to which model. This however seems to be quite a bit of work, because in order to use the models to thin down lists when searching for an item it would have query this table as well as the category and subcategory tables.
Anyone have some feedback about how I should approach this? I don't know if I was able to communicate the specifics of this very well but I can clear up details if anyone wants to throw some feedback my way.
Thanks!
yAy
View 3 Replies
View Related
May 20, 2008
Hello, 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.
View 1 Replies
View Related
Jan 17, 2007
There 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
View 5 Replies
View Related
Mar 7, 2008
Hi there,
I'm faced with a bit of an issue within SSIS and I was wondering if anyone could help. I have data that looks like the following:
TABLE 1:::
CustomerID StaffID Percentage
A123 1234 1
A124 1245 1
A125 1234 0.7
A125 1443 0.3
B117 6653 1
B154 8899 1
C776 1443 0.2
C776 1337 0.6
C776 1245 0.2
I need to be able to summarize the data above and build up a string that should look like the following:
TABLE 2:::
CustomerID StringID
A123 1234||1
A124 1245||1
A125 1234||0.7||1443||0.3
B117 6653||1
B154 8899||1
C776 1443||0.2||1337||0.6||1245||0.2
As you can see in Table 1, if a CustomerID has only one StaffID associated to it, the "Percentage" Column's value is 1. In the cases where there is more than one StaffID associated to a particular CustomerID, the percentage column get's split up among the StaffIDs, however the sum of this split can never exceed 1 (100%) per CustomerID.
I need to know how to find the cases where there is more than one StaffID associated to a CustomerID, group the CustomerIDs, and build a string made up with the StaffID and Percentages as shown in Table 2.
Any help would be greatly appreciated, as I've struggled with this one for a while and have hit a brick wall.
Ciao,
Colin
View 6 Replies
View Related
Jun 7, 2006
I 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!
View 9 Replies
View Related
Feb 26, 2008
Hi, 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
View 6 Replies
View Related
Mar 19, 2007
Hi 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
View 6 Replies
View Related
Feb 21, 2008
Hi 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..
View 1 Replies
View Related
Apr 1, 2008
Hi there,
On my home page I have several different folders to reports which require different data sources. the problem is that within these folders there are multiple copies of the same datasource. is it possible to store all of the datasources in one folder, one location? it would certainly be easier when changes to usernames and passwords need to be modified!
Thanks,
Rhonda
View 4 Replies
View Related
Apr 4, 2008
I searched and read about Data Sources and I'm seeing that there is no advantage in using it, which is what I found from playing around with it.
I expected that you would set a global connection in Data Sources and somehow link this to the things in your Connection Manager, giving you one place to switch from one environment to another. But reading the discussions here and playing around with it, this is not the case.
So, why is it there?
Next question.... another thing I gathered so far is something called "Configurations" that will do what I was describing above. Where do I do this?
View 8 Replies
View Related
Jun 28, 2007
Hi,
how can i use var for Data Sources ,
my project have to be suitable for the QA system and the production system and i want to connect them by var or something else.
if you have another idea about this subject i'll happy to read.
thanks,
gil.
View 8 Replies
View Related
