Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Nov 2004
    Posts
    14

    Unhappy Unanswered: DTS Package hangs while editing

    I have a SQL 2000 SP4 server running on Windows 2003 SP2. About 6 months ago I started experiencing problems editing existing DTS packages. At that time it was just 1 package. (I tested that I could edit all others but this one package the first time I experienced the problem.)

    But now it seems like anytime we experience import problems, I cannot edit the package so that I can verify that the import file is good. (So far the initial problem is always with the import file and once we figure out what that is and resolve it the package runs ok again.)

    The problem is I'm planning on migrating to SQL 2005 and since I can't import DTS packages, I'm going to have to rewrite them as Integration Service Projects. If I can't edit the workflow tasks in DTS, it will be time consuming figuring out what the actual import file is. Also, most of these imports are coming from our AS400 which we are phasing out so I have to rewrite these to work with the new ERP package we are going to as we bring on each division.

    I have not installed any post SQL 2000 SP4 hotfixes. So if anyone is aware of a hotfix that resolves this issue, please let me know.
    Last edited by forbesn; 01-14-08 at 11:05.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You need to explain what problem you are seeing. Are you getting an error message? Is it failing to open?

    "It don't work no good no more" does not really give us much to go on.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have your permissions changed?
    Can you open them in design view at all?

    SQL Server 2005 does have backward compatability for DTS packages; it allows you to have them on there, but does not allow you to create them. I believe you need a service pack update to be able to do this though.
    George
    Home | Blog

  4. #4
    Join Date
    Nov 2004
    Posts
    14
    I can open the package but when I right click on the workflow task and select properties, SQL Server Hangs. I have to forcibly close Enterprise Manager. The only error I get is an Application Hang error in event viewer.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It is likely that the DTS package is attempting to find a server that no longer exists. Especially if you use the Dynamic Properties task to change server names at runtime.

  6. #6
    Join Date
    Nov 2004
    Posts
    14
    The problem with the Import file is resolved. The job now runs successfully, however I still cannot edit the workflow tasks. It cannot be a permission problem, nor is it a problem that the server no longer exists. It appears to be a problem with the DTS Editor. Also, I can copy the job and it runs but I still cannot edit the copy.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by forbesn
    It appears to be a problem with the DTS Editor.
    A bad workman always blames his tools

    Who is the package owner?
    George
    Home | Blog

  8. #8
    Join Date
    Nov 2004
    Posts
    14
    It is the transform data task that I cannot edit the properties on.

  9. #9
    Join Date
    Nov 2004
    Posts
    14
    In this case "Administrator". In other import jobs, I am listed as the owner. It appears that any of the jobs where I import from the AS400 cause Enterprise manager to hang. I use a "special" account on the AS400 to access the information. I suppose it is possible that the account still has "execute" rights but may not have browse rights? These jobs were created years ago and only fail when our AS400 goes down. (yes, our AS400 is ancient and goes down frequently.)

  10. #10
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    58
    HERE IS A COOL WAY TO GET AT YOUR DTS,


    There is an add on for SQL2005 SQL Server Management Studio that lets you see DTS packages and edit them under this version. It works really well.

    Install it, then make a connection to your sql2000 server (under database engine) then select Management, then legacy folder, all of your dts packages should appear, then migrate the packages to DSTX files and open under microsoft visual studio.
    Then you can edit away...... ( : (utility is listed under here)
    here is the tool
    http://www.microsoft.com/downloads/d...displaylang=en




    Microsoft SQL Server 2000 DTS Designer Components

    The Microsoft SQL Server 2000 Data Transformation Services (DTS) package designer is a design tool used by developers and administrators of SQL Server 2005 servers to edit and maintain existing DTS packages until they can be upgraded or recreated in the SQL Server 2005 Integration Services package format. After installing this download, SQL Server 2005 users can continue to edit and maintain existing DTS packages from the Object Explorer in SQL Server 2005 Management Studio and from the Execute DTS 2000 Package Task Editor in Business Intelligence Development Studio, without needing to reinstall the SQL Server 2000 tools. The DTS package designer in this download was formerly accessed from the Data Transformation Services node in SQL Server 2000 Enterprise Manager.

    Audience(s): Customer, Developer

    X86 Package (SQLServer2005_DTS.msi) - 5083 KB

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This sure smells like a problem with security permissions, not with DTS.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    I've had similar problems, and it was either the destination table was not there/changed or the source file was not there/changed, or some other dependent object that is not in the right place.

  13. #13
    Join Date
    Feb 2008
    Posts
    1
    Hi, I'm having the same problem and i'd notice that if you wait for a few minutes (some times up to 30) the EM returns with th task ready to be edited.

    It seems to me a bug in the editor or some kind o delay when the task editor is returning the DB structure from AS400.

    So in this case the problem is a big delay, and not a hang. Anybody else can confirm if this is the case?

    And if you find a solution for the delay i'll appreciate.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What service pack are you running?
    2000 SP4 sorted out a number of problems I had with DTS'
    George
    Home | Blog

  15. #15
    Join Date
    Nov 2004
    Posts
    14
    It appears to be a hang for me. I let is run for 4.5 hrs still no go.

Posting Permissions

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