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.
  1. TRUNCATE TABLE dbo.[Employee_Stage]  
  2. Go  

  3. TRUNCATE TABLE dbo.[Employee]  
  4. Go  
  5.   
  6. TRUNCATE TABLE dbo.[Hobbies_Stage]  
  7. Go  

  8. TRUNCATE TABLE dbo.[Hobbies]  
  9. 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
  1. using Newtonsoft.Json;  
  2. using Newtonsoft.Json.Linq;  
  3. using System;  
  4. using System.Collections.Generic;  
  5. using System.Linq;  
  6. using System.Net.Http;  
  7. using System.Net.Http.Headers;  
  8. using System.Text;  
  9. using System.Threading.Tasks;  
  10.   
  11. namespace SC_89b6d39124744fa083fe63b6b3706317  
  12. {  
  13.   
  14.     public class SPHttpClient : HttpClient  
  15.     {  
  16.         public SPHttpClient(Uri webUri, string userName, string password) : base(new SPHttpClientHandler(webUri, userName, password))  
  17.         {  
  18.             BaseAddress = webUri;  
  19.         }  
  20.         /// <summary>  
  21.         /// Execure request method  
  22.         /// </summary>  
  23.         /// <param name="requestUri"></param>  
  24.         /// <param name="method"></param>  
  25.         /// <param name="headers"></param>  
  26.         /// <param name="payload"></param>  
  27.         /// <returns></returns>  
  28.         public JObject ExecuteJson<T>(string requestUri, HttpMethod method, IDictionary<string, string> headers, T payload)  
  29.         {  
  30.             HttpResponseMessage response;  
  31.             switch (method.Method)  
  32.             {  
  33.                 case "POST":  
  34.                     var requestContent = new StringContent(JsonConvert.SerializeObject(payload));  
  35.                     requestContent.Headers.ContentType = MediaTypeHeaderValue.Parse("application/json;odata=verbose");  
  36.                     DefaultRequestHeaders.Add("X-RequestDigest", RequestFormDigest());  
  37.                     if (headers != null)  
  38.                     {  
  39.                         foreach (var header in headers)  
  40.                         {  
  41.                             DefaultRequestHeaders.Add(header.Key, header.Value);  
  42.                         }  
  43.                     }  
  44.                     response = PostAsync(requestUri, requestContent).Result;  
  45.                     break;  
  46.                 case "GET":  
  47.                     response = GetAsync(requestUri).Result;  
  48.                     break;  
  49.                 default:  
  50.                     throw new NotSupportedException(string.Format("Method {0} is not supported", method.Method));  
  51.             }  
  52.   
  53.             response.EnsureSuccessStatusCode();  
  54.             var responseContent = response.Content.ReadAsStringAsync().Result;  
  55.             return String.IsNullOrEmpty(responseContent) ? new JObject() : JObject.Parse(responseContent);  
  56.         }  
  57.   
  58.           
  59.         public JObject ExecuteJson<T>(string requestUri, HttpMethod method, T payload)  
  60.         {  
  61.             return ExecuteJson(requestUri, method, null, payload);  
  62.         }  
  63.   
  64.         public JObject ExecuteJson(string requestUri)  
  65.         {  
  66.             return ExecuteJson(requestUri, HttpMethod.Get, null, default(string));  
  67.         }  
  68.   
  69.   
  70.         /// <summary>  
  71.         /// Request Form Digest  
  72.         /// </summary>  
  73.         /// <returns></returns>  
  74.         public string RequestFormDigest()  
  75.         {  
  76.             var endpointUrl = string.Format("{0}/_api/contextinfo", BaseAddress);  
  77.             var result = this.PostAsync(endpointUrl, new StringContent(string.Empty)).Result;  
  78.             result.EnsureSuccessStatusCode();  
  79.             var content = result.Content.ReadAsStringAsync().Result;  
  80.             var contentJson = JObject.Parse(content);  
  81.             return contentJson["d"]["GetContextWebInformation"]["FormDigestValue"].ToString();  
  82.         }  
  83.     }  
  84. }  
SPHttpClientHandler.cs
  1. using Microsoft.SharePoint.Client;  
  2. using System;  
  3. using System.Collections.Generic;  
  4. using System.Linq;  
  5. using System.Net;  
  6. using System.Net.Http;  
  7. using System.Security;  
  8. using System.Text;  
  9. using System.Threading;  
  10. using System.Threading.Tasks;  
  11.   
  12. namespace SC_89b6d39124744fa083fe63b6b3706317  
  13. {  
  14.     public class SPHttpClientHandler : HttpClientHandler  
  15.     {  
  16.         public SPHttpClientHandler(Uri webUri, string userName, string password)  
  17.         {  
  18.             CookieContainer = GetAuthCookies(webUri, userName, password);  
  19.             FormatType = FormatType.JsonVerbose;  
  20.         }  
  21.   
  22.   
  23.         protected override Task<HttpResponseMessage> SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)  
  24.         {  
  25.             request.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED""f");  
  26.             if (FormatType == FormatType.JsonVerbose)  
  27.             {  
  28.                 //request.Headers.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json;odata=verbose"));  
  29.                 request.Headers.Add("Accept""application/json;odata=verbose");  
  30.             }  
  31.             return base.SendAsync(request, cancellationToken);  
  32.         }  
  33.   
  34.   
  35.         /// <summary>  
  36.         /// Retrieve SPO Auth Cookies   
  37.         /// </summary>  
  38.         /// <param name="webUri"></param>  
  39.         /// <param name="userName"></param>  
  40.         /// <param name="password"></param>  
  41.         /// <returns></returns>  
  42.         private static CookieContainer GetAuthCookies(Uri webUri, string userName, string password)  
  43.         {  
  44.             var securePassword = new SecureString();  
  45.             foreach (var c in password) { securePassword.AppendChar(c); }  
  46.             var credentials = new SharePointOnlineCredentials(userName, securePassword);  
  47.             var authCookie = credentials.GetAuthenticationCookie(webUri);  
  48.             var cookieContainer = new CookieContainer();  
  49.             cookieContainer.SetCookies(webUri, authCookie);  
  50.             return cookieContainer;  
  51.         }  
  52.         public FormatType FormatType { get; set; }  
  53.     }  
  54.   
  55.     public enum FormatType  
  56.     {  
  57.         JsonVerbose,  
  58.         Xml  
  59.     }  
  60. }  
main.cs
 
