Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    62

    Unanswered: Transfer SQL Server Objects task

    does anyone know if it is possible to use a list of tables to use in the Transfer SQL Server Objects Task step in SSIS for the Object List parameter?

    I want to move about 500 tables from SQL 2000 to Sql 2005 using SSIS but i don't want to have to check each table off, rather i have the table names stored in a table and would like to query that and have that list be the used for the Object List for my tables.

    Any ideas if this is possible?

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Look into Foreach Loop container and ADO enumerator. You can connect to a data source, retrieve each table, and form a CREATE TABLE script along with forming a BCP statement.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Sep 2009
    Posts
    62
    do you have an example of using a bcp script to pass data from one table to another table on a seperate server?

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ooops, you got me. BCP only deals with data files. And of course suggesting to load the data from the source table into a file before loading it into the target table will not be the most optimal. But you can use Data Flow Task with source and destination as variables.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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