February 13, 2006 at 2:47 pm
Hi, I a have a problem i need to insert new records or update the records that had changes from one server to my server(localhost) how can i do that ??? i saw an option making updates or inserts in the same server but how can i do that between 2 different servers???
February 13, 2006 at 2:52 pm
setup link server and use fullyqualified object name
Amit Lohia
February 14, 2006 at 7:20 am
If you are using DTS then you use a data driven query task. The object looks like a Database with a magnifying glass over it in the Task tool bar.
Ex. (Oracle to SQL)
On the source tab I pick a table
PROPERTY_VIEW (from Oracle source)
Defined as
PROPERTY_ID,
CONTACT_ID,
POSITION_ID
On the Bindings tab (SQL Server) I set the following table
Property_Assignments
Defined AS
CREATE TABLE Property_Assignments (
[Property_ID] int not null,
[Employee_ID] varchar (10) not null,
[Title_ID] int not null )
On the Transformation tab I delete all the existing mappings, select all columns on both sides and choose new (AxtiveX Script)
Here is the code for the work
' Copy each source column to the destination column
Function Main()
DTSDestination("Property_ID") = DTSSource("PROPERTY_ID")
DTSDestination("Title_ID") = DTSSource("POSITION_ID")
DTSDestination("Employee_ID") = DTSSource("CONTACT_ID")
if Len(Trim(DTSSource("POSITION_ID"))) = 0 OR IsNull(DTSSource("POSITION_ID")) Then ' If POSITION_ID is 0 (invlaid) or null then I need to remove the existing record.
Main = DTSTransformStat_DeleteQuery
ElseIf DTSLookups("TitleFilled").Execute(DTSSource("PROPERTY_ID"), DTSSource("POSITION_ID")) = 1 Then ' If POSITION_ID is filled at PROPERTY_ID I may need to UPDATE the record.
Main = DTSTransformStat_UpdateQuery
Else ' Otherwise I am just inserting new data.
Main = DTSTransformstat_InsertQuery
End If
End Function
Now on the queries tab I define the following for each query type.
Type = INSERT
Query
INSERT
INTO Property_Assignments
(Property_ID,
Title_ID, Employee_ID)
VALUES (?, ?, ?)
Parameter Mapping (? represents the parameter positions in order)
Parameter 1 = Property_ID
Parameter 2 = Title_ID
Parameter 3 = Employee_ID
Type = UPDATE
Query
UPDATE Property_Assignments
SET Employee_ID
= ?
WHERE (Property_ID = ?) AND (Title_ID = ?) AND (Employee_ID
<> ?)
Parameter Mapping (? represents the parameter positions in order)
Parameter 1 = Employee_ID
Parameter 2 = Property_ID
Parameter 3 = Title_ID
Parameter 4 = Employee_ID
Note: Parameter for sets UPDATE so only occurrs if the value doesn't match, so number of imported records do not neccessairly matvch work performed, possibly could have setup a skip if value matched thru use of lookup query.
Type = DELETE
Query
DELETE
FROM Property_Assignments
WHERE (Property_ID = ?) AND (Title_ID
= ?)
Parameter Mapping (? represents the parameter positions in order)
Parameter 1 = Property_ID
Parameter 2 = Title_ID
Now on the Lookup tab I created a Lookup named TitleFilled with my sql server as the defined connection and the query I set as
SELECT 1 AS Expr1
FROM Property_Assignments
WHERE (Property_ID = ?) AND (Title_ID = ?)
If you look back at the ActiveX Script the ElseIf has the following in the statement
DTSLookups("TitleFilled").Execute(DTSSource("PROPERTY_ID"), DTSSource("POSITION_ID"))
the basics of DTSLookups is
DTSLookups("LookupName").Execute(Parameter1, Parameter2, Parameter3, ...n)
So as I have 2 parameters as defined by my ? and the parameters are in order of occurance I put my two values in DTSSource("PROPERTY_ID"), DTSSource("POSITION_ID") to be the lookupo values for the query.
There are other more technical things you could do with DTSLookups, this is just a simple one.
Hope this helps.
February 14, 2006 at 7:39 am
Obviously a few ways to do this. Replication is one...Especially if you start needing to worry about Delete's. If it is SQL Server to SQL Server then SQL Server Replication may be the best option (depending on scale and your comfort zone.)
I had a DB2 system I needed millions of records nightly. I am in Florida and the source was in Texas. We put a SQL box next to the DB2 with a DTS package that downloaded the data into a temp table and compared it to a mirror (pre copied) set of data from FL. I used stored procedures to compare and insert he changes to change tables (
_INSERT,
_DELETE,
_UPDATE,
_ALWAYS_UPDATE,
_[FIELD]_UPDATE). You send just the changes over the internet (went from several hours to 5 minutes) and use stored procedures on the destination server to make changes. There are third party applications that do this but cost ALOT of money. Many companies use integration servers to acheive this like biztalk.
I hope your project is more simple than having to use replication (or rewriting replication), but if you have to, it is a great project to learn DTS/Replication and SQL.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply