Multiple server connections in DTS

[SteelCityFan]

I'm new to SQL Server and DTS (I was an Oracle guy). My problem should be simple for someone who has done this for a little while.......and any help would be greatly appreciated.

This is my problem:

I am writing a DTS package that pulls data from a production database into a local database. The databases have the same name but they are on different servers. How can I reference both connections in one SQL statement. I am using the "Transform Data" task. I need to transfer some data from the "Source" connection to the "Destination" connection, but I need to look for some values in the "Destination" connection before I do this.

Basically, I don't know how to do it or if I'm even on the right path.

I would realy appreciate any help - thank you!

 

[DrSql]

Here are the steps.

User Microsoft OLEDB Sql Server, and fill in the ServerName, DBANEM, userid, password create 2 Objects.
named Prod & Dev. Then you create a Trasnformation Task, select Prod as the Source and Desination as Dev.
When you right on the Transformation Task properties will show the DB and tables, or you can enter query below by clicking SQL Query.
Then populate Source and destaination tabs.

Execute the package and verfiy the rsults.

Dr.Sql
Good Luck.

 

[SteelCityFan]

Dr. SQL,

It's not that easy.

I guess I should explain. The DTS package pulls data down table by table. I have a table that has 400,000 records in production, so I can't bring it all down. I selectively pull certain records down into the local database. The next table after that must not violate constraints from the previous table. I need to query the local table AND the production table to populate the local database.

This is the query I am using in the Transformation task:

SELECT
*
FROM
IMSPurchaseOrderLineItemDetail
WHERE
IMSPurchaseOrderLineItemID IN
(SELECT
IMSPurchaseOrderLineItemID
FROM
IMSPurchaseOrderLineItem)

The entire query attempts to get data from the Production machine which is not what I want. I want the subquery to point to the local machines database so I can filter out the records and not violate constraints.

 

[DrSql]

Can you explain with little more details with an example, may be I can suggest a way.

Dr.Sql
Good Luck.

 

[mojoT]

Perhaps you need to do this:

SELECT *
FROM [ProdServerName].[DBName]..[TableName]
WHERE ID IN
(
SELECT ID
FROM [DevServerName].[DBName]..[TableName]
)

Try it in Query Analyzer to make sure you're getting the right results (substitute the correct values for Server, Database and table names). You may need to run sp_addlinkedserver first though (see BooksOnline for this).

I hope this is what you're after. I often use this to validate FK constraints, however it is within a stored procedure that is run in DTS through an Execute SQL task. I haven't done it through a Transform Data task, but would expect that if you're specifying the transform using a query, and your servers are linked, then it should work.