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 |
|
Willio
Starting Member
20 Posts |
 Posted - 2007-07-16 : 08:42:10
|
| Hello,I need to create a DTS-package to create a right connection between two different SQL databases.The package need to execute a query and write the result into an other database. I'v looked over the internet but can't find a right example. So i hope anyone can help with this.Thanks!Willio |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-07-16 : 10:39:42
|
| create a source server and destination server (oledb providers)add a data transform between themtype in the query as the sourceselect the destmap the columnssave the result.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Willio
Starting Member
20 Posts |
Posted - 2007-07-17 : 07:39:59
|
| Could you be more specific cause my knowledge of DTS is only a little.I created a connection to the source server and can perform a query. That query will write it's result in a recordset. Question remains is how to write the data to the destinationserver. Should i perform an insert query or is there an other way?Thanks! |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-07-17 : 08:01:40
|
| >> create a source server and destination server (oledb providers)Those are the things that look like servers.>> add a data transform between themTask, 3 data transform task. Click on the source connection then the destination.Now double click on the line that has appeared between the two connections.You can now configure the source, destination (table) and column mappings.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Willio
Starting Member
20 Posts |
Posted - 2007-07-17 : 08:30:13
|
Finally somebody with a right answer  . It works :-). One question remains, if i run the again now it should delete all records in the destination-table. Is that also possible with that function?Thanks  |
|
|
|
Willio
Starting Member
20 Posts |
Posted - 2007-07-17 : 08:40:20
|
| I have created an sql-query that deletes all rows and on succes it runs the copy.Works perfect but maybe it isn't the best method....Greetings! |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-07-17 : 08:51:49
|
| That's the usual way to do it.If you're running as the table owner (or sa) and there are no foreign keys you can do a truncate which will be faster than a delete.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
DTS-package query import