SSIS package deployment

In the Solution Explorer, right-click the Integration Services package to rename it. Let us rename it to something that has to do with the transfer of tables for sales data.

Now, right click the SSIS project to convert to Package Deployment Model:

Now, open properties for the SSIS project:

And set the output path for SSIS Package deployment:

Open the Microsoft SQL Server Management Studio, and connect to the Database Engine.

In the Object Explorer, notice the SQL Server Agent at the bottom. Expand it to see the sub-folders. (If the SQL Server Agent service is not running, you must right click the SQL Server Agent icon to start it.)

We will use the SQL Server Agent to automatically execute scheduled jobs for us. But first we must create a scheduled job that runs the SSIS package at fixed intervals.

Right click the Jobs folder to add a new job.

At the first page, the General page, we can give the job a proper name, e.g. TDWD1Job.

Go to the Steps page and click the New button. At the New Job Step page we must enter information about Name, Type and Source.

• Step name: Run Sales Transfer SSIS Package.
• Type: SQL Server Integration Services Package.
• Run as: SQL Server Agent Service Account.
• Package Source: File System, Deployment Output path.

The new job step, when fully configured, must now look like this:

Now, go to the Schedules page and click the New button. At the New Job Schedule page you may enter a name and properties for the new schedule.

Complete this lesson by running the job a few times. Check that data in the Multidimensional database aren’t missing and hasn’t been doubled.

Make sure that the job can execute and complete without errors.

Notice: At this point you may get an authentication error. If you do this, open Properties for the job and change the owner of the SQL Server Agent Job. Change it to System Administrator = [sa].