Post
Ask Question

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

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. );    
  18.     
  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. );    
  11.     
  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;    
  29.     
  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;    
  29.     
  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. 
 
  


Similar Articles