Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2010
    Posts
    2

    Unanswered: Automating data transfer

    Hi All,

    I am uisng SQL Server 2000. I need to transfer tables in about 116 databases from AS400 to SQL Server.Is there anyway to automate this? right now I am creating DSN for each data source and using DTS to import tables from each data base. This work is too much for 116 databases. What is the approach I need to take in order to automate this task? Your help is greatly appreciated

    Thank you,
    Assh

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is a bit kinky, but...

    1) Create a work table with an NVARCHAR(256) column
    2) Populate the column with ADO connection strings for all N of the AS400 databases
    3) Create a DTS package that uses a UDL to access an AS400 table and copy it to SQL Server
    4) Create another DTS package to iterate the table in step 2 to create a scratch UDL, then run the DTS package from step 3
    5) Watch the fun!
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2010
    Posts
    2
    Pat Phelan,

    Thank you for your response. Could you please explain me the above steps with small example? I am new to ADO connection and UDL. Could you please explain it with more detail?

    Thank you,
    Assh

Posting Permissions

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