Load Data To A SQL Table From SharePoint List Using SSIS - Part Two

Hi guys, this article is series of my previous article Load data to a SQL table from sharepoint list using SSIS Part one. I recommend you to please go through it before viewing this one.

Prerequisites

• Visual Studio
• SharePoint Online account.
• Basic knowledge of SharePoint. 

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

• Create SSIS Profontject in visual studio
• Build and Deploy the solution
• Schedule the SSIS packages.

Create SSIS Project in Visual Studio

Let's start by opening the Visual Studio and running as administrator.

Step 1

From file menu select New -> Project. 

Step 2

Under New Project window, select Business Intelligence -> Integration Services -> Integration Services Project.

Enter the name of your project (e.g., Employee_SSIS) and select a folder where you want to save the project.

Once the project gets created, let's create the project params and connection in the next two steps. 

Step 3 

To create project params, click on solution explorer -> Project.params

Create at least these four parameters which are required in our projects.

Step 4

Now create the new connection, right click on Connection Managers- > New Connection Manager 

Select OLEDB option from type and click on Add button 

Click on New button 

Select the appropriate Server name, authentication and database, check the connection by clicking on test connection button.

Now we are ready to create SSIS packages

EmployeeFullPackage 

This package will run on a weekly basis (although you can configure it differently).

Functionality

• In the first step, it will truncate all tables; i.e Employee_Stage, Employee, Hobbies_Stage and Hobbies.
• In the second step, it will copy all items from Employee and Hobbies list and store into Employee and Hobbies buffer.
• In the third step, it will add all items from Employee buffer to Employee_Stage table and from Hobbies buffer to Hobbies_Stage table.
• In the last step, it will call the stored procedures usp_MergeEmployee and usp_MergeHobbies (created in previous article) to copy records from stage to main table.

Right click on SSIS package -> New SSIS package and name it as EmployeFullPackage.dtsx

Open the EmployeeFullPackage.dstx and drag the relevent component from SSIS Toolbox to control flow.

Control Flow

The control flow task consists of three main tasks; i.e Execute SQL Task, Data Flow Task and Execute SQL Task 1  

We have selected three components [Data Flow Task(1) and Execute SQL Task(2)] from SSIS toolbox and added into the EmployeeFullPackage.dstx control flow tab.

Execute SQL Task

It is used to execute the SQL statement.

In this task, we will truncate our table i.e. Employee_Stage, Employee, Hobbies_Stage and Hobbies.

Open the task, rename the task as Truncate Table Employee Hobbies  and configure in this way.

Open SQL Statement and paste the following script.

TRUNCATE TABLE dbo.[Employee_Stage]  
Go  

TRUNCATE TABLE dbo.[Employee]  
Go  
  
TRUNCATE TABLE dbo.[Hobbies_Stage]  
Go  

TRUNCATE TABLE dbo.[Hobbies]  
Go

Data Flow Task

In this task, we will get all records from Employee and Hobbies list and add into Employee_Stage and Hobbies_Stage table. 

Click on Data flow task and select the relevant component from SSIS toolbox by dragging and dropping to data flow.

For demo purposes, we have selected only three components [Script Component(1) and OLE DB Destination(2)] in data flow task.

Let's configure each component in data flow task.

Script Component

Basically, the script component consists of three sub tasks; i.e Script, Inputs and Outputs, Connection Managers.

Script

In this task, we will add the project variables under Custom Properties - > ReadOnlyVariables.

Inputs and Outputs

In this task, we will create the ouputs (by clicking on Add Ouput) and their columns (by clicking Add Column). 

Employee 

Create column ItemId for Employee Output in this way.

Note

Create the same number of output columns with datatype that exists in employee table.

Now, create the remaining output columns with proper datatype in the following ways.

• FullName (DataType - Unicode string [DT_WSTR] and Length - 500) 
• FirstName (DataType - Unicode string [DT_WSTR] and Length - 500)
• LastName (DataType - Unicode string [DT_WSTR] and Length - 500)
• PhoneNum (DataType - numeric [DT_NUMERIC])
• Address (DataType - Unicode string [DT_WSTR] and Length - 4000)
• Role (DataType - Unicode string [DT_WSTR] and Length - 500)
• IsActive(DataType - Unicode string [DT_WSTR] and Length - 50)
• Hobbies(DataType - Unicode string [DT_WSTR] and Length - 4000)
• Created (DataType - date [DT_DATE])
• CreatedById (DataType - numeric [DT_NUMERIC])
• Modified (DataType - date [DT_DATE])
• ModifiedById (DataType - numeric [DT_NUMERIC])
• CreatedBy (DataType - Unicode string [DT_WSTR] and Length - 500)
• ModifiedBy (DataType - Unicode string [DT_WSTR] and Length - 500)

In the same way create the output Hobbies and their columns.

Hobbies

Output Hobbies columns are: 

• ItemId (DataType - numeric [DT_NUMERIC])
• Title (DataType - Unicode string [DT_WSTR] and Length - 500)
• Created (DataType - date [DT_DATE])
• CreatedById (DataType - numeric [DT_NUMERIC])
• Modified (DataType - date [DT_DATE])
• ModifiedById (DataType - numeric [DT_NUMERIC])
• CreatedBy (DataType - Unicode string [DT_WSTR] and Length - 500)
• ModifiedBy (DataType - Unicode string [DT_WSTR] and Length - 500)

 Connection Managers

 Open the connection manager in script component and add the appropriate connection.

Add the link from Script component to OLE DB Destination and OLE DB Destination1 for full package.

 

Now, we will write code to fetch the item from the list and insert into table. 

Go back to the script section and click on Edit Script button.

 It will open the code in a new window solution.

Add the references in a new window solution in this way.

Add these four references from the following paths:

• System.Net.Http - Assemblies - > Framework or C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.5\System.Net.Http.dll
• Microsoft.SharePoint.Client - C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll
• Microsoft.SharePoint.Client.Runtime - C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll
• Newtonsoft.Json- C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Newtonsoft.Json\v4.0_12.0.0.0__30ad4fe6b2a6aeed\Newtonsoft.Json.dll

Create two helper classes and paste the following code in it. 

Note

The namespace can be different because it is automatically generated by Visual Studio.

SPHttpClient.cs

using Newtonsoft.Json;  
using Newtonsoft.Json.Linq;  
using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Net.Http;  
using System.Net.Http.Headers;  
using System.Text;  
using System.Threading.Tasks;  
  
namespace SC_89b6d39124744fa083fe63b6b3706317  
{  
  
    public class SPHttpClient : HttpClient  
    {  
        public SPHttpClient(Uri webUri, string userName, string password) : base(new SPHttpClientHandler(webUri, userName, password))  
        {  
            BaseAddress = webUri;  
        }  
        /// <summary>  
        /// Execure request method  
        /// </summary>  
        /// <param name="requestUri"></param>  
        /// <param name="method"></param>  
        /// <param name="headers"></param>  
        /// <param name="payload"></param>  
        /// <returns></returns>  
        public JObject ExecuteJson<T>(string requestUri, HttpMethod method, IDictionary<string, string> headers, T payload)  
        {  
            HttpResponseMessage response;  
            switch (method.Method)  
            {  
                case "POST":  
                    var requestContent = new StringContent(JsonConvert.SerializeObject(payload));  
                    requestContent.Headers.ContentType = MediaTypeHeaderValue.Parse("application/json;odata=verbose");  
                    DefaultRequestHeaders.Add("X-RequestDigest", RequestFormDigest());  
                    if (headers != null)  
                    {  
                        foreach (var header in headers)  
                        {  
                            DefaultRequestHeaders.Add(header.Key, header.Value);  
                        }  
                    }  
                    response = PostAsync(requestUri, requestContent).Result;  
                    break;  
                case "GET":  
                    response = GetAsync(requestUri).Result;  
                    break;  
                default:  
                    throw new NotSupportedException(string.Format("Method {0} is not supported", method.Method));  
            }  
  
            response.EnsureSuccessStatusCode();  
            var responseContent = response.Content.ReadAsStringAsync().Result;  
            return String.IsNullOrEmpty(responseContent) ? new JObject() : JObject.Parse(responseContent);  
        }  
  
          
        public JObject ExecuteJson<T>(string requestUri, HttpMethod method, T payload)  
        {  
            return ExecuteJson(requestUri, method, null, payload);  
        }  
  
        public JObject ExecuteJson(string requestUri)  
        {  
            return ExecuteJson(requestUri, HttpMethod.Get, null, default(string));  
        }  
  
  
        /// <summary>  
        /// Request Form Digest  
        /// </summary>  
        /// <returns></returns>  
        public string RequestFormDigest()  
        {  
            var endpointUrl = string.Format("{0}/_api/contextinfo", BaseAddress);  
            var result = this.PostAsync(endpointUrl, new StringContent(string.Empty)).Result;  
            result.EnsureSuccessStatusCode();  
            var content = result.Content.ReadAsStringAsync().Result;  
            var contentJson = JObject.Parse(content);  
            return contentJson["d"]["GetContextWebInformation"]["FormDigestValue"].ToString();  
        }  
    }  
} 

SPHttpClientHandler.cs 

using Microsoft.SharePoint.Client;  
using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Net;  
using System.Net.Http;  
using System.Security;  
using System.Text;  
using System.Threading;  
using System.Threading.Tasks;  
  
namespace SC_89b6d39124744fa083fe63b6b3706317  
{  
    public class SPHttpClientHandler : HttpClientHandler  
    {  
        public SPHttpClientHandler(Uri webUri, string userName, string password)  
        {  
            CookieContainer = GetAuthCookies(webUri, userName, password);  
            FormatType = FormatType.JsonVerbose;  
        }  
  
  
        protected override Task<HttpResponseMessage> SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)  
        {  
            request.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f");  
            if (FormatType == FormatType.JsonVerbose)  
            {  
                //request.Headers.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json;odata=verbose"));  
                request.Headers.Add("Accept", "application/json;odata=verbose");  
            }  
            return base.SendAsync(request, cancellationToken);  
        }  
  
  
        /// <summary>  
        /// Retrieve SPO Auth Cookies   
        /// </summary>  
        /// <param name="webUri"></param>  
        /// <param name="userName"></param>  
        /// <param name="password"></param>  
        /// <returns></returns>  
        private static CookieContainer GetAuthCookies(Uri webUri, string userName, string password)  
        {  
            var securePassword = new SecureString();  
            foreach (var c in password) { securePassword.AppendChar(c); }  
            var credentials = new SharePointOnlineCredentials(userName, securePassword);  
            var authCookie = credentials.GetAuthenticationCookie(webUri);  
            var cookieContainer = new CookieContainer();  
            cookieContainer.SetCookies(webUri, authCookie);  
            return cookieContainer;  
        }  
        public FormatType FormatType { get; set; }  
    }  
  
    public enum FormatType  
    {  
        JsonVerbose,  
        Xml  
    }  
} 

main.cs

Update the main.cs file.

#region Help:  Introduction to the Script Component  
/* The Script Component allows you to perform virtually any operation that can be accomplished in 
 * a .Net application within the context of an Integration Services data flow. 
 * 
 * Expand the other regions which have "Help" prefixes for examples of specific ways to use 
 * Integration Services features within this script component. */  
#endregion  
  
#region Namespaces  
using System;  
using System.Collections.Generic;  
using System.Data;  
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;  
using Microsoft.SqlServer.Dts.Runtime.Wrapper;  
using Newtonsoft.Json.Linq;  
using SC_89b6d39124744fa083fe63b6b3706317;  
#endregion  
  
/// <summary>  
/// This is the class to which to add your code.  Do not change the name, attributes, or parent  
/// of this class.  
/// </summary>  
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]  
public class ScriptMain : UserComponent  
{  
    #region Help:  Using Integration Services variables and parameters  
    /* To use a variable in this script, first ensure that the variable has been added to 
     * either the list contained in the ReadOnlyVariables property or the list contained in 
     * the ReadWriteVariables property of this script component, according to whether or not your 
     * code needs to write into the variable.  To do so, save this script, close this instance of 
     * Visual Studio, and update the ReadOnlyVariables and ReadWriteVariables properties in the 
     * Script Transformation Editor window. 
     * To use a parameter in this script, follow the same steps. Parameters are always read-only. 
     * 
     * Example of reading from a variable or parameter: 
     *  DateTime startTime = Variables.MyStartTime; 
     * 
     * Example of writing to a variable: 
     *  Variables.myStringVariable = "new value"; 
     */  
    #endregion  
  
    #region Help:  Using Integration Services Connnection Managers  
    /* Some types of connection managers can be used in this script component.  See the help topic 
     * "Working with Connection Managers Programatically" for details. 
     * 
     * To use a connection manager in this script, first ensure that the connection manager has 
     * been added to either the list of connection managers on the Connection Managers page of the 
     * script component editor.  To add the connection manager, save this script, close this instance of 
     * Visual Studio, and add the Connection Manager to the list. 
     * 
     * If the component needs to hold a connection open while processing rows, override the 
     * AcquireConnections and ReleaseConnections methods. 
     *  
     * Example of using an ADO.Net connection manager to acquire a SqlConnection: 
     *  object rawConnection = Connections.SalesDB.AcquireConnection(transaction); 
     *  SqlConnection salesDBConn = (SqlConnection)rawConnection; 
     * 
     * Example of using a File connection manager to acquire a file path: 
     *  object rawConnection = Connections.Prices_zip.AcquireConnection(transaction); 
     *  string filePath = (string)rawConnection; 
     * 
     * Example of releasing a connection manager: 
     *  Connections.SalesDB.ReleaseConnection(rawConnection); 
     */  
    #endregion  
  
    #region Help:  Firing Integration Services Events  
    /* This script component can fire events. 
     * 
     * Example of firing an error event: 
     *  ComponentMetaData.FireError(10, "Process Values", "Bad value", "", 0, out cancel); 
     * 
     * Example of firing an information event: 
     *  ComponentMetaData.FireInformation(10, "Process Values", "Processing has started", "", 0, fireAgain); 
     * 
     * Example of firing a warning event: 
     *  ComponentMetaData.FireWarning(10, "Process Values", "No rows were received", "", 0); 
     */  
    #endregion  
  
    /// <summary>  
    /// This method is called once, before rows begin to be processed in the data flow.  
    ///  
    /// You can remove this method if you don't need to do anything here.  
    /// </summary>  
    public override void PreExecute()  
    {  
        base.PreExecute();  
        /* 
         * Add your code here 
         */  
    }  
  
    /// <summary>  
    /// This method is called after all the rows have passed through this component.  
    ///  
    /// You can delete this method if you don't need to do anything here.  
    /// </summary>  
    public override void PostExecute()  
    {  
        base.PostExecute();  
        /* 
         * Add your code here 
         */  
    }  
  
    public override void CreateNewOutputRows()  
    {  
        /* 
          Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer". 
          For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput". 
        */  
        string siteURL = Variables.SiteUrl;  
        string userName = Variables.UserName;  
        string password = Variables.Pwd;  
        CallDataEmployee(siteURL, userName, password, "ID");  
        //CallDataArchieveEmployee(archiveURL, userName, password, "SourceID");  
  
        CallDataHobbies(siteURL, userName, password, "ID");  
        //CallDataArchieveHobbies(archiveURL, userName, password, "SourceID");  
    }  
  
    /// <summary>  
    /// This method is used to get the data from Employee list.  
    /// </summary>  
    /// <param name="siteURL"></param>  
    /// <param name="userName"></param>  
    /// <param name="password"></param>  
    /// <param name="IDCol"></param>  
    public void CallDataEmployee(string siteURL, string userName, string password, string IDCol)  
    {  
        var webUri = new Uri(siteURL);  
        using (var client = new SPHttpClient(webUri, userName, password))  
        {  
            try  
            {  
                var listTitle = "Employee";  
                string queryCall = string.Empty;  
                string callQuery = "ID&$top=1&$orderby=ID desc";  
                int chunkSize = 3999;  
                int initial = 0;  
                int final = 0;  
                var endpointUrlTopCall = string.Format("{0}/_api/web/lists/getbytitle('{1}')/items?$select=" + callQuery, webUri, listTitle);  
                var data = client.ExecuteJson(endpointUrlTopCall);  
                JToken item = data["d"]["results"][0];  
                int maxNumber = GetInt(item, "ID", -1);  
                //maxNumber = 99;  
                string colNames = IDCol + ",Title,FirstName,LastName,PhoneNo,Role,Address,IsActive,Hobbies/ID,Hobbies/Title,Author/Title,Editor/Title," +  
                    "Created,Modified,AuthorId,EditorId";  
                do  
                {  
                    initial = final + 1;  
                    final = initial + chunkSize - 1;  
                    queryCall = "&$top=" + chunkSize + "&$filter=ID ge " + initial + " and ID le " + final + "&$orderby=ID asc&$expand=Hobbies,Author,Editor";  
                    var endpointUrl = string.Format("{0}/_api/web/lists/getbytitle('{1}')/items?$select=" + colNames + queryCall, webUri, listTitle);  
                    var returndata = client.ExecuteJson(endpointUrl);  
                    try  
                    {  
                        CallDataEmployee(returndata, IDCol);  
                    }  
                    catch (Exception e)  
                    {  
  
                    }  
  
                } while (maxNumber > final);  
            }  
            catch (Exception e)  
            {  
  
            }  
        }  
    }  
  
    /// <summary>  
    /// This method is used to iterate the result and stored into Employee Buffer Output  
    /// </summary>  
    /// <param name="data"></param>  
    /// <param name="IDCol"></param>  
    public void CallDataEmployee(JToken data, string IDCol)  
    {  
        foreach (var item in data["d"]["results"])  
        {  
            try  
            {  
                EmployeeBuffer.AddRow();  
                EmployeeBuffer.ItemId = GetDecimal(item, IDCol, -1);  
                EmployeeBuffer.FullName = GetString(item, "Title");  
                EmployeeBuffer.FirstName = GetString(item, "FirstName");  
                EmployeeBuffer.LastName = GetString(item, "LastName");  
                EmployeeBuffer.PhoneNum = GetInt(item, "PhoneNo", -1);  
                EmployeeBuffer.Role = GetString(item, "Role");  
                EmployeeBuffer.Address = GetString(item, "Address");  
                EmployeeBuffer.IsActive = GetString(item, "IsActive");  
                EmployeeBuffer.Hobbies = GetMultipleComplex(item, "Hobbies", "Title", ",");  
                EmployeeBuffer.Created = GetDateTime(item, "Created");  
                EmployeeBuffer.Modified = GetDateTime(item, "Modified");  
                EmployeeBuffer.CreatedById = GetDecimal(item, "AuthorId", -1);  
                EmployeeBuffer.ModifiedById = GetDecimal(item, "EditorId", -1);  
                EmployeeBuffer.CreatedBy = GetComplex(item, "Author", "Title");  
                EmployeeBuffer.ModifiedBy = GetComplex(item, "Editor", "Title");  
            }  
            catch (Exception e1)  
            {  
  
            }  
        }  
  
    }  
  
    /// <summary>  
    /// This method is used to get data from Hobbies list.  
    /// </summary>  
    /// <param name="siteURL"></param>  
    /// <param name="userName"></param>  
    /// <param name="password"></param>  
    /// <param name="IDCol"></param>  
    public void CallDataHobbies(string siteURL, string userName, string password, string IDCol)  
    {  
        var webUri = new Uri(siteURL);  
        using (var client = new SPHttpClient(webUri, userName, password))  
        {  
            try  
            {  
                var listTitle = "Hobbies";  
                string queryCall = string.Empty;  
                string callQuery = "ID&$top=1&$orderby=ID desc";  
                int chunkSize = 3999;  
                int initial = 0;  
                int final = 0;  
                var endpointUrlTopCall = string.Format("{0}/_api/web/lists/getbytitle('{1}')/items?$select=" + callQuery, webUri, listTitle);  
                var data = client.ExecuteJson(endpointUrlTopCall);  
                JToken item = data["d"]["results"][0];  
                int maxNumber = GetInt(item, "ID", -1);  
                //maxNumber = 99;  
                string colNames = IDCol + ",Title,Author/Title,Editor/Title," +  
                    "Created,Modified,AuthorId,EditorId";  
                do  
                {  
                    initial = final + 1;  
                    final = initial + chunkSize - 1;  
                    queryCall = "&$top=" + chunkSize + "&$filter=ID ge " + initial + " and ID le " + final + "&$orderby=ID asc&$expand=Author,Editor";  
                    var endpointUrl = string.Format("{0}/_api/web/lists/getbytitle('{1}')/items?$select=" + colNames + queryCall, webUri, listTitle);  
                    var returndata = client.ExecuteJson(endpointUrl);  
                    try  
                    {  
                        CallDataHobbies(returndata, IDCol);  
                    }  
                    catch (Exception e)  
                    {  
  
                    }  
  
                } while (maxNumber > final);  
            }  
            catch (Exception e)  
            {  
  
            }  
        }  
    }  
  
    /// <summary>  
    /// This method is used to iterate the result and store into Hobbies buffer.  
    /// </summary>  
    /// <param name="data"></param>  
    /// <param name="IDCol"></param>  
    public void CallDataHobbies(JToken data, string IDCol)  
    {  
        foreach (var item in data["d"]["results"])  
        {  
            try  
            {  
                HobbiesBuffer.AddRow();  
                HobbiesBuffer.ItemId = GetDecimal(item, IDCol, -1);  
                HobbiesBuffer.Title = GetString(item, "Title");  
                HobbiesBuffer.Created = GetDateTime(item, "Created");  
                HobbiesBuffer.Modified = GetDateTime(item, "Modified");  
                HobbiesBuffer.CreatedById = GetDecimal(item, "AuthorId", -1);  
                HobbiesBuffer.ModifiedById = GetDecimal(item, "EditorId", -1);  
                HobbiesBuffer.CreatedBy = GetComplex(item, "Author", "Title");  
                HobbiesBuffer.ModifiedBy = GetComplex(item, "Editor", "Title");  
            }  
            catch (Exception e1)  
            {  
  
            }  
        }  
  
    }  
    /// <summary>  
    /// This method is used to Get the string from item.  
    /// </summary>  
    /// <param name="token"></param>  
    /// <param name="key"></param>  
    /// <returns></returns>  
    public string GetString(JToken token, string key)  
    {  
        string value = string.Empty;  
  
        try  
        {  
            value = Convert.ToString(token[key]);  
        }  
        catch (Exception e)  
        {  
  
        }  
  
        return value;  
    }  
    /// <summary>  
    ///   
    /// </summary>  
    /// <param name="token"></param>  
    /// <param name="key"></param>  
    /// <param name="var"></param>  
    /// <returns></returns>  
    public string GetComplex(JToken token, string key, string var)  
    {  
        string value = string.Empty;  
  
        try  
        {  
            JToken tok = token[key];  
            value = GetString(tok, var);  
        }  
        catch (Exception e)  
        {  
  
        }  
  
        return value;  
    }  
  
    public Decimal GetComplexID(JToken token, string key, string var)  
    {  
        Decimal dec = -1;  
  
        try  
        {  
            JToken tok = token[key];  
            dec = GetDecimal(tok, var, -1);  
        }  
        catch (Exception e)  
        {  
  
        }  
  
        return dec;  
    }  
    /// <summary>  
    /// This method is used to get the decimal from item.  
    /// </summary>  
    /// <param name="token"></param>  
    /// <param name="key"></param>  
    /// <param name="defaultVal"></param>  
    /// <returns></returns>  
    public Decimal GetDecimal(JToken token, string key, Decimal defaultVal)  
    {  
        Decimal dec = -1;  
  
        bool complete = Decimal.TryParse(GetString(token, key), out dec);  
  
        if (complete)  
        {  
            return dec;  
        }  
        else  
        {  
            return defaultVal;  
        }  
    }  
    /// <summary>  
    /// This method is used to return int from item.  
    /// </summary>  
    /// <param name="token"></param>  
    /// <param name="key"></param>  
    /// <param name="defaultVal"></param>  
    /// <returns></returns>  
    public Int32 GetInt(JToken token, string key, Int32 defaultVal)  
    {  
        Int32 dec = -1;  
  
        bool complete = Int32.TryParse(GetString(token, key), out dec);  
  
        if (complete)  
        {  
            return dec;  
        }  
        else  
        {  
            return defaultVal;  
        }  
    }  
    /// <summary>  
    /// This method is used to return the datetime from item.  
    /// </summary>  
    /// <param name="token"></param>  
    /// <param name="key"></param>  
    /// <returns></returns>  
    public DateTime GetDateTime(JToken token, string key)  
    {  
        DateTime dec;  
        bool complete = DateTime.TryParse(GetString(token, key), out dec);  
        return dec.ToLocalTime();  
    }  
    /// <summary>  
    /// This method is used to return the multiple string value from item.  
    /// </summary>  
    /// <param name="token"></param>  
    /// <param name="key"></param>  
    /// <param name="objectKey"></param>  
    /// <param name="seperater"></param>  
    /// <returns></returns>  
    public string GetMultipleComplex(JToken token, string key, string objectKey, string seperater)  
    {  
        string tempString = string.Empty;  
        List<string> strList = new List<string>();  
        try  
        {  
            JToken stringTokens = token[key]["results"];  
  
            foreach (var user in stringTokens)  
            {  
                string tempStr = GetString(user, objectKey);  
                strList.Add(tempStr);  
            }  
            if (strList.Count > 0)  
            {  
                tempString = string.Join(seperater, strList);  
            }  
        }  
        catch (Exception ex)  
        {  
  
        }  
  
        return tempString;  
    }  
}

No need to change the BufferWrapper.cs and ComponentWrapper.cs classes.

Save the changes by clicking on Ok button in script section of script component.

OLE DB Destination

Configure the OLE DB Destination for Employee parts i.e OLE DB Destination

Each OLE DB Destination has three parts i.e. Connection Managers, Mappings and Error Output.

Connection Managers

 Mappings

Configure the OLE DB Destination for Hobbies parts i.e OLE DB Destination 1.

Each OLE DB Destination has three parts i.e. Connection Managers, Mappings and Error Output.

Connection Managers

 Mappings

 If data flow task is configured properly for full package it will look like

Now we are going to configure the last component of control flow task for EmployeeFullPackage.dtsx

Execute SQL Task1

In this task we insert records in main table (i.e Employee and Hobbies) from stage table (i.e. Employee_Stage and Hobbies_Stage) by calling stored procedures(usp_MergeEmployee & usp_MergeHobbies).

Open the task, rename the task as Merge Table Employee Hobbies and configure in this way.

Open SQL Statment and paste this command.

EXEC [dbo].[usp_MergeEmployee]  
Go  
  
EXEC [dbo].[usp_MergeHobbies]  
Go

Run and validate the EmployeeFullPackage

Simply click on start icon present in visual studio menu.

If package runs without any errors then it will look like

 

Now check the Employee_Stage and Employee table in database SP_POC both have identical data. In the same way Hobbies_Stage and Hobbies table have identical data.

Records in Employee_Stage table

Records in Employee table

 

Records in Hobbies_Stage table

 

Records in Hobbies table 

Debugging the package

• Open the script component -> click on Edit Script.
• Add the debbuger in the code and click on ok button which exists in previous window.
• Click on Start icon present in visual studion menu. 

EmployeeIncrementalPackage

This package will run on daily basis after every 15 mins (although you can configure it diffently).

Functionality

