SQL Lion

In SQL server 2000, scheduling a DTS package is not as easy as compare to SSIS packages in SQL 2005 or 2008. As the DTS packages are basically stored on the server itself and are being managed on the server only, it’s little tricky to schedule more than one packages in a single Job.

In a job we don’t have any options to directly link a DTS package other then “Operating System Command (CmdExec)” under which DTSRun command is used to run the package. The drawback here is the way Steps are linked to packages.

For example:

DTSRun /~Z0xF8E00643B5C10B856F46C910818FF9E31A87E56685F1AAEBA260B82B45427B082207B157B09E991F20459491….

Now the question is how to get such a big encrypted string for a DTS package.

But we always have an alternative solution for most of the problems. So the alternative for this is given below:

But first let us clear about some basic steps to schedule DTS package in SQL Server 2000:

1. Open SQL Server Enterprise Manager, navigate to [Server].[Data Transformation Services].[Local Packages]

Note: You can click on [Meta Data Services Packages], if the package is stored there.

2. Right click on the package at the right pane that you want to schedule, and then click on [Schedule Package…]

3.  In the dialog box named “Edit Recurring Job Schedule”, fill up the require options necessary for the given package.

4.  You can notice that it is going to create one job automatically to run the package, to note down the Job name, see the level “Job Name:” in the above dialog box.

5.  Click on OK and it’s all done.

6. If you want to reschedule the package, then simply navigate to [Server].[Management].[Jobs] and double click the job that you already noted from “Edit Recurring Job Schedule” dialog box.

7. In the new dialog box related to our package, click on “Schedules”, then double click the associated schedule for the package in the list box.

8. Then in “Edit Job Schedule” dialog box, click on the button named “Change…”

9.  Now you can re-schedule the package to a new time period.

Now let’s see how to add one more package along with our former package in the same job.

Well, it’s pretty easy. Follow the below steps to accomplish it.

Step 1: Follow the above steps for scheduling the second package (it will create another job for it).

Step 2:  Go to the second job ([Server].[Management].[Jobs]) and double click the second job and navigate to “Steps” tab.

Step 3: Select the step name and click on “Edit…” button.

Step 4: Copy the text under “Command:” textbox and close all open dialog boxes.

Step 5: Open the original job that is scheduled for the first package. Navigate to “Steps” tab and click on “New…” button.

Step 6: In the dialog box named “New Job Step”, give the name for this new step and select “Operating System Command (CmdExec)” from the “Type” dropdown box.

Step 7: Paste the text that you copied from Step 4, in the Command Textbox and press OK button.

Step 8: Double click the first step i.e. TestPackage, go to “Advanced” tab. Select “Goto step: (2) my step” under “On success action:” drop down  box.

Step 9: Press Ok and all done.

Note: The scheduled package will be executed by SQL Server Agent, so be sure that the credential info for the SQL Server Agent is correct. It must have permissions to the resources that the package is using in order to run the package successfully.