Introduction

According to MSDN, SSIS Services is a Microsoft Integration Service and a platform for building enterprise-level data integration and data transformations solutions. Use Integration Services to solve complex business problems by copying or downloading files, loading data warehouses, cleaning and mining data, and managing SQL Server objects and data.
Integration Services can extract and transform data from a wide variety of sources, such as XML data files, flat files, and relational data sources. They can then load the data into one or more destinations.
Prerequisites
  • Visual Studio
  • SharePoint Online account.
  • Basic knowledge of SharePoint.
To achieve our functionality, we have to follow these steps:
  • Create a list and add items to the list.
  • Create a database, table, and procedures in SQL Server
  • Create an SSIS Project in Visual Studio
  • Build and Deploy the solution
  • Schedule the SSIS packages.
Let begin.

Create a list and add an item in the list

For demonstration purposes, I have created two lists i.e Hobbies and Employee in SharePoint Online.
Hobbies - This list is a Master List
Load Data To A SQL Table From SharePoint List Using SSIS
Employee - This list consists of the lookup column (Hobbies) from the Hobbies master list.
Load Data To A SQL Table From SharePoint List Using SSIS
Load Data To A SQL Table From SharePoint List Using SSIS
This list consists of the following items.
Load Data To A SQL Table From SharePoint List Using SSIS
Now, we have the records in the list, let's create the database and table to load these records.

Create database, table, and procedures in SQL Server

To create a database, log into the SQL server with the proper credentials.
Load Data To A SQL Table From SharePoint List Using SSIS
Step 1
Select New Query from the menu and add this script (To execute script press F5)
create database SP_POC
Step 2
Once the database is created successfully, create two new tables for each list to holds employee and hobby records.
Employee List
Employee_Stage
This table will truncate weekly to holds all records for EmployeeFullPackage.dtsx as well as daily after every 15 min to holds new records for
EmployeeIncrementalPackage.dtsx
Employee
This table will truncate on weekly basis to holds all records for EmployeeFullPackage and updates daily after every 15 mins for EmployeeIncrementalPackage.dtsx
  1. CREATE TABLE Employee_Stage (
  2. ItemId int,
  3. FullName nvarchar(max),
  4. FirstName nvarchar(max),
  5. LastName nvarchar(max),
  6. PhoneNum int,
  7. Address nvarchar(max),
  8. Role nvarchar(max),
  9. IsActive nvarchar(max),
  10. Hobbies nvarchar(max),
  11. Created datetime,
  12. Modified datetime,
  13. CreatedById int,
  14. ModifiedById int,
  15. CreatedBy nvarchar(max),
  16. ModifiedBy nvarchar(max)
  17. );
  19. CREATE TABLE Employee (
  20. ItemId int,
  21. FullName nvarchar(max),
  22. FirstName nvarchar(max),
  23. LastName nvarchar(max),
  24. PhoneNum int,
  25. Address nvarchar(max),
  26. Role nvarchar(max),
  27. IsActive nvarchar(max),
  28. Hobbies nvarchar(max),
  29. Created datetime,
  30. Modified datetime,
  31. CreatedById int,
  32. ModifiedById int,
  33. CreatedBy nvarchar(max),
  34. ModifiedBy nvarchar(max)
  35. );
Hobbies List
Hobbies_Stage
This table will truncate weekly to holds all records for EmployeeFullPackage.dtsx as well as daily after every 15 min to holds new records for
EmployeeIncrementalPackage.dtsx
Hobbies
This table will truncate on weekly basis to hold all records for EmployeeFullPackage and updates daily after every 15 mins for EmployeeIncrementalPackage.dtsx
  1. CREATE TABLE Hobbies_Stage (
  2. ItemId int,
  3. Title nvarchar(max),
  4. Created datetime,
  5. Modified datetime,
  6. CreatedById int,
  7. ModifiedById int,
  8. CreatedBy nvarchar(max),
  9. ModifiedBy nvarchar(max)
  10. );
  12. CREATE TABLE Hobbies (
  13. ItemId int,
  14. Title nvarchar(max),
  15. Created datetime,
  16. Modified datetime,
  17. CreatedById int,
  18. ModifiedById int,
  19. CreatedBy nvarchar(max),
  20. ModifiedBy nvarchar(max)
  21. );
