Create and schedule SQL Server jobs with SSIS Packages | SansSQL

Tuesday, June 11, 2013

Create and schedule SQL Server jobs with SSIS Packages

In my previous post, we have seen how to use SQL Server Import and export wizard to import or export data and save the package.
When you save the package that is generated using this wizard, it is actually an SSIS package.
You can also use Visual Studio to create complex SSIS packages which performs the data extraction, transformation, maintenance of database, cleanup, etc...

There may be various reasons for saving the package, but one of those reason will be to automate.
The automation can be done by scheduling the SSIS packages using the SQL Server agent Jobs.

To schedule a job which executes the SSIS package,
  1. Connect to the SQL Server
  2. Expand the "SQL Server Agent"
  3. Right-Click on "Jobs" and Click "New Job..."
  4. In the new job creation screen, give the name of the Job, select Owner and its category
  5. Navigate to the "Steps" page and click "New"
    Provide the Step Name
    Choose the Type as "SQL Server Integration Services package"
    Choose the package source. The source can be either SQL Server or File System.
    Choose the package location
  6. In the "Advanced" page, you can set the advance job step options
  7. Now you can set the schedule to this job by navigating to the "Schedules" page or you may choose to run this job manually.
  8. Once you click "OK" and come out of the wizard, your new job will appear in the Jobs list and based on the schedule set, the job will automatically execute provided that the job is enabled and the SQL Server agent is in running state.

No comments:

Post a Comment