Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

multiple datasources/servers? Expand / Collapse
Author
Message
Posted Tuesday, August 14, 2012 6:25 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 14, 2014 3:19 PM
Points: 158, Visits: 334
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
Posted Tuesday, August 14, 2012 6:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
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
Posted Tuesday, August 14, 2012 9:15 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 14, 2014 3:19 PM
Points: 158, Visits: 334

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
Posted Wednesday, August 15, 2012 2:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
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/




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 #1345166
Posted Friday, August 17, 2012 11:54 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 14, 2014 3:19 PM
Points: 158, Visits: 334
Thank you so much Anthony !!. I am going to try that. appreciate your time and help.
Post #1346692
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse