July 10, 2012 at 10:05 am
Hi,
Does anyone know if its possible to use 2 different servers within a same connection in an SSIS package ?
For eg. if using 'Execute SQL Task Editor' component can 2 different servers be specified in the 'Connection' part ?
If its possible could you please also mention how this can be achieved ?
Thanks,
Paul
July 10, 2012 at 1:35 pm
Not at the same time. A connection is to one server. If you want to query multiple servers in a single query you'd need to setup linked servers and then query using 4 part naming.
Can you explain what you need to accomplish? Then perhaps someone can offer a solution in SSIS.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 10, 2012 at 1:41 pm
You can have 1 connection manager and have a variable to overwrite the server expression for that connection manager. But only 1 at a time.
A better description of what you need would be very useful.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 17, 2012 at 9:59 am
SQLRNNR (7/10/2012)
You can have 1 connection manager and have a variable to overwrite the server expression for that connection manager. But only 1 at a time.A better description of what you need would be very useful.
Thanks for your reply.
I would show you with an example of what is required. The following query uses database 'A' under the 'Connection' properties on server 1 but also needs to access databases 'B' and 'C' on server 2.
-----------------------------------------------------------------------
update
d
set
d.wrong_sc_num=d.sc_num,
d.sc_num=null
from
mgs_cra_data d
where
(d.sc_num not in (select SC_NUM from B..MF_SC_NUMBER)
and d.sc_num not in (select CR_NUM from C..tbl_CCPARTY) )
------------------------------------------------------------------------
Is there a way to achieve this via shared servers or any other method ?
Thanks,
Paul
July 17, 2012 at 10:08 am
pwalter83 (7/17/2012)
SQLRNNR (7/10/2012)
You can have 1 connection manager and have a variable to overwrite the server expression for that connection manager. But only 1 at a time.A better description of what you need would be very useful.
Thanks for your reply.
I would show you with an example of what is required. The following query uses database 'A' under the 'Connection' properties on server 1 but also needs to access databases 'B' and 'C' on server 2.
-----------------------------------------------------------------------
update
d
set
d.wrong_sc_num=d.sc_num,
d.sc_num=null
from
mgs_cra_data d
where
(d.sc_num not in (select SC_NUM from B..MF_SC_NUMBER)
and d.sc_num not in (select CR_NUM from C..tbl_CCPARTY) )
------------------------------------------------------------------------
Is there a way to achieve this via shared servers or any other method ?
Thanks,
Paul
You would need to use linked servers to servers B & C on server A in order to accomplish this using it this way.
You could do this in SSIS in several steps:
In a data flow:
1. Get the data to be updated
2. Lookup to server B where rows meeting the criteria passed on
3. Lookup to server C where rows meeting the criteria are passed on
4. Insert into a staging table on server A
Then an Execute SQL Task that does an update using the staging table on server A with
Update mgs_cra_data
SET wrong_sc_num = sc_num,
sc_num = NULL
WHERE
EXISTS (SELECT 1 from database.schema.staging_table ST WHERE ST.sc_num = mgs_cra_data.sc_num)
I'm actually not sure which one would actualkly perform better. I tend to not use Linked Servers if I can avoid them. I think the ability to handle connection errors with SSIS make the SSIS path better than the linked servers.
There are other options depending on the business needs that could work by copying the tables on Servers B and C to server A:
1. REplication
2. A scheduled job that copies that data as needed.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 17, 2012 at 11:05 am
pwalter83 (7/17/2012)
SQLRNNR (7/10/2012)
You can have 1 connection manager and have a variable to overwrite the server expression for that connection manager. But only 1 at a time.A better description of what you need would be very useful.
Thanks for your reply.
I would show you with an example of what is required. The following query uses database 'A' under the 'Connection' properties on server 1 but also needs to access databases 'B' and 'C' on server 2.
-----------------------------------------------------------------------
update
d
set
d.wrong_sc_num=d.sc_num,
d.sc_num=null
from
mgs_cra_data d
where
(d.sc_num not in (select SC_NUM from B..MF_SC_NUMBER)
and d.sc_num not in (select CR_NUM from C..tbl_CCPARTY) )
------------------------------------------------------------------------
Is there a way to achieve this via shared servers or any other method ?
Thanks,
Paul
You can use three connections and within ssis use the join task.
You can also import the data from each of those remote servers into the local instance via ssis and then use a single query against the one database.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 18, 2012 at 3:02 am
SQLRNNR (7/17/2012)
pwalter83 (7/17/2012)
SQLRNNR (7/10/2012)
You can have 1 connection manager and have a variable to overwrite the server expression for that connection manager. But only 1 at a time.A better description of what you need would be very useful.
Thanks for your reply.
I would show you with an example of what is required. The following query uses database 'A' under the 'Connection' properties on server 1 but also needs to access databases 'B' and 'C' on server 2.
-----------------------------------------------------------------------
update
d
set
d.wrong_sc_num=d.sc_num,
d.sc_num=null
from
mgs_cra_data d
where
(d.sc_num not in (select SC_NUM from B..MF_SC_NUMBER)
and d.sc_num not in (select CR_NUM from C..tbl_CCPARTY) )
------------------------------------------------------------------------
Is there a way to achieve this via shared servers or any other method ?
Thanks,
Paul
You can use three connections and within ssis use the join task.
You can also import the data from each of those remote servers into the local instance via ssis and then use a single query against the one database.
Thanks for your reply, Jason. Could you please give me an example on how to use three connections and then join them ?
July 18, 2012 at 1:37 pm
Here is an article with details for that.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply