• Part 1 – Introduction & Building the Back-End Reporting Database
• Part 2 – Developing SSIS Package to Import Data from Sharepoint List to Reporting Database
• Part 3 – Setup SQL Server Agent Job to periodically execute the SSIS Package
• Part 4 – Building Reports and Publish on Sharepoint using Excel Services

This part of the blog series will go through the steps of setting up a SQL Server Agent Job to periodically execute the SSIS package created in previous post.

Step 1: Create a SSIS package execution proxy account. The SSIS package created in previous post will be executed under this account.

To create this proxy account, you need first create a new Credential. Open SQL Server Management Studio and connect to the server/instance where the reporting database is located, and you can find the Credentials folder under the Security folder. Please make sure this credential has rights to access ‘Sports Court Log’ Sharepoint list.

Then you need create a new Proxy Account under ‘SSIS Package Execution’ folder.

When creating the Proxy account, use the credential you just created, and also set the account active to ‘SQL Server Integration Services Package’ subsystems.

Step 2: Copy the SSIS package file (with .dtsx as file extension) to the SQL Server or somewhere the SQL Server can access to.

Step 3: Create a SQL Server Agent Job. You can find the agent jobs folder under ‘SQL Server Agent’.

Right-click the ‘Jobs’ folder and select ‘New Job…’. Give a meaningful name of the job and set the job owner.

Go to the ‘Steps’ page and add a new Job step. Select ‘SQL Server Integration Services Package’ as the job type, and set the job running as the SSIS package execution proxy account we created at step 1. Select ‘File system’ as the Package source and point the path to our SSIS package.

After the job step is created, go to the Schedule page and add a new Job Schedule. In this example, I set the SSIS package to be executed at 2:00am every morning.

Now the SQL Server Agent Job has been created. You can give the job a test run to check if the package has been deployed successfully.

Also, you can view the execution history of the SSIS package.

This part of the blog series introduces how to setup SQL Server Agent Job to periodically execute the SSIS package. Next part of the blog will build a sample report using Excel Pivot Table/Charts and publish the report onto Sharepoint through Excel Services.

• SSIS/SSAS/SSRS/MDX