Update the main.cs file.
  1. #region Help:  Introduction to the Script Component  
  2. /* The Script Component allows you to perform virtually any operation that can be accomplished in 
  3.  * a .Net application within the context of an Integration Services data flow. 
  4.  * 
  5.  * Expand the other regions which have "Help" prefixes for examples of specific ways to use 
  6.  * Integration Services features within this script component. */  
  7. #endregion  
  8.   
  9. #region Namespaces  
  10. using System;  
  11. using System.Collections.Generic;  
  12. using System.Data;  
  13. using Microsoft.SqlServer.Dts.Pipeline.Wrapper;  
  14. using Microsoft.SqlServer.Dts.Runtime.Wrapper;  
  15. using Newtonsoft.Json.Linq;  
  16. using SC_89b6d39124744fa083fe63b6b3706317;  
  17. #endregion  
  18.   
  19. /// <summary>  
  20. /// This is the class to which to add your code.  Do not change the name, attributes, or parent  
  21. /// of this class.  
  22. /// </summary>  
  23. [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]  
  24. public class ScriptMain : UserComponent  
  25. {  
  26.     #region Help:  Using Integration Services variables and parameters  
  27.     /* To use a variable in this script, first ensure that the variable has been added to 
  28.      * either the list contained in the ReadOnlyVariables property or the list contained in 
  29.      * the ReadWriteVariables property of this script component, according to whether or not your 
  30.      * code needs to write into the variable.  To do so, save this script, close this instance of 
  31.      * Visual Studio, and update the ReadOnlyVariables and ReadWriteVariables properties in the 
  32.      * Script Transformation Editor window. 
  33.      * To use a parameter in this script, follow the same steps. Parameters are always read-only. 
  34.      * 
  35.      * Example of reading from a variable or parameter: 
  36.      *  DateTime startTime = Variables.MyStartTime; 
  37.      * 
  38.      * Example of writing to a variable: 
  39.      *  Variables.myStringVariable = "new value"; 
  40.      */  
  41.     #endregion  
  42.   
  43.     #region Help:  Using Integration Services Connnection Managers  
  44.     /* Some types of connection managers can be used in this script component.  See the help topic 
  45.      * "Working with Connection Managers Programatically" for details. 
  46.      * 
  47.      * To use a connection manager in this script, first ensure that the connection manager has 
  48.      * been added to either the list of connection managers on the Connection Managers page of the 
  49.      * script component editor.  To add the connection manager, save this script, close this instance of 
  50.      * Visual Studio, and add the Connection Manager to the list. 
  51.      * 
  52.      * If the component needs to hold a connection open while processing rows, override the 
  53.      * AcquireConnections and ReleaseConnections methods. 
  54.      *  
  55.      * Example of using an ADO.Net connection manager to acquire a SqlConnection: 
  56.      *  object rawConnection = Connections.SalesDB.AcquireConnection(transaction); 
  57.      *  SqlConnection salesDBConn = (SqlConnection)rawConnection; 
  58.      * 
  59.      * Example of using a File connection manager to acquire a file path: 
  60.      *  object rawConnection = Connections.Prices_zip.AcquireConnection(transaction); 
  61.      *  string filePath = (string)rawConnection; 
  62.      * 
  63.      * Example of releasing a connection manager: 
  64.      *  Connections.SalesDB.ReleaseConnection(rawConnection); 
  65.      */  
  66.     #endregion  
  67.   
  68.     #region Help:  Firing Integration Services Events  
  69.     /* This script component can fire events. 
  70.      * 
  71.      * Example of firing an error event: 
  72.      *  ComponentMetaData.FireError(10, "Process Values", "Bad value", "", 0, out cancel); 
  73.      * 
  74.      * Example of firing an information event: 
  75.      *  ComponentMetaData.FireInformation(10, "Process Values", "Processing has started", "", 0, fireAgain); 
  76.      * 
  77.      * Example of firing a warning event: 
  78.      *  ComponentMetaData.FireWarning(10, "Process Values", "No rows were received", "", 0); 
  79.      */  
  80.     #endregion  
  81.   
  82.     /// <summary>  
  83.     /// This method is called once, before rows begin to be processed in the data flow.  
  84.     ///  
  85.     /// You can remove this method if you don't need to do anything here.  
  86.     /// </summary>  
  87.     public override void PreExecute()  
  88.     {  
  89.         base.PreExecute();  
  90.         /* 
  91.          * Add your code here 
  92.          */  
  93.     }  
  94.   
  95.     /// <summary>  
  96.     /// This method is called after all the rows have passed through this component.  
  97.     ///  
  98.     /// You can delete this method if you don't need to do anything here.  
  99.     /// </summary>  
  100.     public override void PostExecute()  
  101.     {  
  102.         base.PostExecute();  
  103.         /* 
  104.          * Add your code here 
  105.          */  
  106.     }  
  107.   
  108.     public override void CreateNewOutputRows()  
  109.     {  
  110.         /* 
  111.           Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer". 
  112.           For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput". 
  113.         */  
  114.         string siteURL = Variables.SiteUrl;  
  115.         string userName = Variables.UserName;  
  116.         string password = Variables.Pwd;  
  117.         CallDataEmployee(siteURL, userName, password, "ID");  
  118.         //CallDataArchieveEmployee(archiveURL, userName, password, "SourceID");  
  119.   
  120.         CallDataHobbies(siteURL, userName, password, "ID");  
  121.         //CallDataArchieveHobbies(archiveURL, userName, password, "SourceID");  
  122.     }  
  123.   
  124.     /// <summary>  
  125.     /// This method is used to get the data from Employee list.  
  126.     /// </summary>  
  127.     /// <param name="siteURL"></param>  
  128.     /// <param name="userName"></param>  
  129.     /// <param name="password"></param>  
  130.     /// <param name="IDCol"></param>  
  131.     public void CallDataEmployee(string siteURL, string userName, string password, string IDCol)  
  132.     {  
  133.         var webUri = new Uri(siteURL);  
  134.         using (var client = new SPHttpClient(webUri, userName, password))  
  135.         {  
  136.             try  
  137.             {  
  138.                 var listTitle = "Employee";  
  139.                 string queryCall = string.Empty;  
  140.                 string callQuery = "ID&$top=1&$orderby=ID desc";  
  141.                 int chunkSize = 3999;  
  142.                 int initial = 0;  
  143.                 int final = 0;  
  144.                 var endpointUrlTopCall = string.Format("{0}/_api/web/lists/getbytitle('{1}')/items?$select=" + callQuery, webUri, listTitle);  
  145.                 var data = client.ExecuteJson(endpointUrlTopCall);  
  146.                 JToken item = data["d"]["results"][0];  
  147.                 int maxNumber = GetInt(item, "ID", -1);  
  148.                 //maxNumber = 99;  
  149.                 string colNames = IDCol + ",Title,FirstName,LastName,PhoneNo,Role,Address,IsActive,Hobbies/ID,Hobbies/Title,Author/Title,Editor/Title," +  
  150.                     "Created,Modified,AuthorId,EditorId";  
  151.                 do  
  152.                 {  
  153.                     initial = final + 1;  
  154.                     final = initial + chunkSize - 1;  
  155.                     queryCall = "&$top=" + chunkSize + "&$filter=ID ge " + initial + " and ID le " + final + "&$orderby=ID asc&$expand=Hobbies,Author,Editor";  
  156.                     var endpointUrl = string.Format("{0}/_api/web/lists/getbytitle('{1}')/items?$select=" + colNames + queryCall, webUri, listTitle);  
  157.                     var returndata = client.ExecuteJson(endpointUrl);  
  158.                     try  
  159.                     {  
  160.                         CallDataEmployee(returndata, IDCol);  
  161.                     }  
  162.                     catch (Exception e)  
  163.                     {  
  164.   
  165.                     }  
  166.   
  167.                 } while (maxNumber > final);  
  168.             }  
  169.             catch (Exception e)  
  170.             {  
  171.   
  172.             }  
  173.         }  
  174.     }  
  175.   
  176.     /// <summary>  
  177.     /// This method is used to iterate the result and stored into Employee Buffer Output  
  178.     /// </summary>  
  179.     /// <param name="data"></param>  
  180.     /// <param name="IDCol"></param>  
  181.     public void CallDataEmployee(JToken data, string IDCol)  
  182.     {  
  183.         foreach (var item in data["d"]["results"])  
  184.         {  
  185.             try  
  186.             {  
  187.                 EmployeeBuffer.AddRow();  
  188.                 EmployeeBuffer.ItemId = GetDecimal(item, IDCol, -1);  
  189.                 EmployeeBuffer.FullName = GetString(item, "Title");  
  190.                 EmployeeBuffer.FirstName = GetString(item, "FirstName");  
  191.                 EmployeeBuffer.LastName = GetString(item, "LastName");  
  192.                 EmployeeBuffer.PhoneNum = GetInt(item, "PhoneNo", -1);  
  193.                 EmployeeBuffer.Role = GetString(item, "Role");  
  194.                 EmployeeBuffer.Address = GetString(item, "Address");  
  195.                 EmployeeBuffer.IsActive = GetString(item, "IsActive");  
  196.                 EmployeeBuffer.Hobbies = GetMultipleComplex(item, "Hobbies""Title"",");  
  197.                 EmployeeBuffer.Created = GetDateTime(item, "Created");  
  198.                 EmployeeBuffer.Modified = GetDateTime(item, "Modified");  
  199.                 EmployeeBuffer.CreatedById = GetDecimal(item, "AuthorId", -1);  
  200.                 EmployeeBuffer.ModifiedById = GetDecimal(item, "EditorId", -1);  
  201.                 EmployeeBuffer.CreatedBy = GetComplex(item, "Author""Title");  
  202.                 EmployeeBuffer.ModifiedBy = GetComplex(item, "Editor""Title");  
  203.             }  
  204.             catch (Exception e1)  
  205.             {  
  206.   
  207.             }  
  208.         }  
  209.   
  210.     }  
  211.   
  212.     /// <summary>  
  213.     /// This method is used to get data from Hobbies list.  
  214.     /// </summary>  
  215.     /// <param name="siteURL"></param>  
  216.     /// <param name="userName"></param>  
  217.     /// <param name="password"></param>  
  218.     /// <param name="IDCol"></param>  
  219.     public void CallDataHobbies(string siteURL, string userName, string password, string IDCol)  
  220.     {  
  221.         var webUri = new Uri(siteURL);  
  222.         using (var client = new SPHttpClient(webUri, userName, password))  
  223.         {  
  224.             try  
  225.             {  
  226.                 var listTitle = "Hobbies";  
  227.                 string queryCall = string.Empty;  
  228.                 string callQuery = "ID&$top=1&$orderby=ID desc";  
  229.                 int chunkSize = 3999;  
  230.                 int initial = 0;  
  231.                 int final = 0;  
  232.                 var endpointUrlTopCall = string.Format("{0}/_api/web/lists/getbytitle('{1}')/items?$select=" + callQuery, webUri, listTitle);  
  233.                 var data = client.ExecuteJson(endpointUrlTopCall);  
  234.                 JToken item = data["d"]["results"][0];  
  235.                 int maxNumber = GetInt(item, "ID", -1);  
  236.                 //maxNumber = 99;  
  237.                 string colNames = IDCol + ",Title,Author/Title,Editor/Title," +  
  238.                     "Created,Modified,AuthorId,EditorId";  
  239.                 do  
  240.                 {  
  241.                     initial = final + 1;  
  242.                     final = initial + chunkSize - 1;  
  243.                     queryCall = "&$top=" + chunkSize + "&$filter=ID ge " + initial + " and ID le " + final + "&$orderby=ID asc&$expand=Author,Editor";  
  244.                     var endpointUrl = string.Format("{0}/_api/web/lists/getbytitle('{1}')/items?$select=" + colNames + queryCall, webUri, listTitle);  
  245.                     var returndata = client.ExecuteJson(endpointUrl);  
  246.                     try  
  247.                     {  
  248.                         CallDataHobbies(returndata, IDCol);  
  249.                     }  
  250.                     catch (Exception e)  
  251.                     {  
  252.   
  253.                     }  
  254.   
  255.                 } while (maxNumber > final);  
  256.             }  
  257.             catch (Exception e)  
  258.             {  
  259.   
  260.             }  
  261.         }  
  262.     }  
  263.   
  264.     /// <summary>  
  265.     /// This method is used to iterate the result and store into Hobbies buffer.  
  266.     /// </summary>  
  267.     /// <param name="data"></param>  
  268.     /// <param name="IDCol"></param>  
  269.     public void CallDataHobbies(JToken data, string IDCol)  
  270.     {  
  271.         foreach (var item in data["d"]["results"])  
  272.         {  
  273.             try  
  274.             {  
  275.                 HobbiesBuffer.AddRow();  
  276.                 HobbiesBuffer.ItemId = GetDecimal(item, IDCol, -1);  
  277.                 HobbiesBuffer.Title = GetString(item, "Title");  
  278.                 HobbiesBuffer.Created = GetDateTime(item, "Created");  
  279.                 HobbiesBuffer.Modified = GetDateTime(item, "Modified");  
  280.                 HobbiesBuffer.CreatedById = GetDecimal(item, "AuthorId", -1);  
  281.                 HobbiesBuffer.ModifiedById = GetDecimal(item, "EditorId", -1);  
  282.                 HobbiesBuffer.CreatedBy = GetComplex(item, "Author""Title");  
  283.                 HobbiesBuffer.ModifiedBy = GetComplex(item, "Editor""Title");  
  284.             }  
  285.             catch (Exception e1)  
  286.             {  
  287.   
  288.             }  
  289.         }  
  290.   
  291.     }  
  292.     /// <summary>  
  293.     /// This method is used to Get the string from item.  
  294.     /// </summary>  
  295.     /// <param name="token"></param>  
  296.     /// <param name="key"></param>  
  297.     /// <returns></returns>  
  298.     public string GetString(JToken token, string key)  
  299.     {  
  300.         string value = string.Empty;  
  301.   
  302.         try  
  303.         {  
  304.             value = Convert.ToString(token[key]);  
  305.         }  
  306.         catch (Exception e)  
  307.         {  
  308.   
  309.         }  
  310.   
  311.         return value;  
  312.     }  
  313.     /// <summary>  
  314.     ///   
  315.     /// </summary>  
  316.     /// <param name="token"></param>  
  317.     /// <param name="key"></param>  
  318.     /// <param name="var"></param>  
  319.     /// <returns></returns>  
  320.     public string GetComplex(JToken token, string key, string var)  
  321.     {  
  322.         string value = string.Empty;  
  323.   
  324.         try  
  325.         {  
  326.             JToken tok = token[key];  
  327.             value = GetString(tok, var);  
  328.         }  
  329.         catch (Exception e)  
  330.         {  
  331.   
  332.         }  
  333.   
  334.         return value;  
  335.     }  
  336.   
  337.     public Decimal GetComplexID(JToken token, string key, string var)  
  338.     {  
  339.         Decimal dec = -1;  
  340.   
  341.         try  
  342.         {  
  343.             JToken tok = token[key];  
  344.             dec = GetDecimal(tok, var, -1);  
  345.         }  
  346.         catch (Exception e)  
  347.         {  
  348.   
  349.         }  
  350.   
  351.         return dec;  
  352.     }  
  353.     /// <summary>  
  354.     /// This method is used to get the decimal from item.  
  355.     /// </summary>  
  356.     /// <param name="token"></param>  
  357.     /// <param name="key"></param>  
  358.     /// <param name="defaultVal"></param>  
  359.     /// <returns></returns>  
  360.     public Decimal GetDecimal(JToken token, string key, Decimal defaultVal)  
  361.     {  
  362.         Decimal dec = -1;  
  363.   
  364.         bool complete = Decimal.TryParse(GetString(token, key), out dec);  
  365.   
  366.         if (complete)  
  367.         {  
  368.             return dec;  
  369.         }  
  370.         else  
  371.         {  
  372.             return defaultVal;  
  373.         }  
  374.     }  
  375.     /// <summary>  
  376.     /// This method is used to return int from item.  
  377.     /// </summary>  
  378.     /// <param name="token"></param>  
  379.     /// <param name="key"></param>  
  380.     /// <param name="defaultVal"></param>  
  381.     /// <returns></returns>  
  382.     public Int32 GetInt(JToken token, string key, Int32 defaultVal)  
  383.     {  
  384.         Int32 dec = -1;  
  385.   
  386.         bool complete = Int32.TryParse(GetString(token, key), out dec);  
  387.   
  388.         if (complete)  
  389.         {  
  390.             return dec;  
  391.         }  
  392.         else  
  393.         {  
  394.             return defaultVal;  
  395.         }  
  396.     }  
  397.     /// <summary>  
  398.     /// This method is used to return the datetime from item.  
  399.     /// </summary>  
  400.     /// <param name="token"></param>  
  401.     /// <param name="key"></param>  
  402.     /// <returns></returns>  
  403.     public DateTime GetDateTime(JToken token, string key)  
  404.     {  
  405.         DateTime dec;  
  406.         bool complete = DateTime.TryParse(GetString(token, key), out dec);  
  407.         return dec.ToLocalTime();  
  408.     }  
  409.     /// <summary>  
  410.     /// This method is used to return the multiple string value from item.  
  411.     /// </summary>  
  412.     /// <param name="token"></param>  
  413.     /// <param name="key"></param>  
  414.     /// <param name="objectKey"></param>  
  415.     /// <param name="seperater"></param>  
  416.     /// <returns></returns>  
  417.     public string GetMultipleComplex(JToken token, string key, string objectKey, string seperater)  
  418.     {  
  419.         string tempString = string.Empty;  
  420.         List<string> strList = new List<string>();  
  421.         try  
  422.         {  
  423.             JToken stringTokens = token[key]["results"];  
  424.   
  425.             foreach (var user in stringTokens)  
  426.             {  
  427.                 string tempStr = GetString(user, objectKey);  
  428.                 strList.Add(tempStr);  
  429.             }  
  430.             if (strList.Count > 0)  
  431.             {  
  432.                 tempString = string.Join(seperater, strList);  
  433.             }  
  434.         }  
  435.         catch (Exception ex)  
  436.         {  
  437.   
  438.         }  
  439.   
  440.         return tempString;  
  441.     }  
  442. }  
 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.
  1. EXEC [dbo].[usp_MergeEmployee]  
  2. Go  
  3.   
  4. EXEC [dbo].[usp_MergeHobbies]  
  5. 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.
  1. TRUNCATE TABLE dbo.[Employee_Stage]  
  2. Go  
  3.   
  4. TRUNCATE TABLE dbo.[Hobbies_Stage]  
  5. 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 
  1. #region Help:  Introduction to the Script Component  
  2. /* The Script Component allows you to perform virtually any operation that can be accomplished in 
  3.  * a .Net application within the context of an Integration Services data flow. 
  4.  * 
  5.  * Expand the other regions which have "Help" prefixes for examples of specific ways to use 
  6.  * Integration Services features within this script component. */  
  7. #endregion  
  8.   
  9. #region Namespaces  
  10. using System;  
  11. using System.Collections.Generic;  
  12. using System.Data;  
  13. using Microsoft.SqlServer.Dts.Pipeline.Wrapper;  
  14. using Microsoft.SqlServer.Dts.Runtime.Wrapper;  
  15. using Newtonsoft.Json.Linq;  
  16. using SC_f0b9a68df8ce499a8004dd3218dae9c0;  
  17. #endregion  
  18.   
  19. /// <summary>  
  20. /// This is the class to which to add your code.  Do not change the name, attributes, or parent  
  21. /// of this class.  
  22. /// </summary>  
  23. [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]  
  24. public class ScriptMain : UserComponent  
  25. {  
  26.     #region Help:  Using Integration Services variables and parameters  
  27.     /* To use a variable in this script, first ensure that the variable has been added to 
  28.      * either the list contained in the ReadOnlyVariables property or the list contained in 
  29.      * the ReadWriteVariables property of this script component, according to whether or not your 
  30.      * code needs to write into the variable.  To do so, save this script, close this instance of 
  31.      * Visual Studio, and update the ReadOnlyVariables and ReadWriteVariables properties in the 
  32.      * Script Transformation Editor window. 
  33.      * To use a parameter in this script, follow the same steps. Parameters are always read-only. 
  34.      * 
  35.      * Example of reading from a variable or parameter: 
  36.      *  DateTime startTime = Variables.MyStartTime; 
  37.      * 
  38.      * Example of writing to a variable: 
  39.      *  Variables.myStringVariable = "new value"; 
  40.      */  
  41.     #endregion  
  42.   
  43.     #region Help:  Using Integration Services Connnection Managers  
  44.     /* Some types of connection managers can be used in this script component.  See the help topic 
  45.      * "Working with Connection Managers Programatically" for details. 
  46.      * 
  47.      * To use a connection manager in this script, first ensure that the connection manager has 
  48.      * been added to either the list of connection managers on the Connection Managers page of the 
  49.      * script component editor.  To add the connection manager, save this script, close this instance of 
  50.      * Visual Studio, and add the Connection Manager to the list. 
  51.      * 
  52.      * If the component needs to hold a connection open while processing rows, override the 
  53.      * AcquireConnections and ReleaseConnections methods. 
  54.      *  
  55.      * Example of using an ADO.Net connection manager to acquire a SqlConnection: 
  56.      *  object rawConnection = Connections.SalesDB.AcquireConnection(transaction); 
  57.      *  SqlConnection salesDBConn = (SqlConnection)rawConnection; 
  58.      * 
  59.      * Example of using a File connection manager to acquire a file path: 
  60.      *  object rawConnection = Connections.Prices_zip.AcquireConnection(transaction); 
  61.      *  string filePath = (string)rawConnection; 
  62.      * 
  63.      * Example of releasing a connection manager: 
  64.      *  Connections.SalesDB.ReleaseConnection(rawConnection); 
  65.      */  
  66.     #endregion  
  67.   
  68.     #region Help:  Firing Integration Services Events  
  69.     /* This script component can fire events. 
  70.      * 
  71.      * Example of firing an error event: 
  72.      *  ComponentMetaData.FireError(10, "Process Values", "Bad value", "", 0, out cancel); 
  73.      * 
  74.      * Example of firing an information event: 
  75.      *  ComponentMetaData.FireInformation(10, "Process Values", "Processing has started", "", 0, fireAgain); 
  76.      * 
  77.      * Example of firing a warning event: 
  78.      *  ComponentMetaData.FireWarning(10, "Process Values", "No rows were received", "", 0); 
  79.      */  
  80.     #endregion  
  81.   
  82.     /// <summary>  
  83.     /// This method is called once, before rows begin to be processed in the data flow.  
  84.     ///  
  85.     /// You can remove this method if you don't need to do anything here.  
  86.     /// </summary>  
  87.     public override void PreExecute()  
  88.     {  
  89.         base.PreExecute();  
  90.         /* 
  91.          * Add your code here 
  92.          */  
  93.     }  
  94.   
  95.     /// <summary>  
  96.     /// This method is called after all the rows have passed through this component.  
  97.     ///  
  98.     /// You can delete this method if you don't need to do anything here.  
  99.     /// </summary>  
  100.     public override void PostExecute()  
  101.     {  
  102.         base.PostExecute();  
  103.         /* 
  104.          * Add your code here 
  105.          */  
  106.     }  
  107.   
  108.     public override void CreateNewOutputRows()  
  109.     {  
  110.         /* 
  111.           Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer". 
  112.           For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput". 
  113.         */  
  114.   
  115.         string siteURL = Variables.SiteUrl;  
  116.         string userName = Variables.UserName;  
  117.         string password = Variables.Pwd;  
  118.         DateTime date = Variables.LoadCreated;  
  119.         string lastModified = date.ToUniversalTime().ToString("yyyy-MM-ddTHH:mm:ssZ");  
  120.   
  121.         CallDataEmployee(siteURL, userName, password, "ID", lastModified);  
  122.         //CallDataEmployee(archiveURL, userName, password, "SourceID", obj, true, lastModified);  
  123.   
  124.         CallDataHobbies(siteURL, userName, password, "ID", lastModified);  
  125.         //CallDataHobbies(archiveURL, userName, password, "SourceID", lastModified);  
  126.     }  
  127.     /// <summary>  
  128.     /// This method is used to get data from employee list for last 15 min.  
  129.     /// </summary>  
  130.     /// <param name="siteURL"></param>  
  131.     /// <param name="userName"></param>  
  132.     /// <param name="password"></param>  
  133.     /// <param name="IDCol"></param>  
  134.     /// <param name="lastModified"></param>  
  135.     public void CallDataEmployee(string siteURL, string userName, string password, string IDCol, string lastModified)  
  136.     {  
  137.         var webUri = new Uri(siteURL);  
  138.         using (var client = new SPHttpClient(webUri, userName, password))  
  139.         {  
  140.             try  
  141.             {  
  142.                 var listTitle = "Employee";  
  143.                 string colNames = IDCol + ",Title,FirstName,LastName,PhoneNo,Address,Role,IsActive,Hobbies/ID,Hobbies/Title,Created,Modified,AuthorId,EditorId,Author/Title,Editor/Title";  
  144.                 string filter = "&$top=4000&$filter=Modified ge datetime'" + lastModified + "'&$expand=Hobbies,Author,Editor";  
  145.   
  146.                 var endpointUrl = string.Format("{0}/_api/web/lists/getbytitle('{1}')/items?$select=" + colNames + filter, webUri, listTitle);  
  147.                 var returndata = client.ExecuteJson(endpointUrl);  
  148.                 try  
  149.                 {  
  150.                     CallDataEmployee(returndata, IDCol);  
  151.                 }  
  152.                 catch (Exception e)  
  153.                 {  
  154.   
  155.                 }  
  156.             }  
  157.             catch (Exception e)  
  158.             {  
  159.   
  160.             }  
  161.         }  
  162.     }  
  163.     /// <summary>  
  164.     /// This method is used to iterate the result and store into employeebuffer.  
  165.     /// </summary>  
  166.     /// <param name="data"></param>  
  167.     /// <param name="IDCol"></param>  
  168.     public void CallDataEmployee(JToken data, string IDCol)  
  169.     {  
  170.         foreach (var item in data["d"]["results"])  
  171.         {  
  172.             try  
  173.             {  
  174.                 EmployeeBuffer.AddRow();  
  175.                 EmployeeBuffer.ItemId = GetDecimal(item, IDCol, -1);  
  176.                 EmployeeBuffer.FullName = GetString(item, "Title");  
  177.                 EmployeeBuffer.FirstName = GetString(item, "FirstName");  
  178.                 EmployeeBuffer.LastName = GetString(item, "LastName");  
  179.                 EmployeeBuffer.PhoneNum = GetInt(item, "PhoneNo", -1);  
  180.                 EmployeeBuffer.Address = GetString(item, "Address");  
  181.                 EmployeeBuffer.Role = GetString(item, "Role");  
  182.                 EmployeeBuffer.IsActive = GetString(item, "IsActive");  
  183.                 EmployeeBuffer.Hobbies = GetMultipleComplex(item, "Hobbies""Title"",");  
  184.                 EmployeeBuffer.Modified = GetDateTime(item, "Modified");  
  185.                 EmployeeBuffer.Created = GetDateTime(item, "Created");  
  186.                 EmployeeBuffer.CreatedById = GetDecimal(item, "AuthorId", -1);  
  187.                 EmployeeBuffer.ModifiedById = GetDecimal(item, "EditorId", -1);  
  188.                 EmployeeBuffer.CreatedBy = GetComplex(item, "Author""Title");  
  189.                 EmployeeBuffer.ModifiedBy = GetComplex(item, "Editor""Title");  
  190.             }  
  191.             catch (Exception e1)  
  192.             {  
  193.   
  194.             }  
  195.         }  
  196.   
  197.     }  
  198.     /// <summary>  
  199.     /// This method is used to get data from Hobbies list for last 15 min.  
  200.     /// </summary>  
  201.     /// <param name="siteURL"></param>  
  202.     /// <param name="userName"></param>  
  203.     /// <param name="password"></param>  
  204.     /// <param name="IDCol"></param>  
  205.     /// <param name="lastModified"></param>  
  206.     public void CallDataHobbies(string siteURL, string userName, string password, string IDCol, string lastModified)  
  207.     {  
  208.         var webUri = new Uri(siteURL);  
  209.         using (var client = new SPHttpClient(webUri, userName, password))  
  210.         {  
  211.             try  
  212.             {  
  213.                 var listTitle = "Hobbies";  
  214.                 string colNames = IDCol + ",Title,Created,Modified,AuthorId,EditorId,Author/Title,Editor/Title";  
  215.                 string filter = "&$top=4000&$filter=Modified ge datetime'" + lastModified + "'&$expand=Author,Editor";  
  216.   
  217.                 var endpointUrl = string.Format("{0}/_api/web/lists/getbytitle('{1}')/items?$select=" + colNames + filter, webUri, listTitle);  
  218.                 var returndata = client.ExecuteJson(endpointUrl);  
  219.                 try  
  220.                 {  
  221.                     CallDataHobbies(returndata, IDCol);  
  222.                 }  
  223.                 catch (Exception e)  
  224.                 {  
  225.   
  226.                 }  
  227.             }  
  228.             catch (Exception e)  
  229.             {  
  230.   
  231.             }  
  232.         }  
  233.     }  
  234.   
  235.     /// <summary>  
  236.     /// This method is used to iterate the result and store into Hobbies buffer.  
  237.     /// </summary>  
  238.     /// <param name="data"></param>  
  239.     /// <param name="IDCol"></param>  
  240.     public void CallDataHobbies(JToken data, string IDCol)  
  241.     {  
  242.         foreach (var item in data["d"]["results"])  
  243.         {  
  244.             try  
  245.             {  
  246.                 HobbiesBuffer.AddRow();  
  247.                 HobbiesBuffer.ItemId = GetDecimal(item, IDCol, -1); 
  248.                 HobbiesBuffer.Title = GetString(item, "Title"); 
  249.                 HobbiesBuffer.Modified = GetDateTime(item, "Modified");  
  250.                 HobbiesBuffer.Created = GetDateTime(item, "Created");  
  251.                 HobbiesBuffer.CreatedById = GetDecimal(item, "AuthorId", -1);  
  252.                 HobbiesBuffer.ModifiedById = GetDecimal(item, "EditorId", -1);  
  253.                 HobbiesBuffer.CreatedBy = GetComplex(item, "Author""Title");  
  254.                 HobbiesBuffer.ModifiedBy = GetComplex(item, "Editor""Title");  
  255.             }  
  256.             catch (Exception e1)  
  257.             {  
  258.   
  259.             }  
  260.         }  
  261.   
  262.     }  
  263.     public string GetString(JToken token, string key)  
  264.     {  
  265.         string value = string.Empty;  
  266.   
  267.         try  
  268.         {  
  269.             value = Convert.ToString(token[key]);  
  270.         }  
  271.         catch (Exception e)  
  272.         {  
  273.   
  274.         }  
  275.   
  276.         return value;  
  277.     }  
  278.   
  279.     public string GetComplex(JToken token, string key, string var)  
  280.     {  
  281.         string value = string.Empty;  
  282.   
  283.         try  
  284.         {  
  285.             JToken tok = token[key];  
  286.             value = GetString(tok, var);  
  287.         }  
  288.         catch (Exception e)  
  289.         {  
  290.   
  291.         }  
  292.   
  293.         return value;  
  294.     }  
  295.   
  296.     public Decimal GetComplexID(JToken token, string key, string var)  
  297.     {  
  298.         Decimal dec = -1;  
  299.   
  300.         try  
  301.         {  
  302.             JToken tok = token[key];  
  303.             dec = GetDecimal(tok, var, -1);  
  304.         }  
  305.         catch (Exception e)  
  306.         {  
  307.   
  308.         }  
  309.   
  310.         return dec;  
  311.     }  
  312.   
  313.     public Decimal GetDecimal(JToken token, string key, Decimal defaultVal)  
  314.     {  
  315.         Decimal dec = -1;  
  316.   
  317.         bool complete = Decimal.TryParse(GetString(token, key), out dec);  
  318.   
  319.         if (complete)  
  320.         {  
  321.             return dec;  
  322.         }  
  323.         else  
  324.         {  
  325.             return defaultVal;  
  326.         }  
  327.     }  
  328.   
  329.     public Int32 GetInt(JToken token, string key, Int32 defaultVal)  
  330.     {  
  331.         Int32 dec = -1;  
  332.   
  333.         bool complete = Int32.TryParse(GetString(token, key), out dec);  
  334.   
  335.         if (complete)  
  336.         {  
  337.             return dec;  
  338.         }  
  339.         else  
  340.         {  
  341.             return defaultVal;  
  342.         }  
  343.     }  
  344.   
  345.     public DateTime GetDateTime(JToken token, string key)  
  346.     {  
  347.         DateTime dec;  
  348.         bool complete = DateTime.TryParse(GetString(token, key), out dec);  
  349.         return dec.ToLocalTime();  
  350.     }  
  351.   
  352.     public string GetMultipleComplex(JToken token, string key, string objectKey, string seperater)  
  353.     {  
  354.         string tempString = string.Empty;  
  355.         List<string> strList = new List<string>();  
  356.         try  
  357.         {  
  358.             JToken stringTokens = token[key]["results"];  
  359.   
  360.             foreach (var user in stringTokens)  
  361.             {  
  362.                 string tempStr = GetString(user, objectKey);  
  363.                 strList.Add(tempStr);  
  364.             }  
  365.             if (strList.Count > 0)  
  366.             {  
  367.                 tempString = string.Join(seperater, strList);  
  368.             }  
  369.         }  
  370.         catch (Exception ex)  
  371.         {  
  372.   
  373.         }  
  374.   
  375.         return tempString;  
  376.     }  
  377.   
  378. }  
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.