multiple datasources/servers?

Question

multiple datasources/servers?

SQL_path

SSCrazy

Points: 2810
More actions
August 14, 2012 at 6:25 am

#266000

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 !!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113509 More actions · Answer 1

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.

SQL_path SSCrazy Points: 2810 More actions · Answer 2

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

Ant-Green SSC Guru Points: 113509 More actions · Answer 3

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/

SQL_path SSCrazy Points: 2810 More actions · Answer 4

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