Results 1 to 8 of 8

Thread: dts package?

  1. #1
    Join Date
    Dec 2003
    Posts
    23

    Unanswered: dts package?*****Resolved******

    I have a DTS package schedule to run once each day and the package is save in Enterprise Manager. Does Enterprise Manager have to be loaded for the DTS package to run on schedule? Thanks.
    Last edited by xayavon; 05-14-04 at 18:56.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    EM is just an application interface to sql server.

    sql server stores the jobs and dts packages in the master database.

    sql server agent takes care of the execution of all your jobs, and knows where to find your "stuff"

    The is service the is constantly running

    Anyone know how the internals of this works.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    To answer the original question, no SQL Enterprise Manager does not need to be running for a scheduled package to run. SQL Agent needs to be running on the server.

    The DTS packages and the Job scheduling information are actually stored in the MSDB database, not in master.

    While I don't know nearly as much as I'd like to, I know enough to get both of us into major trouble without even breaking a sweat!

    -PatP

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    msdb, huh....

    Hell, failed that interview....


    Had a dba here who wanted to "clean up" his server and get rid of un-needed databases...

    Northwind
    Pubs
    model
    msdb
    master


    scrub....

    He didn't last very long....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Dec 2003
    Posts
    23
    How do I get slq agent running? I keep getting that error that sqlagent is not running.

    Thanks

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The easy way is to start the SQL Service Manager, select the correct server in the top drop down box, select SQL Server Agent in the lower box, then click the button with the green "start" triangle.

    -PatP

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I have heard of some places that will drop pubs and Northwind. I never do. Too useful for testing stuff.

    As for the DTS internals, I will take an ill-advised stab at explaining it all.

    When you create a new DTS package, you are really creating a VB script which will do all the things that you told (not necessarily wanted) the DTS package to do. Technically, you could write a VB script in notepad to run a DTS package, but I already tried it. It stank.

    The generated script is stored by default in the msdb database (sysdtspackages), but can be stored as a VB script, a proprietary DTS formatted file, or in the SQL Server metadata repository (have not touched that, myself).
    A VB script version could be run on any windows machine, but will promptly choke, if it can not find the .dlls in order to get all the fancy functions it needs.

    Actual package formatted DTS packages (from msdb, Metadata, or the proprietary file format) can all be run by the dtsrun.exe utility. This is what SQL Agent calls, when you schedule a package to run. It also happens to be the same executable that Enterprise Manager calls to run a package for you on your laptop. This is where the client dependencies start. If you have a SQL 2000 Enterprise Manager, you can run your package locally no problem. When you try to run the "same" package via SQL Agent on a SQL 7.0 server, you get nothing but errors. This is because you have asked the script to created with one library, and run with a separate library, and your package has only one library card (sorry, it is past 5:00).

    So, now that you are no doubt utterly confused, the answer is "No, Enterprise Manager does not have to be running, but SQLAgent has to be running (in order to spawn the process), the dtsrun.exe utility has to be there (it is for any SQL Server install), and the dts*.dlls have to be there, and be the right version." Clear as mud?

  8. #8
    Join Date
    Dec 2003
    Posts
    23

    Resolved

    Thanks everyone for the help. I tried it and it's working just how I want it too. Thanks agian.

Posting Permissions

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