SQL Server Performance

DTS Package vs. DTS Job

Discussion in 'SQL Server DTS-Related Questions' started by sueolo, Apr 29, 2005.

  1. sueolo New Member

    I've created both DTS packages and DTS jobs, and my question is this: since they can both be scheduled, what's the difference between a package and a job. Is one better than the other?

    If you have a package that you want to run as part of a job, when you create the job, how does the job know what package to run? When you select new->job, I don't see anyplace to include the name of the package you want the job to run.

    I know I said I have created both, but now I'm a little confused about the difference between the two and also what links or connects a package to a specific job.

    Thank you,

    Sue
  2. dineshasanka Moderator

    When you are scheduling a DTS its automatically creates a or you.so nothing much to choose to between them
  3. satya Moderator

    When you schedule the dTS package, you can see a binary coded value under that scheduled job.

    When you execute DTS package individually it runs the login context and its privileges.
    When DTS package is scheduled it runs with SQLAgent account privileges.

    All the DTS packages are stored under sysdtspackages in MSDB, which also includes the scheduled jobs information on the same database.

    In order to schedule the DTS package, right-click on the package and choose schedule package. Then you can see relevant entry under Management --> SQLAgent--> Jobs pane from EM.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. sueolo New Member

    Hi, Satya.

    Are you saying that after I schedule a package that I should see a new job for that scheduled packaged under Maintenance -> SQL Server Agent -> Jobs ?? I tried this just now and I don't see a new job listed under Jobs.

    Thanks,

    Sue
  5. Argyle New Member

    Right click on "jobs" in enterprise manager and select refresh. It doesn't update automatically.
  6. sueolo New Member

    Hello, Argyle.

    Thanks for the tip - I see the job now. I have been creating and scheduling both
    the packages and jobs independently because I did not know scheduling a package
    automatically created a job.

    Thanks for the help. Now all the other comments make sense.

    - Sue
  7. satya Moderator

    This is another problem of Enterprise Manager tool, sometimes you need to use F5 or refresh to get the value.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. ranjitjain New Member

    quote:Originally posted by sueolo

    Hello, Argyle.
    Thanks for the tip - I see the job now. I have been creating and scheduling both
    the packages and jobs independently because I did not know scheduling a package
    automatically created a job.
    Thanks for the help. Now all the other comments make sense.
    - Sue

    Hi sueolo,
    Whenever u schedule a package that means u want that package to be run at
    specific time, right....
    So its sql server's duty to create a job to accomplish this task for u, so for
    every scheduled package there will be atleast one job. U can add another schedule
    and then a new job will be created likewise.
  9. sueolo New Member

    Hello, all. Thanks for all the good information.

    Well, it must have just been "dumb luck" that I ever got this working before, so
    I'm back with more questions.

    I am creating my package as follows:

    Data Transformation Services --> All Tasks --> Export Data

    This successfully creates my package using the SQL query and the transformations I provided. Next, I need to add an ActiveX Script to append the current day's date to my output text file, so I edit my package by right-clicking on the package and selecting Design Package. The only thing I see are my connections. I do not see my Transform Data Task or my Execute SQL Task - why? Next, I add my ActiveX Script task and add my Execute SQL Task (again).

    When I run the package, it works (I get my file with the date appended - thank goodness for small favors). When I schedule the package and run the job, I get a file (no date appended).

    Any thoughts on what is going on will be greatly appreciated.

    Thank you,

    Sue



  10. ranjitjain New Member

    Through EM, Right click on any data task and check for the workflow
    properties and task properties.
    The normal window size is reduced to 10%.
    U can zoom 150% to check the package more clearly.
    I feel ur package has to run with date appended file.
    Anyways ill check this on my machine and will come back to u.
  11. sueolo New Member

    Hi, ranjitjain!

    Thanks for your reply and additional info. All the responses are really helping me
    learn more about DTS packages and certainly providing more info than I found in the documentation. I deleted my job and started over this morning, so I'll see if I get my file with date appended. I ran both the package and the job "on demand" this morning, and both created a text file with date appended (the file that was generated at 5:00 p.m. yesterday did NOT have the date appended).

    From the package designer, when I right-click on my SQL task and view workflow --> workflow properties, there is nothing set up in either Precedence or Options tabs (should there be something there?) When I right-click on properties, I see my SQL query.

    The same is true for my ActiveX Script task (nothing set up in either Precedence or Options tabs) except properties shows my ActiveX Script.

    When I right-click on Connection 2 (destination with data transformation), I can view properties only, workflow properties is greyed out. The same is true of connection 1 (source).

    Again, thank you for your help.

    - Sue

  12. ranjitjain New Member

Share This Page