• In the first step, it will truncate only stage table; i.e Employee_Stage and Hobbies_Stage.
• In the second step, it will copy items which are modified or added within 15 min in Employee and Hobbies list and stored into Employee and Hobbies buffer.
• In the third step, it will add all items from Employee buffer to Employee_Stage table and from Hobbies buffer to Hobbies_Stage table.
• In the last step, it will call the stored procedures usp_MergeEmployee and usp_MergeHobbies (created in previous article) to copy records from stage to main table.

Right click on SSIS package -> New SSIS package and name it as EmployeIncrementalPackage.dtsx

Control Flow

Open the EmployeeIncrementalPackage.dstx and drag the relevent component from SSIS Toolbox to control flow.

The control flow task consists of three main tasks; i.e Execute SQL Task, Data Flow Task and Execute SQL Task 1.

Create the same control flow for incremental package. 

Execute SQL Task

It is used to execute the SQL statement.

In this task, we will truncate our stage tables i.e. Employee_Stage and Hobbies_Stage.

Open the task, rename the task as Truncate Table Employee Hobbies and configure similar to employeefullpackage except the SQL Statement.

Open the SQL Statement and write the below command.

TRUNCATE TABLE dbo.[Employee_Stage]  
Go  
  
TRUNCATE TABLE dbo.[Hobbies_Stage]  
Go

Data Flow Task

In this task, we will fetch those records which are created or updated within 15 mins in Employee and Hobbies list and add into Employee_Stage and Hobbies_Stage table.

Click on Data flow task and select the relevant component from SSIS toolbox by dragging and dropping to data flow similar to employeefullpackage.

Script Component

Basically, the script component consist of three sub tasks; i.e Script, Inputs and Outputs, Connection Managers.

Script

For incremental package we will create two more variables for passing the date and time.

Click on Script Component, from SSIS menu select variables and create the following variables.

 Paste this expression for Load Created variables

 DATEADD("minute", - @[User::LoadMinutes] , GETDATE() )

In this task, we will add the project variables as well as newly created variable (LoadCreated) under Custom Properties - > ReadOnlyVariables.

Inputs and Outputs

Create similar ouputs (Employee and Hobbies) and thier columns like EmployeeFullPackage. 

Connection Managers

Create similar connections like EmployeeFullPackage.

Add the link from Script component to OLE DB Destination and OLE DB Destination1 for incremental package.

Now, we will write code to fetch the item from list and insert into table.

Go back to the script section and click on Edit Script button.

Add the same .dll files in references, as we have done for employeefullpackage

Create the similar helper class as we have created for employeefullpackage.

In incremental package script code, there is a change in only one file; i.e. main.cs.

main.cs

#region Help:  Introduction to the Script Component  
/* The Script Component allows you to perform virtually any operation that can be accomplished in 
 * a .Net application within the context of an Integration Services data flow. 
 * 
 * Expand the other regions which have "Help" prefixes for examples of specific ways to use 
 * Integration Services features within this script component. */  
#endregion  
  
#region Namespaces  
using System;  
using System.Collections.Generic;  
using System.Data;  
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;  
using Microsoft.SqlServer.Dts.Runtime.Wrapper;  
using Newtonsoft.Json.Linq;  
using SC_f0b9a68df8ce499a8004dd3218dae9c0;  
#endregion  
  
/// <summary>  
/// This is the class to which to add your code.  Do not change the name, attributes, or parent  
/// of this class.  
/// </summary>  
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]  
public class ScriptMain : UserComponent  
{  
    #region Help:  Using Integration Services variables and parameters  
    /* To use a variable in this script, first ensure that the variable has been added to 
     * either the list contained in the ReadOnlyVariables property or the list contained in 
     * the ReadWriteVariables property of this script component, according to whether or not your 
     * code needs to write into the variable.  To do so, save this script, close this instance of 
     * Visual Studio, and update the ReadOnlyVariables and ReadWriteVariables properties in the 
     * Script Transformation Editor window. 
     * To use a parameter in this script, follow the same steps. Parameters are always read-only. 
     * 
     * Example of reading from a variable or parameter: 
     *  DateTime startTime = Variables.MyStartTime; 
     * 
     * Example of writing to a variable: 
     *  Variables.myStringVariable = "new value"; 
     */  
    #endregion  
  
    #region Help:  Using Integration Services Connnection Managers  
    /* Some types of connection managers can be used in this script component.  See the help topic 
     * "Working with Connection Managers Programatically" for details. 
     * 
     * To use a connection manager in this script, first ensure that the connection manager has 
     * been added to either the list of connection managers on the Connection Managers page of the 
     * script component editor.  To add the connection manager, save this script, close this instance of 
     * Visual Studio, and add the Connection Manager to the list. 
     * 
     * If the component needs to hold a connection open while processing rows, override the 
     * AcquireConnections and ReleaseConnections methods. 
     *  
     * Example of using an ADO.Net connection manager to acquire a SqlConnection: 
     *  object rawConnection = Connections.SalesDB.AcquireConnection(transaction); 
     *  SqlConnection salesDBConn = (SqlConnection)rawConnection; 
     * 
     * Example of using a File connection manager to acquire a file path: 
     *  object rawConnection = Connections.Prices_zip.AcquireConnection(transaction); 
     *  string filePath = (string)rawConnection; 
     * 
     * Example of releasing a connection manager: 
     *  Connections.SalesDB.ReleaseConnection(rawConnection); 
     */  
    #endregion  
  
