Using SSIS and Excel Services to Build a Lightweight Reporting Solution for SharePoint-Based Applications (Part 3)

Using SSIS and Excel Services to Build a Lightweight Reporting Solution for SharePoint-Based Applications (Part 3)

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.

clip_image001

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

clip_image002

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

clip_image004

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’.

clip_image005

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

clip_image007

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.

clip_image009

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.

clip_image011

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.

clip_image012

clip_image013

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

clip_image015

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.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s