Referance Source and Destination in SQL

Question

Referance Source and Destination in SQL

Jonathan Matt

SSCarpal Tunnel

Points: 4027
More actions
July 31, 2002 at 12:51 pm

#104974

Heloo all,
I would like to append to a table on SQL Server 7. The source table is in a Progress database (never heard of it? join the club!)that is accesable through a Progress ODBC driver. In my package I have a connection to the source and destination table with a transformation between them. In the transformation on the source tab, I want to write SQL that will query the destination table for the MAX of a transaction number field and then grab only records from the source table whose transactions are greater than this MAX. I tried the following with fully qualified identifiers:
SELECT all fields
FROM sourcetable
WHERE sourcetable.tran-num >
(SELECT MAX(destinationtable.tran-num)
FROM destinationtable)
No good! Can you check something in the destination table and use it to select from the source table in DTS?

Viewing 9 posts - 1 through 8 (of 8 total)

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

mhweiss SSCarpal Tunnel Points: 4284 More actions · Answer 1

Have you tried using either an activex task object or a dynamic task object along with a global variable to pull the max trans number from the destination table then reference your global variable in an execute sql task object?

hth,

Michael

PS - If you have problems with this I might be able to dig around and find some code that is close to what you need...

Michael Weiss

Jonathan Matt SSCarpal Tunnel Points: 4027 More actions · Answer 2

Thanks for your reply Mike,

I see now that ActiveX is the way to go here. I am a bit confused about the big picture however. What about this:

1. ActiveX script that uses ADO to get max transaction from destination table and puts it into a global variable.

2. Datapump that uses the global variable value as the criteria in a SELECT statement.

What I don't get is how does the result of this select statement get pumped to the destination table?

Thanks and any code you have would be welcomed if its not too much trouble.

Jonathan

mhweiss SSCarpal Tunnel Points: 4284 More actions · Answer 3

Your assumptions (1) & (2) are correct...use an activex task, with ADO query for your max value...then you should either be able to use a global variable in your insert sql statement or you could place your insert statement in your activex task and run it under ado to accomplish the insert...

hth,

Michael

PS - I will look for some code examples I may have that are similar to what you want to accomplish. If I find something, I will post it here...

Michael Weiss

Ranjit Wimalaratne SSC Enthusiast Points: 101 More actions · Answer 4

Hi,

Just to say my two cents worth, wouldn't it be easier to make a linked server to the source from the traget server, as you said that Progress DB has a ODBC driver and then run the fully qualified query you wrote, with out anything else?

Jonathan Matt SSCarpal Tunnel Points: 4027 More actions · Answer 5

Thanks for your reply Ranjit,

I am hesitant to link servers because the Progress database is our production database for our ERP (Enterprise Resource Planning)software. The Progress database has its version of transaction logs (called before image files). I am concerned that linking the servers would cause a flood of ODBC activity in the before image file and crah the database. I don't know enough about the implications of linking.

Thanks again

Jonathan

Ranjit Wimalaratne SSC Enthusiast Points: 101 More actions · Answer 6

Jonathan,

If both servers are MS SQL, the enviroment I am used with, if the source server is linked and copied FROM the source to the target, the

tansaction log gets updated ONLY in the target server as that's where the data gets loaded. I believe it should be the same in your environment. You probably could do a test by copying a small number od records

across?

Ranjit

Jonathan Matt SSCarpal Tunnel Points: 4027 More actions · Answer 7

Thanks again Ranjit

I will take your suggestion and try a test.

Jonathan

Deuce SSCrazy Points: 2712 More actions · Answer 8

Jon,

Establishing a linked server just stores your connection string information in the master..sysservers table as you have in the DTS package. The only ODBC traffic you'll see is from active queries as you would from the DTS package anyway. It really is the way to go.

Good luck,

John