multiple datasources/servers?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » multiple datasources/servers?

multiple datasources/servers?

Rate Topic

Display Mode

Topic Options

Author

Message

SQL_path

Posted Tuesday, August 14, 2012 6:25 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, December 22, 2012 5:39 PM
Points: 158, Visits: 325

Hi,

What should be sql server integration package contents?

For instance Query is ------------------

 select  * from orders

when connected to server A it bring data for A only,
connected to B and then data for B only and so on.............

If I need to see data from A, B, C, D , E server in one single report. how do i go about it?

thank you !!

Post #1344649

anthony.green

Posted Tuesday, August 14, 2012 6:53 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

X amount of data flow tasks selecting what is needed from the same table in each server, then import that into 1 big central table.

They in the report query the big central table.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1344675

SQL_path

Posted Tuesday, August 14, 2012 9:15 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, December 22, 2012 5:39 PM
Points: 158, Visits: 325

thank you. I am very new to this so please excuse if any of the questions looks basic.

the query has no parameters.
something like this

 create table #product 
(

prod_id int 
,prod_detail varchar(40) )

INSERT INTO #product  (prod_id ,prod_detail )
 
SELECT  1, 'bicycle'
UNION ALL 
SELECT 2, 'car'
UNION ALL 
SELECT  3, 'truck'
UNION ALL 
SELECT  4, 'van' 

select * from #product
drop table #product

I should add one execute sql task then a dataflow task for each server right ?
>I added one execute sql task on control flow tab
>selected ADO.net connection, result set as full result set,

i get error like failed to lock variable. I think I am not setting up the variable right.

if there is a good link for beginner, please post that as well.

thanks

Post #1344797

anthony.green

Posted Wednesday, August 15, 2012 2:36 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

You dont need an execute SQL task, just a data flow task.

Create connection managers for ServerA,B,C,D,E and then a connection manager for the server where the big table is going to be stored (e.g. ServerF)

Depending how you want to do it, drag between 1 and 5 data flow tasks (DFT) onto the control flow, then inside the data flow tasks created the source and destination connections, and use a SQL query on the source connection to select what you want, then it will insert it into ServerF.

Each DFT can have multiple sources and connections, so you could have 1 DFT with 5 sources, all mapping 5 destinations, or you could use the merge task to merge all 5 result sets into 1 then import it into 1 destination.

Or you could have 5 DFTs each one relates to each source server.

Up to you how you want to do it.

I would recommend running through the SSIS stairways www.sqlservercentral.com/stairway/

Post #1345166

SQL_path

Posted Friday, August 17, 2012 11:54 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, December 22, 2012 5:39 PM
Points: 158, Visits: 325

Thank you so much Anthony !!. I am going to try that. appreciate your time and help.

Post #1346692

« Prev Topic | Next Topic »

Permissions