Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cornall
Posting Yak Master
148 Posts |
 Posted - 2008-06-09 : 11:46:43
|
| Hi guys I am using DTS for the first time and am strugling a little.I have created two connections to server a and server b.I am copying a table from server a to server b.On server b a bunch of e-mails are then sent and the last record number processed is stored. The servers aren't linked servers.I want to say SELECT * FROM [serverA].[db].[dbo].[SourceTable] where ID > (SELECT TOP 1 ID FROM [serverB].[db].[dbo].[lastSentTable])This doesn't work as they aren't linked servers. How do I read the value of SELECT TOP 1 ID FROM [serverB].[db].[dbo].[lastSentTable] so I can use it as part of my transform data task?Hope this makes sense!Cheers D |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-09 : 22:08:08
|
| Use opendatasource if you don't want to create linked server. |
 |
|
|
cornall
Posting Yak Master
148 Posts |
Posted - 2008-06-10 : 10:05:53
|
| There is no way I can use a global temp table or variable of some sort? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-10 : 11:00:01
|
quote:
Originally posted by cornall
There is no way I can use a global temp table or variable of some sort?
You can define a globalvariable for the package and store the result of this query to variable by using Execute SQL task and giving variable as Output variable.SELECT TOP 1 ID FROM [serverB].[db].[dbo].[lastSentTable] Then use this variable in the second querySELECT * FROM [serverA].[db].[dbo].[SourceTable] where ID >?and map the parameter value to variable created above |
|
|
|
cornall
Posting Yak Master
148 Posts |
Posted - 2008-06-10 : 11:41:36
|
| Cheers visakh was just coming on to post that as my solution! works perfectly. There really isnt much out there on assigning global variables using SQL it kept giving me active x and vb solutions! But I found your solution eventualy!I find te visual aspects of DTS a bit frustrating but sure I will get used to it!D |
|
|
|
|
|
|
DTS question. New to DTS