SQL Server Performance Forum – Threads Archive
DTS Package vs. DTS Job
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, SueWhen you are scheduling a DTS its automatically creates a or you.so nothing much to choose to between them
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.
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
Right click on "jobs" in enterprise manager and select refresh. It doesn’t update automatically.
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
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.
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
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.
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
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.
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
check this link :
How to overwrite DTS package logs everytime the package executes?
http://vyaskn.tripod.com/sql_server_dts_error_file.htm
]]>