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.
View 10 Replies
ADVERTISEMENT
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
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
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
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
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
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
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
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
Jul 6, 2006
ASP.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 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
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
Mar 6, 2006
Hey guys, here is my issue, i'm trying to combine multiple columns of data into one row. For example, I have a temp table:
create table #Custom_Address
( patient_idchar(10)null,
episode_idchar(3)null,
custom_address varchar(100) null
)
As you can tell, the 'custom_address' column is going to house the results of the combination.
I am trying to INSERT (combine) using this statement:
INSERT #Custom_Address
select cm.patient_id, cm.episode_id, (cc.coverage_plan_add1 +' ' + coverage_plan_add2 + ' ' + coverage_plan_city+ ' ' + coverage_plan_st+ ' ' + coverage_plan_zip) as custom_address
FROM #ClaimMaster cm join Coverage_Custom cc on cm.patient_id = cc.patient_id and cm.episode_id = cc.episode_id
WHEREcc.coverage_plan_add1 > 0
OR cc.coverage_plan_add2 > 0
OR cc.coverage_plan_city > 0
OR cc.coverage_plan_st > 0
OR cc.coverage_plan_zip > 0
GROUP BY cm.patient_id, cm.episode_id
I want to insert the patient_id, episode_id, and then combine the 'cc.coverage_plan_add1 +' ' + coverage_plan_add2 + ' ' + coverage_plan_city+ ' ' + coverage_plan_st+ ' ' + coverage_plan_zip' data to represent my "custom_address" column and only when there is data in those columns.
Then I do my update:
UPDATE#ClaimMaster
SET #ClaimMaster.custom_address = ca.custom_address
FROM #ClaimMaster
JOIN#Custom_Address ca on #ClaimMaster.patient_id = ca.patient_id and #ClaimMaster.episode_id = ca.episode_id
However, when I do this, it says my 'cc.coverage_plan_add1 + etc' columns are invalid because they are not contained in either an aggregate function or a GROUP By clause.
If i'm combining all the data to represent a single column, how do I format my group by clause? Or is my entire INSERT statement wrong?
As a side note, my #ClaimMaster table is another temp table, but the problem is not in there.
View 5 Replies
View Related
Feb 13, 2007
I have data that looks like this:
ID Value
1 Descr1
1 Descr2
1 Descr3
where Descr could range from 1 to 100 for each ID
The result set I need is:
Descr1,Descr2,Desc3...etc.
Does someone have a query to do this?
Thank you
View 8 Replies
View Related
Dec 19, 2014
I have data that looks like this:
Cus_no Inv_No Trans_type Amt_Inv Amt_Paid
100 12345 Other 0 -21.76
100 12345 Discount 0 -6.39
100 12345 Discount 6.39 6.39
100 12345 Discount 21.76 21.76
100 12345 Sales Inv 218.99 218.99
What I would like to do is a select statement that returns:
Cus_no Inv_no Amt_Inv Amt_Paid Disc_amt Other
100 12345 247.14 218.99 -6.39 -21.76
I've tried something like this but since I'm referencing trans_type I get a message that trans_type must be in Group by. doing that give me multiple records.
select cus_no, Inv_no, Sum(Amount_Invoiced_DC) AS InvAmt,
case trans_type when 'Sales Inv' then sum(Amount_Paid_DC) else 0 end as AmtPaid,
case when trans_type = 'Discount' and sum(Amount_Paid_DC)<0 then sum(Amount_Paid_DC) else 0 end as DiscountAmt
FROM BI50_BankTransactions_AR_InvcDt_H
where cus_no is not null
group by cus_no, Inv_no
View 2 Replies
View Related
May 4, 2007
Hii all.
quite urgent... I have 3 matix tables.. all the same row headings. I need to be able to make these visible/invisible depending on the user parameters. The reason for this, if a user hides table on the left.. the middle table needs to show the row headings. and last table must not.
The problem:
When making the row headings invisible it doesnt exactly chop off the textboxes... so the tables look disjoint.. and a big gap appears between both tables...
Is there a way to join these tables without leavings gaps ?
any help is appreciated.
Neil
View 7 Replies
View Related
May 16, 2008
Hi,
I'm trying to do this in T-SQL. I have a query that returns, for this matter, only 2 rows. The table output looks like this:
AdjType
AdjNbrStart
AdjNbrEnd
AdjTotalAmt
1
9
9
-134180
2
8
10
104981.42
How do you do it to bring back only one row that will look like so:
1
9
9
-134180
2
8
10
104981.4
Is it possible? Like I said, I only have 2 rows. I'm not concatenating these columns, just want to bring it back as one row. Any ideas? Thanks.
View 1 Replies
View Related
Jul 27, 2007
I was wondering if it's possible to have a stored procedure that has two select statements which you can combine as a single result set. For instance:select name, age, titlefrom tableaselect name, age, titlefrom tablebCould you combine these queries into a single result set?
View 2 Replies
View Related
Nov 21, 2007
Hi,
I am trying to combine information from two or more records into one and I am completely stuck on a solution for my problem so I hope there is someone out there who can help me.
My table looks like this:
ID - DayNr - Transportation - TransOrder - Route
25 - 1 - Car - 1 - Text A
25 - 1 - Train - 1 - Text B
25 - 1 - Train - 2 - Text C
25 - 7 - Train - 1 - Text D
25 - 7 - Train - 2 - Text E
I want to combine all Route - information belonging to the same combination of ID & DayNr & Transportation into one new record. The result should look like:
Column 1 - Column 2
25/1/Car - Text A
25/1/Train - TextB;TextC
25/7/Train - TextD;TextE
I have tried Coalesce-statements and Cursor-solutions but until now everything I tried didn't work. Ideas anyone?
Thanks.
RMG
P.S. ID is not my primary key and doesn't have to be unique
View 14 Replies
View Related
Oct 17, 2013
I have 3 queries pulling from the same table, trying to define a count on each criteria. I have the data pulling, but the data is in multiple rows. I want the data in one row with all the counts in each separate columns. Also I need to setup a flag if a client purchased and order within 30 days from their last purchase.
I am doing this select for each credit card, check and cash purchases. I do not know how to setup a flag where the client may have ordered and paid by check or cash after 30 days from a credit card purchase. Is this something that can be done?
select
clientnumber,count(distinct clientnumber) as cccnt,
0 as ckcnt, 0 as cacnt
from dbo.purchases
where orderdate >= 20120101 and orderdate <= 20121231 and
payment_type = 'CC'
group by clientnumber;
OUTPUT currently looks like this:
1234 2 0 0
1234 0 1 0
1234 0 0 4
Is it possible to result in this, along with a flag with the criteria above?:
1234 2 1 4 Y
View 3 Replies
View Related
Dec 20, 2007
Hi All,
I'm trying to develop a query that joins one record from a table with multiple matching records from another table all in one record,
Table1 has the primary key
id
--
1
2
3
4
Table2 has the follwing records
id year subject
-----------------
1 2000 English
1 2002 French
2 2004 English
2 2005 English
2 2006 English
3 2007 French
I want the result to be like this
id 2000 2001 2002 2003 2004 2005 2006 2007
-----------------------------------------------------------
1 English null French null null null null null
2 null null null null English English English null
3 null null null null null null null English
Appretiate your assistance
View 4 Replies
View Related
Jul 30, 2007
I have a table employee: that contains one column and three rows. How can I transform it using SELECT to display only one row and one column, with comma delimited strings: John, Mike, Dale?
Employee Name
John
Mike
Dale
View 5 Replies
View Related
Nov 21, 2007
Hi,
I am trying to combine information from two or more records into one and I am completely stuck on a solution for my problem so I hope there is someone out there who can help me.
My table looks like this:
ID - DayNr - Transportation - TransOrder - Route
25 - 1 - Car - 1 - Text A
25 - 1 - Train - 1 - Text B
25 - 1 - Train - 2 - Text C
25 - 7 - Train - 1 - Text D
25 - 7 - Train - 2 - Text E
I want to combine all Route - information belonging to the same combination of ID & DayNr & Transportation into one new record. The result should look like:
Column 1 - Column 2
25/1/Car - Text A
25/1/Train - TextB;TextC
25/7/Train - TextD;TextE
I have tried Coalesce-statements and Cursor-solutions but until now everything I tried didn't work. The big issue here is that I have to base my concatenation on 3 columns. Ideas anyone?
Thanks.
RMG
P.S. ID is not my primary key and doesn't have to be unique
View 1 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
Mar 21, 2006
Hello again,
Another combining multiple rows teaser, during a few routines I made a mistake and I would like to combine my efforts. Here is my data:
Code:
Table A
ID DSN VN AX Diag
1111296.54
3212318.00
Both DSNs share the same Patient_id in a seperate table which holds the DSN numbers and their corresponding patients.
Code:
Table B
DSN Patient_id
100000001
200000001
So what I need to do is maintain their unique 'ID' number in Table A but update their DSN numbers to reflect the first instance in Table B. So my data would look like this in both tables.
Code:
Table A
ID DSN VN AX Diag
1111296.54
3112318.00
Note: The second rows DSN changed to 1 from 2
Code:
Table B
DSN Patient_id
100000001
(Duplicate row removed with same patient_id)
The result would look like the above but as you noticed I need to remove the duplicate row that had the different DSN in Table B so that only one DSN remains that can map to multiple rows (IDs) in Table A.
Table A:
DSN can map to multiple rows (IDs)
IDs must be unique (aka kept to what they are currently)
Table B:
Second row with same DSN must be removed.
Any takes, ideas? I need to do this on a couple thousand rows....
Thanks, and im happy to clarify if needed.
View 1 Replies
View Related
