HOW TO: Schedule a Package with Visual C++ in SQL Server 2000 (316799)

SUMMARY

This article demonstrates how to schedule a Data Transformation Services (DTS) package through Microsoft Visual C++, which may be useful if you must schedule a DTS package programmatically. The example creates a job that runs from January 08, 2002 to January 10, 2002 between 12:30 PM and 14:30 PM, once a day.

back to the top

Code Sample

This article assumes that a DTS package already exists. If you do not have a DTS package, please create one through SQL Server Enterprise Manager, Microsoft Visual Basic, or Microsoft Visual C++. This is a sample program and you must modify the code if you want to use it in your work environment.

#include <iostream.h>
#undef EOF
// Provide the correct path for Sqldmo.dll. 
#import "D:\program files\Microsoft SQL Server\80\Tools\Binn\sqldmo.dll" no_namespace

int main(int argc, char* argv[])
{
   CoInitialize(NULL); 

   try
   {

   // Create the SQL Server Object
   _SQLServerPtr mySQLServer(__uuidof(SQLServer));
   // Replace the <server_name>, <user_name>, and <password> with yours. 
   mySQLServer->Connect("<server_name>","<user_name>","<password>"); 
   
   // Create the Jobs Object
   JobsPtr myJobs;

   // Create the Job Object
   _JobPtr myJob(__uuidof(Job));
   // This name will show up under Jobs in Enterprise Manager.
   myJob->Name = "myTestJob";

   // Create the JobStep
   _JobStepPtr myJobStep(__uuidof(JobStep));
   myJobStep->Name = "Job Step 1";
   myJobStep->StepID = 1;

   // Execute a package with a trusted connection.
   // Replace the <server_name> and <package_name> with yours.
   // Check which options are correct for dtsrun.
   myJobStep->Command = "DTSRun /S <server_name> /E /N <package_name>";

   // Create the JobSchedule Object
   _JobSchedulePtr myJobSchedule(__uuidof(JobSchedule));
   myJobSchedule->Name = "myTestPackage";

   // Job occurs every day
   myJobSchedule->Schedule->FrequencyType = SQLDMOFreq_Daily;

   // Job occurs one time every day
   myJobSchedule->Schedule->FrequencyInterval = 1;

   myJobSchedule->Schedule->ActiveStartDate = 20020108;
   myJobSchedule->Schedule->ActiveEndDate = 20020110; 

   myJobSchedule->Schedule->ActiveStartTimeOfDay = 123000;
   myJobSchedule->Schedule->ActiveEndTimeOfDay = 143000;
 
   // Add the Job Object to the JobServer
   mySQLServer->JobServer->Jobs->Add(myJob);
   
   // Start the changes to the job schedule
   myJob->BeginAlter (); 
   
   // Add the JobSchedule to the Job
   myJob->JobSchedules->Add(myJobSchedule);

   // Add the Job Step to the Job
   myJob->AddStepToJob(myJobStep);
   myJob->StartStepID = 1;

   // Apply changes to the Job Schedule
   myJob->DoAlter ();

   }
   catch (_com_error& e)
   {
      cout << "Source     : " << e.Source() << endl;
      cout << "Error      : " << e.Error() << " " << endl;
      cout << "Description:  " << e.Description() << endl ;
   }

   return 0;
}
				

back to the top