Load Data to a SQL Table from SharePoint List Using SSIS - Part Three

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.
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.
 
Load Data To A SQL Table From SharePoint List Using SSIS 
 
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.
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
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.
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
To Deploy
 
We can deploy our project in two ways.
 
Method 1
 
Right-click on the project and select deploy options.
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
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 
 
Load Data To A SQL Table From SharePoint List Using SSIS
 

Select Source

 
Select the path of our .ispac file in source wizard i.e. Project Directory\Project\Employee_SSIS\bin\Development\Employee_SSIS.ispac
 
Load Data To A SQL Table From SharePoint List Using SSIS 
 

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.
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
If the SSIS catalog already exists, then please choose it, otherwise, you can also create a new folder to store the project in.
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
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.
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
Once the path of destination is selected, click on the Next button.
 
Load Data To A SQL Table From SharePoint List Using SSIS
 

Review

 
In this step, you will see an overview of the actions the wizard will take. Hit the Deploy button to start the deployment.
 
Load Data To A SQL Table From SharePoint List Using SSIS
 

Results 

 
The deployment will go through a couple of steps.
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
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.
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
You will be taken to a dialog where you can edit certain properties, such as the connection managers, parameters, and so on.
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
Confirm the pop-up to open the overview reports.
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
The results of the reports will look like the following:
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
Method 2
 
Right-click on the solution and select Open Folder in File Explorer.
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
Navigate to \bin\Development i.e my case \Employee_SSIS\bin\Development, double click on Employee_SSIS file to start the deployment.
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
Now, it will open a deployment wizard similar to Method 1.
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
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.
 
Load Data To A SQL Table From SharePoint List Using SSIS
 

Employee Full Package Schedule 

 
Open the Microsoft SQL Server Management Studio with proper credentials and navigate to the Jobs node.
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
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.
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
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. 
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
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).
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
After creating the step configuration, it will look like the following: 
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
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.
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
Alerts
 
Configure the alerts tab to display a message to the user(I have not set any alert messages).
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
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)
Load Data To A SQL Table From SharePoint List Using SSIS
 
Targets
 
In this tab, select the server in which you want to execute the job (I have selected any target)
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
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.
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
It will start the job and the screen will look like the following:
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
We can view the history of our jobs, to do so, right-click on POC_FullPackage job select View History.
 
Load Data To A SQL Table From SharePoint List Using SSIS
 

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)
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
schedules
 
Since my requirement is to run the incremental package daily after every 15 min, I have configured the schedules tab like this:
 
Load Data To A SQL Table From SharePoint List Using SSIS 
 
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.
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
It will start the job, and the screen will look like the following:
 
Load Data To A SQL Table From SharePoint List Using SSIS
 
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.  
Load Data To A SQL Table From SharePoint List Using SSIS
 

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.