DTS copy table from source DB->destination DB

I have started to create a package in DTS. I have created 2 connections for source DB and destination DB. I want to schedule a task to copy data from source to destination

I'm not sure which task to choose from. I tried 'Data Driven Query Task'. Firstly, I entered the source, using the source DB connection. I then chose the table I want to copy.
Next I clicked on the Bindings tab and chose the destination connection and created an identical table to the source table, ready for transferring data.

I just want to transfer the data and schedule it but when I ran the package it said:

'ActiveX scripting requires Script Text and Langauge and at least one Phase function to be specified.'

I re-opened the 'Data Driven Query Task'

I clicked on the Queries tab and the Query type was set to the default 'Insert'. The next step is to build the Insert query, I thought. I clicked on the Build button and got a list of tables in the destination connection. I dragged the destination table over (the one I just created), selected all the fields and now I am lost. As I clicked on the field list, the Insert statement was generated as:

INSERT INTO tblMasterCrownOffice
(CrownOfficeUniqueID, ChangeHistory, CurrentChange, RelocationsContact)
VALUES ()

But I cannot fill in the VALUES () part as I can only choose from a lits of destination tables.

Does anyone know how to setup the package to copy data from one source connection table to a destination connection table?

Thanks.I found out that I could click on the Transformations tab and choose 'Copy Columns' transformation.

So, when I run it now I get the error

'The data driven query task requires at least one query (and associated columns) to be specified'

I don't want to set any queries. I just want the transformation to run when i execute the package.

Any ideas?|||Why not just schedule an SP to run to copy the data?

For dts.
Create the two ole db connections
Add a transform data task between them
double click on the line
Source
select the table or enter a query to filter the data
Destination
Select the destination table
Transformations
Select the mappings from source to destiation

Save the package - you can save it in msdb but I prefer to save as files.
scheduling
create a scheduled job and in a command step put dtsrun /F<package name and path>|||Originally posted by nigelrivett
Why not just schedule an SP to run to copy the data?

For dts.
Create the two ole db connections
Add a transform data task between them
double click on the line
Source
select the table or enter a query to filter the data
Destination
Select the destination table
Transformations
Select the mappings from source to destiation

Save the package - you can save it in msdb but I prefer to save as files.
scheduling
create a scheduled job and in a command step put dtsrun /F<package name and path>

Thanks,

I was using 'Data driven Query Task', when I only need 'Transform Data' task. I have to wait until next week before I can link the client's oracle table to sql-server, so I've simulated the task by scheduling a table to be copied and transformed a bit from one SQL-Server Database to another. I executed it no problem, and found the Job list under SQL Agent.

i think I'll straight copy the Oracle tables over to SQL-Server daily, then schedule an SP to run on successful completion, that will transform the data properly. my only worry is if a DTS fails then it will screw up the data. I think I'll copy the data into a temp holding table, check the no of rows are OK, and only then delete the current data with the bext day's data.