Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Location
    hong kong
    Posts
    6

    Unanswered: 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.

  2. #2
    Join Date
    Sep 2003
    Location
    hong kong
    Posts
    6
    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?

  3. #3
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    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>

  4. #4
    Join Date
    Sep 2003
    Location
    hong kong
    Posts
    6
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •