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
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]()
.