SSIS: Creating a Job To Run An SSIS Package On A SQL Server

For this blog post I'm going to use the test SSIS package I created as part of the "Exporting Query Results To A Flat File"-blog post, and had previously deployed to SQL Server (see "Deploying A Package To A Remote SQL Server") because it's all setup and I know it works.

Start SQL Server Management Studio;

Management Studio: Connect to Server

Make sure you select "Database Engine" in the "Server type" drop down. Click "Connect";

SQL Server: Object Explorer

Expand the "SQL Server Agent" node in the "Object Explorer" (left-hand side) and then right-click the "Jobs" node;

SQL Server: Creating A New Job

Click on "New Job ...";

SQL Server: New Job Dialog

Enter a Name (I've chosen "SSISDataExportDemo") and then select "Steps" from the list on the left;

SQL Server: New Job Dialog (Steps)

Click on "New..." (highlighted above);

SQL Server: New Job Step Dialog

Enter a "Step Name" (we're only going to have one so it's not really important) and then change the Type from "Transaction SQL script (T-SQL)" to "SQL Server Integration Services Package" and a lot of new options will appear;

SQL Server: New Job Step (SQL Server Integration Services Package)

You need to select the SQL Server you wish to run the package on (doesn't have to be the one you're currently connected to) and then select the name of the package. Once you've done that click "OK";

SQL Server: New Job (Steps)

As you can see the new step we've just created is now listed.

It is now ready to run but if the job is only going to be run as a one-off you might want to click "Notification" on the left and select "Automatically Delete Job" (when the Job Succeeds);

SQL Server: New Job (Notifications)

This way you'll keep your SQL Server tidy but you should bear in mind that if you do this the job will run immediately and then be gone so you won't be able to see anything in the following steps!

Click "OK" when you're done (a few seconds will pass while the script to create the job is executed on the SQL Server).

Now you can expand the "Jobs" node on the SQL Server and find the new Job you've just created (usually it's inserted at the bottom of the list) and right-click it;

SQL Server: Job Options

Choose "Start Job at Step...";

SQL Server: Start Jobs

The job will run and will take as long as it takes and then you'll see the result (as above). Now that we know the Job is there and works we can schedule it by right-clicking it and bringing up "Properties" and select the "Schedules" page;

SQL Server: Job Properties (Schedules)

Click on "New ..." to create a New Schedule;

SQL Server: New Job Schedule

I've changed the "Schedule type" to "One time" and set it to run in a few minutes but there is a lot of flexibility to schedule the task as and when you see fit.

When you're done click "OK", and then "OK" again to save your changes.