    #region Help:  Firing Integration Services Events  
    /* This script component can fire events. 
     * 
     * Example of firing an error event: 
     *  ComponentMetaData.FireError(10, "Process Values", "Bad value", "", 0, out cancel); 
     * 
     * Example of firing an information event: 
     *  ComponentMetaData.FireInformation(10, "Process Values", "Processing has started", "", 0, fireAgain); 
     * 
     * Example of firing a warning event: 
     *  ComponentMetaData.FireWarning(10, "Process Values", "No rows were received", "", 0); 
     */  
    #endregion  
  
    /// <summary>  
    /// This method is called once, before rows begin to be processed in the data flow.  
    ///  
    /// You can remove this method if you don't need to do anything here.  
    /// </summary>  
    public override void PreExecute()  
    {  
        base.PreExecute();  
        /* 
         * Add your code here 
         */  
    }  
  
    /// <summary>  
    /// This method is called after all the rows have passed through this component.  
    ///  
    /// You can delete this method if you don't need to do anything here.  
    /// </summary>  
    public override void PostExecute()  
    {  
        base.PostExecute();  
        /* 
         * Add your code here 
         */  
    }  
  
    public override void CreateNewOutputRows()  
    {  
        /* 
          Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer". 
          For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput". 
        */  
  
        string siteURL = Variables.SiteUrl;  
        string userName = Variables.UserName;  
        string password = Variables.Pwd;  
        DateTime date = Variables.LoadCreated;  
        string lastModified = date.ToUniversalTime().ToString("yyyy-MM-ddTHH:mm:ssZ");  
  
        CallDataEmployee(siteURL, userName, password, "ID", lastModified);  
        //CallDataEmployee(archiveURL, userName, password, "SourceID", obj, true, lastModified);  
  
        CallDataHobbies(siteURL, userName, password, "ID", lastModified);  
        //CallDataHobbies(archiveURL, userName, password, "SourceID", lastModified);  
    }  
    /// <summary>  
    /// This method is used to get data from employee list for last 15 min.  
    /// </summary>  
    /// <param name="siteURL"></param>  
    /// <param name="userName"></param>  
    /// <param name="password"></param>  
    /// <param name="IDCol"></param>  
    /// <param name="lastModified"></param>  
    public void CallDataEmployee(string siteURL, string userName, string password, string IDCol, string lastModified)  
    {  
        var webUri = new Uri(siteURL);  
        using (var client = new SPHttpClient(webUri, userName, password))  
        {  
            try  
            {  
                var listTitle = "Employee";  
                string colNames = IDCol + ",Title,FirstName,LastName,PhoneNo,Address,Role,IsActive,Hobbies/ID,Hobbies/Title,Created,Modified,AuthorId,EditorId,Author/Title,Editor/Title";  
                string filter = "&$top=4000&$filter=Modified ge datetime'" + lastModified + "'&$expand=Hobbies,Author,Editor";  
  
                var endpointUrl = string.Format("{0}/_api/web/lists/getbytitle('{1}')/items?$select=" + colNames + filter, webUri, listTitle);  
                var returndata = client.ExecuteJson(endpointUrl);  
                try  
                {  
                    CallDataEmployee(returndata, IDCol);  
                }  
                catch (Exception e)  
                {  
  
                }  
            }  
            catch (Exception e)  
            {  
  
            }  
        }  
    }  
    /// <summary>  
    /// This method is used to iterate the result and store into employeebuffer.  
    /// </summary>  
    /// <param name="data"></param>  
    /// <param name="IDCol"></param>  
    public void CallDataEmployee(JToken data, string IDCol)  
    {  
        foreach (var item in data["d"]["results"])  
        {  
            try  
            {  
                EmployeeBuffer.AddRow();  
                EmployeeBuffer.ItemId = GetDecimal(item, IDCol, -1);  
                EmployeeBuffer.FullName = GetString(item, "Title");  
                EmployeeBuffer.FirstName = GetString(item, "FirstName");  
                EmployeeBuffer.LastName = GetString(item, "LastName");  
                EmployeeBuffer.PhoneNum = GetInt(item, "PhoneNo", -1);  
                EmployeeBuffer.Address = GetString(item, "Address");  
                EmployeeBuffer.Role = GetString(item, "Role");  
                EmployeeBuffer.IsActive = GetString(item, "IsActive");  
                EmployeeBuffer.Hobbies = GetMultipleComplex(item, "Hobbies", "Title", ",");  
                EmployeeBuffer.Modified = GetDateTime(item, "Modified");  
                EmployeeBuffer.Created = GetDateTime(item, "Created");  
                EmployeeBuffer.CreatedById = GetDecimal(item, "AuthorId", -1);  
                EmployeeBuffer.ModifiedById = GetDecimal(item, "EditorId", -1);  
                EmployeeBuffer.CreatedBy = GetComplex(item, "Author", "Title");  
                EmployeeBuffer.ModifiedBy = GetComplex(item, "Editor", "Title");  
            }  
            catch (Exception e1)  
            {  
  
            }  
        }  
  
    }  
    /// <summary>  
    /// This method is used to get data from Hobbies list for last 15 min.  
    /// </summary>  
    /// <param name="siteURL"></param>  
    /// <param name="userName"></param>  
    /// <param name="password"></param>  
    /// <param name="IDCol"></param>  
    /// <param name="lastModified"></param>  
    public void CallDataHobbies(string siteURL, string userName, string password, string IDCol, string lastModified)  
    {  
        var webUri = new Uri(siteURL);  
        using (var client = new SPHttpClient(webUri, userName, password))  
        {  
            try  
            {  
                var listTitle = "Hobbies";  
                string colNames = IDCol + ",Title,Created,Modified,AuthorId,EditorId,Author/Title,Editor/Title";  
                string filter = "&$top=4000&$filter=Modified ge datetime'" + lastModified + "'&$expand=Author,Editor";  
  
                var endpointUrl = string.Format("{0}/_api/web/lists/getbytitle('{1}')/items?$select=" + colNames + filter, webUri, listTitle);  
                var returndata = client.ExecuteJson(endpointUrl);  
                try  
                {  
                    CallDataHobbies(returndata, IDCol);  
                }  
                catch (Exception e)  
                {  
  
                }  
            }  
            catch (Exception e)  
            {  
  
            }  
        }  
    }  
  
