Multiple Sources - Single Destination

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

Data Flow Multiple Sources To Populate A Destination

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

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

View 1 Replies View Related

Using Multiple Data Sources In Single Report.

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

Single Report Multiple Data Sources

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

Insert Data From Multiple Sources To A Single Table

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

Data Flow Task - Multiple Columns From Different Sources To A Single Table

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

Combining Multiple Tables Into A Single Flat File Destination

Aug 21, 2007

Hi,

I want to combine a series of outputs from tsql queries into a single flat file destination using SSIS.

Does anyone have any inkling into how I would do this.

I know that I can configure a flat file connection manager to accept the output from the first oledb source, but am having difficulty with subsequent queries.

e.g. output

personID, personForename, personSurname,
1, pf, langan

***Roles
roleID, roleName
1, developer
2, architect
3, business analyst
4, project manager
5, general manager
6, ceo

***joinPersonRoles
personID,roleID
1,1
1,2
1,3
1,4
1,5
1,6

View 9 Replies View Related

Selecting A Single Data Source From Multiple Data Sources

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

Single Report With The Data From Multiple Data Sources

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

SQL Server 2012 :: Insert Values In A Single Table With Four Columns From 4 Different Sources?

Jan 30, 2014

I am trying to insert values in a single table with four columns from 4 different sources. is it possible to run these 4 insertions in parallel. all these insertion are independent of each other

View 3 Replies View Related

Copying Table Data From SQL Server 2005 To SQL Server 2000 - Very Slow When Using OLEDB Source And Destination Sources?

May 8, 2006

An SSIS package to transfer data from a DB instance on SQL Server 2005 to SQL Server 2000 is extremely slow. The package uses an OLEDB Source to OLEDB Destination for data transfer which is basically one table from sql server 2005 to sql server 2000. The job takes 5 minutes to transfer about 400 rows at night when there is very little activity on the server. During the day the job almost always times out.

On SQL Server 200 instances the job ran in minutes in the old 2000 package.

Is there an alternative to this. Tranfer Objects task does not work as there is apparently a defect according to Microsoft. Please let me know if there is any other option other than using a Execute 2000 package task or using an ActiveX Script to read records from one source and to insert them into the destination source, which I am not certain how long it might take and how viable will that be?

Any inputs will be much appreciated.

Thanks,

MShah

View 5 Replies View Related

Extract From Multiple Sources

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

Using Multiple Data Sources

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

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 View Related

Multiple Sources && Destinations...

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

Multiple Sources And One Target

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

Binding Multiple Sources To Datagridview

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

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

View 4 Replies View Related

How Do You Add Multiple Data Sources To A Report?

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

Stored Procedure For Multiple Data Sources

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

Building An Application On Multiple Data Sources

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

Reporting Services And Multiple Data Sources

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

Report Parameters And Multiple Data Sources

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

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

View 3 Replies View Related

Reporting Services :: Report Builder 3 Using Data Sources From Multiple Servers?

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

How To Merge Multiple Rows One Column Data Into A Single Row With Multiple Columns

Mar 3, 2008

Please can anyone help me for the following?

I want to merge multiple rows (eg. 3rows) into a single row with multip columns.

for eg:
data

Date Shift Reading
01-MAR-08 1 879.880
01-MAR-08 2 854.858
01-MAR-08 3 833.836
02-MAR-08 1 809.810
02-MAR-08 2 785.784
02-MAR-08 3 761.760

i want output for the above as:

Date Shift1 Shift2 Shift3
01-MAR-08 879.880 854.858 833.836
02-MAR-08 809.810 785.784 761.760
Please help me.

View 8 Replies View Related

Transact SQL :: Query To Convert Single Row Multiple Columns To Multiple Rows

Apr 21, 2015

View 6 Replies View Related

Multiple Columns With Different Values OR Single Column With Multiple Criteria?

Aug 22, 2007

Hi,

I have multiple columns in a Single Table and i want to search values in different columns. My table structure is

col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))

I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as

SELECT

TBL.col2,TBL.col3
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col2,'engine') TBL1
ON

TBL.col1=TBL1.[key]
INNER JOIN

CONTAINSTABLE(TBL,col3,'toyota') TBL2
ON

TBL.col1=TBL2.[key]

Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.

I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT

TBL.col4
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
ON

TBL.col1=TBL1.[key]
Result = 1 row

But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA

SELECT

TBL.col4
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
ON

TBL.col1=TBL1.[key]

Result=0 Row
Any idea how i can write second query to get result?

View 1 Replies View Related

Transact SQL :: Converting From Multiple Rows With Single Values To Single Rows With Multiple Values

May 10, 2015

Here is some data that will explain what I want to do:

Input Data:
Part ColorCode
A100 123
A100 456
A100 789
B100 456
C100 123
C100 456

Output Data:
Part ColorCode
A100 123;456;789
B100 456
C100 123;456

View 4 Replies View Related

Insert Single Row / Multiple Rows Into Multiple Tables

Sep 3, 2014

How to insert single row/multiple rows into multiple tables by using single insert statement.

View 1 Replies View Related

Package For Multiple Ole Db Destination

Mar 21, 2008

i want to make a package to export data to multiple PLE DB detination from one one flat source.

can any body tell how to create ?

View 5 Replies View Related

Excel Destination Multiple Worksheets!

Apr 30, 2007

Hi ,

I am retrieving some data that contains three or four hundred thousand rows. These rows are supposed to go into an excel file with multiple worksheets, since one Excel worksheet cannot handle rows that are more than 65536. Below is what I need to achieve:

Dynamically create mutiple worksheets.
Re-direct data like this i.e. first 64K in first worksheet and next 64K in next worksheet and so on.
Dynamically name the work sheet with the start value in that work sheet e.g. OrderNumber or OrderDate.

Can we achieve this directly/indirectly?

Your help will be appreciated.

Thanks,

Paraclete

View 10 Replies View Related