Results 1 to 15 of 15
  1. #1
    Join Date
    Jul 2004
    Posts
    5

    Unanswered: Changing Connections in DTS Packages

    I have 4-5 DTS packages with more than 100 connections. If I want to change the connections of the source (Oracle Database) from Server 1 to Server 2, how do I do it dynamically....? I know that I can go to Disconnected Edit and change the connections manually , but it is a very tedious process as there are more than 100 connections.

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm moving this thread to the Microsoft SQL Server Forum where it will get attention from users with more Microsoft experience.

    Do you actually have more than 100 connections (the Oracle Connection icon is the gold twin-disk-drives)? I have not EVER seen that happen, and I've seen a lot of Oracle to SQL Server DTS packages!

    -PatP

  3. #3
    Join Date
    Jul 2004
    Posts
    5

    connetions

    Pat

    Thanks for the reply. If I add up all the connections in 3-4 packages I will have 100 connections.

    Let me know if you know any solution.
    Thanks.

  4. #4
    Join Date
    Jun 2009
    Posts
    6
    Hi kir441parupudi,

    Did you ever get a resolution to this problem?

    Paul

  5. #5
    Join Date
    Jun 2009
    Posts
    26
    Hi Paul,

    I suspect you'll have to update each connection in each DTS package manually... I don't think there's any other way around it.

    The only workaround I could suggest is doing some DNS / hostfile trickery on the SQL Server to make traffic pointing to server1 go to server2. Not ideal but it may buy you some time until you can cut over all of your connections.

  6. #6
    Join Date
    Jun 2009
    Posts
    6
    Hi Wilvis,

    Thanks for the response. I suspected as much and looks as if the manual update will be the way we go.

    By the way, has this model changed much in SQL Server 2005 or 2008?

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yes - you will be using SSIS from 2005 onwards as DTS has been deprecated.

    In SSIS you define each connection object once per package (or even solution) which means this should no longer be a problem.
    George
    Home | Blog

  8. #8
    Join Date
    Jun 2009
    Posts
    6
    Hi George,

    Thanks for that. We have a couple of 2005 servers but the economic climate makes sure that our current SQL Servers we will be on 2000 for some time.

    Cheers

    Paul

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Export the DTS package to a VB file. Do a global replace within that VB file using the text editor of choice. Import the resulting VB program as a DTS package. Toddle merrily on your way.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Jun 2009
    Posts
    6
    Hi Pat,

    Sounds great...but what about the 'rumours' of the 'look' being changed?

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Other than a bit less hair (on top anyway) and a bit more weight around the middle (which I'm trying to lose), the look hasn't changed much.

    Would you care to clarify your question a smidgeon ???

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Jun 2009
    Posts
    6
    Ok,

    I've heard, not yet attempted, that exporting, changing and importing packages could make them no longer visually correct!?!

    DOn't know if this has any merit but don't want to mess up an environment that has been running until now.

  13. #13
    Join Date
    Jun 2009
    Posts
    6
    Will try this on a dev server in the morning.

    Cheers for now.

    Paul

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    One way to find out
    Just compile run the VB file to a package of a different name after you've made a change!

    I did it recently. but can't remember if the layout changed...
    George
    Home | Blog

  15. #15
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Script, script, script. If the packages are simply loading data from csv,.txt files (or oracle tables), and then kicking off sprocs (stage then load) I would script it out, I know it is more tedious, but certainly a lot more flexible. It will take a bit longer in the early run, but once you master it, you can make things like this lookup table/file controlled.

    I guess coming from a UNIX/Sybase background, I can't seem to give this method of ETL up.

    For the 100 connections I would simply create a for loop (or multiple) whether via a shell script or sql structure that either looks up via a table or delimited file, etc. and possibly do the loads that way, just my 2 cents.
    Last edited by PMASchmed; 06-16-09 at 09:32.

Posting Permissions

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