Load Data To A SQL Table From SharePoint List Using SSIS
Step 3
We required multiple stored procedures to insert or update records from stage to main table one for employee (i.e. From dbo.Employee_Stage to Employee) and another for hobbies.
EmployeeFullPackage - It will insert the records into the Employee table.
EmployeeIncrementalPackage - If the item is present in the Employee table, then it will update the records else will create the records.
Stored Procedures
A stored procedure is a group of one or more Transact-SQL statements into logical units, so that the statement can be reused over and over again.
Instead of writing an SQL query again and again, save it as a stored procedure, then just call it to execute it.
Write a new store procedure by right click on Stored Procedures -> New -> Store Procedure and paste the below scripts.
Stored Procedure for Employee - usp_MergeEmployee (Execute it by pressing F5).
  1. USE [SP_POC]
  2. GO
  3. -- ================================================
  4. -- Template generated from Template Explorer using:
  5. -- Create Procedure (New Menu).SQL
  6. --
  7. -- Use the Specify Values for Template Parameters
  8. -- command (Ctrl-Shift-M) to fill in the parameter
  9. -- values below.
  10. --
  11. -- This block of comments will not be included in
  12. -- the definition of the procedure.
  13. -- ================================================
  14. SET ANSI_NULLS ON
  15. GO
  16. SET QUOTED_IDENTIFIER ON
  17. GO
  18. -- =============================================
  19. -- Author: <Author,,Name>
  20. -- Create date: <Create Date,,>
  21. -- Description: <Description,,>
  22. -- =============================================
  23. CREATE PROCEDURE [dbo].[usp_MergeEmployee]
  24. AS
  25. BEGIN
  26. -- SET NOCOUNT ON added to prevent extra result sets from
  27. -- interfering with SELECT statements.
  28. SET NOCOUNT ON;
  30. -- Insert statements for procedure here
  31. BEGIN TRAN
  32. MERGE dbo.[Employee] AS dest
  33. USING dbo.[Employee_Stage] AS sour
  34. ON (dest.ItemID = sour.ItemID)
  35. WHEN MATCHED
  36. THEN UPDATE SET
  37. dest.[FullName] = sour.[FullName],
  38. dest.[FirstName] = sour.[FirstName],
  39. dest.[LastName] = sour.[LastName],
  40. dest.[PhoneNum] = sour.[PhoneNum],
  41. dest.[Address] = sour.[Address],
  42. dest.[Role] = sour.[Role],
  43. dest.[IsActive] = sour.[IsActive],
  44. dest.[Hobbies] = sour.[Hobbies],
  45. dest.[Created] = sour.[Created],
  46. dest.[CreatedById] = sour.[CreatedById],
  47. dest.[Modified] = sour.[Modified],
  48. dest.[ModifiedById] = sour.[ModifiedById],
  49. dest.[CreatedBy] = sour.[CreatedBy],
  50. dest.[ModifiedBy] = sour.[ModifiedBy]
  51. WHEN NOT MATCHED THEN
  52. INSERT (
  53. [ItemId]
  54. ,[FullName]
  55. ,[FirstName]
  56. ,[LastName]
  57. ,[PhoneNum]
  58. ,[Address]
  59. ,[Role]
  60. ,[IsActive]
  61. ,[Hobbies]
  62. ,[Created]
  63. ,[CreatedById]
  64. ,[Modified]
  65. ,[ModifiedById]
  66. ,[CreatedBy]
  67. ,[ModifiedBy]
  68. )
  69. VALUES ( sour.[ItemId]
  70. ,sour.[FullName]
  71. ,sour.[FirstName]
  72. ,sour.[LastName]
  73. ,sour.[PhoneNum]
  74. ,sour.[Address]
  75. ,sour.[Role]
  76. ,sour.[IsActive]
  77. ,sour.[Hobbies]
  78. ,sour.[Created]
  79. ,sour.[CreatedById]
  80. ,sour.[Modified]
  81. ,sour.[ModifiedById]
  82. ,sour.[CreatedBy]
  83. ,sour.[ModifiedBy]
  84. )
  85. OUTPUT $action, Inserted.*, Deleted.*;
  86. COMMIT TRAN
  87. END
Stored Procedure for Hobbies - usp_MergeHobbies (Execute it by pressing F5).
  1. USE [SP_POC]
  2. GO
  3. -- ================================================
  4. -- Template generated from Template Explorer using:
  5. -- Create Procedure (New Menu).SQL
  6. --
  7. -- Use the Specify Values for Template Parameters
  8. -- command (Ctrl-Shift-M) to fill in the parameter
  9. -- values below.
  10. --
  11. -- This block of comments will not be included in
  12. -- the definition of the procedure.
  13. -- ================================================
  14. SET ANSI_NULLS ON
  15. GO
  16. SET QUOTED_IDENTIFIER ON
  17. GO
  18. -- =============================================
  19. -- Author: <Author,,Name>
  20. -- Create date: <Create Date,,>
  21. -- Description: <Description,,>
  22. -- =============================================
  23. CREATE PROCEDURE [dbo].[usp_MergeHobbies]
  24. AS
  25. BEGIN
  26. -- SET NOCOUNT ON added to prevent extra result sets from
  27. -- interfering with SELECT statements.
  28. SET NOCOUNT ON;
  30. -- Insert statements for procedure here
  31. BEGIN TRAN
  32. MERGE dbo.[Hobbies] AS dest
  33. USING dbo.[Hobbies_Stage] AS sour
  34. ON (dest.ItemID = sour.ItemID)
  35. WHEN MATCHED
  36. THEN UPDATE SET
  37. dest.[Title] = sour.[Title],
  38. dest.[Created] = sour.[Created],
  39. dest.[CreatedById] = sour.[CreatedById],
  40. dest.[Modified] = sour.[Modified],
  41. dest.[ModifiedById] = sour.[ModifiedById],
  42. dest.[CreatedBy] = sour.[CreatedBy],
  43. dest.[ModifiedBy] = sour.[ModifiedBy]
  44. WHEN NOT MATCHED THEN
  45. INSERT (
  46. [ItemId]
  47. ,[Title]
  48. ,[Created]
  49. ,[CreatedById]
  50. ,[Modified]
  51. ,[ModifiedById]
  52. ,[CreatedBy]
  53. ,[ModifiedBy]
  54. )
  55. VALUES ( sour.[ItemId]
  56. ,sour.[Title]
  57. ,sour.[Created]
  58. ,sour.[CreatedById]
  59. ,sour.[Modified]
  60. ,sour.[ModifiedById]
  61. ,sour.[CreatedBy]
  62. ,sour.[ModifiedBy]
  63. )
  64. OUTPUT $action, Inserted.*, Deleted.*;
  65. COMMIT TRAN
  66. END
Load Data To A SQL Table From SharePoint List Using SSIS
In this article, we have completed the first two major steps.
Check the next part of this articles.
Load Data to an SQL Table from a SharePoint List Using SSIS - Part Two
Load Data to an SQL Table from a SharePoint List Using SSIS - Part Three