    /// <summary>  
    /// This method is used to iterate the result and store into Hobbies buffer.  
    /// </summary>  
    /// <param name="data"></param>  
    /// <param name="IDCol"></param>  
    public void CallDataHobbies(JToken data, string IDCol)  
    {  
        foreach (var item in data["d"]["results"])  
        {  
            try  
            {  
                HobbiesBuffer.AddRow();  
                HobbiesBuffer.ItemId = GetDecimal(item, IDCol, -1); 
                HobbiesBuffer.Title = GetString(item, "Title"); 
                HobbiesBuffer.Modified = GetDateTime(item, "Modified");  
                HobbiesBuffer.Created = GetDateTime(item, "Created");  
                HobbiesBuffer.CreatedById = GetDecimal(item, "AuthorId", -1);  
                HobbiesBuffer.ModifiedById = GetDecimal(item, "EditorId", -1);  
                HobbiesBuffer.CreatedBy = GetComplex(item, "Author", "Title");  
                HobbiesBuffer.ModifiedBy = GetComplex(item, "Editor", "Title");  
            }  
            catch (Exception e1)  
            {  
  
            }  
        }  
  
    }  
    public string GetString(JToken token, string key)  
    {  
        string value = string.Empty;  
  
        try  
        {  
            value = Convert.ToString(token[key]);  
        }  
        catch (Exception e)  
        {  
  
        }  
  
        return value;  
    }  
  
    public string GetComplex(JToken token, string key, string var)  
    {  
        string value = string.Empty;  
  
        try  
        {  
            JToken tok = token[key];  
            value = GetString(tok, var);  
        }  
        catch (Exception e)  
        {  
  
        }  
  
        return value;  
    }  
  
    public Decimal GetComplexID(JToken token, string key, string var)  
    {  
        Decimal dec = -1;  
  
        try  
        {  
            JToken tok = token[key];  
            dec = GetDecimal(tok, var, -1);  
        }  
        catch (Exception e)  
        {  
  
        }  
  
        return dec;  
    }  
  
    public Decimal GetDecimal(JToken token, string key, Decimal defaultVal)  
    {  
        Decimal dec = -1;  
  
        bool complete = Decimal.TryParse(GetString(token, key), out dec);  
  
        if (complete)  
        {  
            return dec;  
        }  
        else  
        {  
            return defaultVal;  
        }  
    }  
  
    public Int32 GetInt(JToken token, string key, Int32 defaultVal)  
    {  
        Int32 dec = -1;  
  
        bool complete = Int32.TryParse(GetString(token, key), out dec);  
  
        if (complete)  
        {  
            return dec;  
        }  
        else  
        {  
            return defaultVal;  
        }  
    }  
  
    public DateTime GetDateTime(JToken token, string key)  
    {  
        DateTime dec;  
        bool complete = DateTime.TryParse(GetString(token, key), out dec);  
        return dec.ToLocalTime();  
    }  
  
    public string GetMultipleComplex(JToken token, string key, string objectKey, string seperater)  
    {  
        string tempString = string.Empty;  
        List<string> strList = new List<string>();  
        try  
        {  
            JToken stringTokens = token[key]["results"];  
  
            foreach (var user in stringTokens)  
            {  
                string tempStr = GetString(user, objectKey);  
                strList.Add(tempStr);  
            }  
            if (strList.Count > 0)  
            {  
                tempString = string.Join(seperater, strList);  
            }  
        }  
        catch (Exception ex)  
        {  
  
        }  
  
        return tempString;  
    }  
  
}

No changes are required  in BufferWrapper.cs and ComponentWrapper.cs classes for incremental package.

Save the changes by clicking on Ok button present in previous visual studio window.

OLE DB Destination

As we know, each OLE DB Destination has three parts; i.e. Connection Managers, Mappings and Error Output.

Configure the Connection Managers and Mappings for employee and hobbies in incremental package, similar to employeefullpackage.

If data flow task is configured properly for incremental package it will look like:

Now we are going to configure the last component of control flow task for EmployeeIncrementalPackage.dtsx

Execute SQL Task1

In this task we insert or update records in main table (i.e Employee and Hobbies) from stage table (i.e. Employee_Stage and Hobbies_Stage) by calling stored procedures (usp_MergeEmployee & usp_MergeHobbies).

If item already exists in main table then store procedure will update the item, else insert the item in main table.  

Open the task, rename the task as Merge Table Employee Hobbies and configure in this way.

 

 Open SQL Statement and paste the below command in it.

 

Run and validate the EmployeeIncrementalPackage

To test and verify please add one item in employee list and update Hobbies list like this.

Now, simply click on start icon present in Visual Studio menu.

If package runs without any errors then it will look like:

Now check the Employee_Stage and Employee table in database SP_POC where Employee_Stage has only new item and Employee has all the items.

In the same way Hobbies_Stage has updated item and Hobbies has all the items.

Records in Employee_Stage table

Records in Employee table

 

Records in Hobbies_Stage table

Records in Hobbies table

Conclusion

We have seen how to create, debug, run and see the result in database table for full and incremental package. 

Hope you have enjoyed this article. Please check the next part of article.

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