Load Data to a SQL Table from SharePoint List Using SSIS

Introduction

Hi guys, this is the third article on how to Load data to a SQL Table from a SharePoint list using SSIS. If you have not checked my previous articles, then please go through them before starting this one.

Created List and Store procedures - Load Data to an SQL Table from a SharePoint List Using SSIS - Part One
Created Full and Incremental Package using SSIS - Load data to an SQL table from a sharepoint list Using SSIS - Part Two

In my previous articles, we have completed the first three major steps, now we will target the remaining ones.

Build and Deploy the solution
Schedule the SSIS packages.

According to an article on MSDN, integration services supports two deployment models:

Project deployment model
Legacy package deployment model

The project deployment model enables you to deploy your projects to the Integration Services server.

Note

The project deployment model was introduced in SQL Server 2012 Integration Services (SSIS). With this deployment model, you were not able to deploy one or more packages without deploying the whole project. SQL Server 2016 Integration Services (SSIS) introduced the Incremental Package Deployment feature, which lets you deploy one or more packages without deploying the whole project.

In this article, we will deploy our project using the project deployment model. For another method, you can check the MSDN article

Build and Deploy the solution

Before beginning with build and deployment, check the MS SQL Server version and targeted build version in Visual Studio.

To check MS SQL Server version, open the Microsoft SQL Server Management Studio with proper credentials, open the New Query window and type below command.

select @@VERSION

Load Data To A SQL Table From SharePoint List Using SSIS

Check the targeted version in Visual Studio

Right-click on the solution - > Properties, check the Single startup project field value. It should match with your SQL Server version.

If the Single startup project field has a different SQL Server version, then change by,

Right-click on the project -> Properties, it will open the Configuration window then expand the Configuration Properties - >General, change the value of the TargetServerVersion field to your SQL server version.

Once the above step has completed, the Single startup project field should contain the new value.

Since I have a similar SQL server version and target server version, let's build the project and then deploy it.

To Build

Right-click on the project and select build options.

To Deploy

We can deploy our project in two ways.

Method 1

Right-click on the project and select deploy options.

This will open the SSIS deployment wizard. Remember, it will deploy the entire project, with all packages included.

If you want to deploy an individual package, simply right-click on the particular package itself and choose to Deploy it (This has been possible since SSIS 2016).

The SSIS deployment Wizard consists of five steps, i.e...

Introduction
Select Source
Select Destination
Review
Results

Introduction

Click on the Next button

Select Source

Select the path of our .ispac file in source wizard i.e. Project Directory\Project\Employee_SSIS\bin\Development\Employee_SSIS.ispac

Select Destination

Here, we have to choose our destination.

ServerName - Enter .(dot) or actual server name

After selecting the proper authentication, click on the connect button to enable path field.

If the SSIS catalog already exists, then please choose it, otherwise, you can also create a new folder to store the project in.

You can also create a folder from Microsoft SQL Server Management Studio under Integration Services Catalogs -> SSISDB and select this folder in the destination path.

Once the path of destination is selected, click on the Next button.

Review

In this step, you will see an overview of the actions the wizard will take. Hit the Deploy button to start the deployment.

Results

The deployment will go through a couple of steps.

Now, the project has been successfully deployed to the server. You can find the project in the catalog of the SQL server.

Execute the package on SSIS server

Right-click on the package and hit execute to execute the package on the SSIS server.

You will be taken to a dialog where you can edit certain properties, such as the connection managers, parameters, and so on.

Confirm the pop-up to open the overview reports.

The results of the reports will look like the following:

Method 2

Right-click on the solution and select Open Folder in File Explorer.

Navigate to \bin\Development i.e my case \Employee_SSIS\bin\Development, double click on Employee_SSIS file to start the deployment.

Now, it will open a deployment wizard similar to Method 1.

Follow the same steps of Method 1 to deploy the project in the SSIS server.

Schedule the SSIS packages

We are scheduling the deployed SSIS package using SQL Server Agent to avoid manually running these packages.

Please make sure that the SQL Server Agent service is running on our machine.

Open the services by typing services.msc run window. If highlighted service is not running then right-click and select the Start option to start the service.

Employee Full Package Schedule

Open the Microsoft SQL Server Management Studio with proper credentials and navigate to the Jobs node.

The new job wizard consists of six steps, i.e...

General
Steps
Schedules
Alerts
Notifications
Targets.

General

Provide the name of the job and choose the owner i.e. POC_FullPackage.

A description is optional.

Steps

In the steps tab, create the new step by clicking on New button

Note

Here, we can add multiple steps and also arrange its execution sequence one-by-one.

In step configuration, enter the name of the step, select the type as SQL Server Integration Services Package, select the Server and select the package (i.e.EmployeeFullPackage.dtsx).

After creating the step configuration, it will look like the following:

Schedules

In the Schedules tab, we can define one or more schedule to execute the package at a predefined time.

Click on the New button to create a new schedule.

Note

Since I want my EmployeeFullPackage to run only on a weekly basis, I have selected the frequency to occurs on a weekly basis. You can choose different properties depending upon the requirement.

Alerts

Configure the alerts tab to display a message to the user(I have not set any alert messages).

Notifications

We can configure this tab when we want to get the notification by email or create a log file for every job that fails or automatically delete the job when the job succeeds. (I have not set any notifications)

Targets

In this tab, select the server in which you want to execute the job (I have selected any target)

Now the job is ready in the SQL server agent.

To start the job, right-click on the job (POC_FullPackage) and select Start Job at Step.

It will start the job and the screen will look like the following:

We can view the history of our jobs, to do so, right-click on POC_FullPackage job select View History.

Employee Incremental Package Schedule

General

Name the job POC_Incremental.

Steps

Configure the steps similar to the Employee Full Package Schedule except for the package field.

In the package field, select the incremental package path instead of the full package. (i.e EmployeeIncrementalPackage.dtsx)

schedules

Since my requirement is to run the incremental package daily after every 15 min, I have configured the schedules tab like this:

I have skipped the next three-tab i.e. Alerts, Notifications and Targets.

To start an incremental job, right-click on the POC_Incremental job and select Start Job at Step.

It will start the job, and the screen will look like the following:

Now we will open the history of our POC_Incremental job (Right-click on POC_Incremental and select View History) to verify that it is running after every 15 mins.

Conclusion

Now we can load records from SharePoint List to SQL Server using SSIS service, deploy packages to SSIS server and schedule the jobs in SQL server agent.

I hope you have enjoyed this series of articles Load Data To A SQL Table From SharePoint List Using SSIS