Beginner to DTS question

[Katy44]

Sorry if this is an easy question or I should know, but I don't really know where to start.
I'm trying to create a package that will be run regularly to transfer data from an Oracle database to SQL Server. In both databases, the records have an ID property. What I want to do is:

Find the Max ID in the SQL Server database
Use this to write a query to get everything from the Oracle database where the ID is bigger than that
Insert it into the SQL Server table.

I've used DTS for simple data dumps - all from an Oracle table to SQL Server, so I know how to set up connections and data transfers. I also won't have a problem writing the SQL.
My problem is: what mechanism do I use to pull this max ID from the SQL Server db and then use it in the query running on the Oracle db? So far I have looked at lookups, global variables and a couple of other things. I just need to know what I should be looking at - I can do the research and figure it all out from there. This must be quite a standard thing to do, and I'm guessing there is a 'right' way to do it?
Thanks for any help

 

[dbomrrsm]

Do:

Select * from MyOracleTable
WHERE MyOracleTable.ID > 
(select max(id) from MySQLServerTable)

[blue]DBomrrsm[/blue]

 

[Katy44]

The tables both have the same name.
I tried <servername>.dbo.<tablename> but it didn't seem to like it - but if this should work then I'll give it another go.
How does that work with setting up the connections though?

 

[dbomrrsm]

if you do a DTS with an oracle connection and a SQL Server connection then do a transform data task between the two then specify the query as above - with obvious tweaks.

[blue]DBomrrsm[/blue]

 

[Katy44]

OK, I'll try it again - thanks!

 

[Katy44]

As soon as I try to change tabs in my DTS package, I get "An error was returned without an error code

 

[Katy44]

I really can't get this to work (I've recreated the entire thing) - any ideas?

 

[dbomrrsm]

when I have had the same non helpful error message it has been to do with the connections inside the DTS.

Select each of the connections and make sure they are releted to what you would expect them to be i.e. the SQL Server connection in the connection propeties window in the DTS (double click the connection) should show a SQL Server connection in the new/existing connection.

I have had the same problem in the past where a SQL Server connection was tring to connect as an Oracle connection in the existing connection drop down list.

Good luck.

[blue]DBomrrsm[/blue]

 

[Katy44]

Thanks, I've checked those, and they seem ok.
The problem I'm having in understanding this is this:
I have two connections on my page the source (Oracle) and the destination (SQL Server), and a data transformation task between the two.
In my DTS package I put a query in the Source tab as shown above. However, this queries bot the source AND the destination - that can't be right surely? Do I need to provide two source connections, and a single destination? Do I have the entire thing set up incorrectly?

 

[dbomrrsm]

it may pay to import the full table from Oracle then do a:

Insert into MyNewSQLTable (cols....)
Select cols..... from MyNewSQLTable
WHERE MYNewSQLTable.ID > 
(select max(id) from MySQLServerTable)

you can then truncate MYNewSQLTable ready for next load.

[blue]DBomrrsm[/blue]

 

[Katy44]

Thanks but that's no good - hte SQL Server table is one that has been imported from a live Oracle database, and this package is intended as a refresh!
Did what I asked in my last post make sense? If it does, and it should work like that then that's fine, but I just have a problem understanding that.

 

[Katy44]

I've not quite got this working exactly as I want due to the fact that it's not going to work quite how I think it will, just importing any ids greater than those already in there. However, I think I've got it in principle.
I have an Execute SQL task which gets the max ID. One of the output parameters on this sis a global variable (MaxID). On success of this Execute SQL task, the actual workflow begins, using a parameter in the query (just marked by a '?',and I have mapped the parameter to the global variable.
Whew.
This seems to work but I haven't tested it properly yet.
If this is a stupid way to do it, or I'm missing something obvious, please let me know!
Thanks for all your help dbomrrsm.

 

[dbomrrsm]

no that sounds like a very sensible way of doing it - although you havent yet tested it I cant see - in theory - why it wont do exactly what you are looking for.

good luck and glad to be of help.

[blue]DBomrrsm[/blue]