ByHariharan Rajendran

CData SharePoint SSIS Components

I had a requirement to extract the data from SharePoint (only Metadata) and I was looking for a task to handle this in SSIS.

Then I found a simple 3rd party plugin to fulfil my requirement. In this post, I am explaining how to use the CData SSIS components.

You can download the component from

The above link will take you directly to the download page. You need to buy this tool to use it on your production packages. If you want to test, then you can go with a trial version.

Download and install where you installed the SQL Server Data tools.

Once you installed then you can start creating an SSIS package using CData SharePoint Source and Destination tasks.

You need to create a connection manager to speak with on your SharePoint server either on-premises or online.

Right click on the connection manager section and choose “CDATA_SHAREPOINT”.

You need to enter your SharePoint site URL and credentials.

Once everything is done then drag and drop the “CData SharePoint Source” task. Point the created Connection manager. Under Table or View, you can choose any of the resources from SharePoint.

Check the columns and ensure everything is working. Then you can map the output to the destination tasks.

ByHariharan Rajendran

Extract Data from SharePoint List using SSIS

Microsoft released a new task called “OData Source” which helps us to connect to online SharePoint and extract the lists.

Follow the below steps to configure and extract the data.

  1. Make sure to use the latest version of SQL Server Data Tools to work with SQL Server integration Projects.
  2. Use the Data Flow Task
  3. You can see “OData Source” task. Drag and drop into the data flow canvas.
  4. This OData Source task required a connection manager. Click New on the OData Connection Manager.
  5. You need to pass the below information to successfully connect with SharePoint Online.
    1. Service document location – https://<yoursharepointsitename>/_vti_bin/listdata.svc
    2. Authentication Type: Microsoft Online Services
    3. UserName: Your email address to connect SharePoint Online
    4. Password: your password.
  6. Test the connection.
  7. On the OData Source Editor, Choose the SharePoint list on the collection section.
  8. Check the columns and use any destination where you want to store the data.


ByHariharan Rajendran

Extract Data from SharePoint excel using SSIS

There is no direct way of extracting the SharePoint Excel data to SQL Server using SSIS. However, we can process multiple steps to achieve the result.

This post explains that how we can download the excel files from SharePoint and store it in a local drive and then process the stored excel from SSIS.

To download the files easily, we can use the tool called “SSIS Integration Toolkit for Microsoft SharePoint”.

Follow the below steps.

  1. Visit –
  2. Download 64 bit of Toolkit
  3. Install it on your system where you have installed the SQL Server Data Tools
  4. Create a new SQL Server Integration Projects
  5. Drag and Drop the data flow task into the control flow
  6. You could see “SharePoint Source” and “SharePoint Destination” tools under Common folder.
  7. Before Drag and drop the “SharePoint Source” task, you should create a SharePoint connection Manager.
  8. Configure SharePoint Connection Manager for SharePoint on-premises or online.
  9. Test the connection once.
  10. Drag and drop the “SharePoint Source”. Point the created SharePoint connection and Choose the source list as “Documents” or the library where you have stored the excel files.
  11. Configure the Download Destination.
  12. Once excel files are downloaded then you can use that excel files to process further using excel source tasks.


ByHariharan Rajendran

Linear Regression with R – Part 2

Check the Linear regression introduction and how to use excel for linear regression.

In this post, let me explain how to use R programming for linear regression.

I am taking the same dataset in R to produce the linear regression.

Run the below codes.

Year<- c(2000,2001,2002,2003,2004,2005,2006,2007,2008,2009,2010)


#linear regression


#display coefficient


# new value for year 2011

year2011 <- data.frame(Year=2011)


#predict the sales amount.

predict (result,year2011)

ByHariharan Rajendran

Linear Regression

Before starting with linear regression, let us try to understand the machine learning concepts.

As of today, machine learning is one of the trending technologies as the applications are started to consume real-time data and deliver machine learning services.

As part of machine learning, we have three different types of machine learning models available.

  1. Supervised Learning
  2. Unsupervised Learning
  3. Reinforcement learning

In Supervised Learning, the system tries to learn from the previous examples that are given.

Under supervised learning, we have classification and regression models.

One of the common and simple techniques for predicting a continuous variable is called linear regression.

It assumes a linear relationship between the outcome and the predictor variables.

The linear regression equation can be written as y = b0 + b*x + e, where:

  1. b0 is the intercept,
  2. b is the regression weight or coefficient associated with the predictor variable x.
  3. e is the residual error

This post covers the linear regression on excel and R studio.

Linear Regression on Excel

Follow the below steps to perform the linear regression in excel

Step 1: Consider you have year wise sales amount. You are in the situation to predict the sales amount for the year 2011.

Step 2: Create a scatter plot like below with Year and Sales Amount

Step 3: Right-click on the line and choose trendline.

Step 4: Display the equation on the trendline by selecting the checkbox on the Format trendline properties.

Step 5: Predict the value using the generated formula for existing values.

Step 6: Predict for the year 2011.

As you can see the values are not 100% correct, we need to adjust the con-efficient and reduce the r2 values to get the perfect result.

ByHariharan Rajendran

Power BI Report using Shared Dataset [Detailed Step by Step]

In my previous post, I have explained the possibility of using SSRS shared dataset as a source for Power BI report. Refer my previous post here.

In this post, I am giving you the detailed step by step procedure to work with the shared dataset in Power BI.

Follow the below steps,

Step 1: Open Power BI Report Server portal URL. For example, the URL is like below.


Step 2: Add the following extension with the above URL. /api/v2.0/datasets. It will list out all the datasets with ID as like below.


Step 3: Filter the dataset with ID. Here I used the second dataset id.


My URL, http://lab/ReportsPBI/api/v2.0/datasets(ca502b81-c88d-4646-af32-ca08d9b4a1f4)/data

Step 4: Open Power BI Desktop and choose OData feed data connector

Step 5: Pass the above URL as like below.

Step 6: Choose advance and select “Include open type columns” checkbox to get all the columns of the dataset.

Step 7: Click Ok and select Edit Option on next screen.

Step 8: Select the icon near the “More Columns” as like below. It will list out all the columns.

Step 9: Uncheck the “Use original column name as a prefix” and click ok. Finally click “Close & Apply”.

Step 10: Choose the fields and create a report.

Thanks. Let me know if you face any issues.

ByDr. SubraMANI Paramasivam

Empowering Every Person in the planet, with “Awareness Enabled Reports”

Following Satya’s quote “Empowering Every Person in the Planet”, in 2016 Microsoft Inspire conference, have certainly left my mind with unstoppable beats. I certainly understood what Satya meant, but I still thought about doing something like this, with my own upgraded version of Data Awareness Programme (which I started in 2014). But at that time, all I had was Power View, Power Map, Power Pivot as Excel add-ons and tried my best, to do the awareness programmes in remote villages, by mingling with the villagers and collecting their own data and showing some visuals back to them. I did this to improve their life style, find more time for personal and have better earnings. Though the main target was students, I hoped this message would spread to their friends, family and others in the remote places.

Following the release of full version of Power BI, I now have a fully working site, with living “Awareness Enabled Reports”, from sleeping open data sources (taken from various Gov/Non-Gov sites).

I managed to get this far, with a simple equation of A + B + C + D = E ( (EEP)). Let me explain this in detail.

Following this, I managed to extract data from various open data sources and identified the most global challenges, that we are encountering and/or going to be a major threat in near future.

With our all time favourite reporting tool, “Microsoft Power BI“, published some “Awareness Enabled Reports” to site and categorized them with regional, national and global challenges, for easy manoeuvring within the EEP site.


This EEP site currently has 3 simple goals.

  1. View the “Awareness Enabled Reports” worldwide in any devices, by categorizing Regional or Global challenges.
  2. Submitting another “Awareness Enabled Report” as a Developer with some guidance. Also listed, Global Challenge Topics to select from open data sources and provided some tips to convince the selection committee and finally submitting the story.

  1. Promote EEP
    1. Provided options to Promote EEP as  a Developer, User Group Leader & End user.

Below screenshot shows categorization of reports by UK.



As per above equation, ‘D’ is the support that we need from you, to promote in any of the following ways.


Develop “Awareness Enabled Reports” for all listed Global Challenge topics and submit your data story to EEP site and once published, tweet / share / post in social media and spread the awareness.


A request to all Community group leaders, to spend at least a minute by starting or ending your user / local / online group sessions by introducing / re-introducing, this website and showcasing the opportunity to all attendees, to build and submit their own data story with “Awareness Enabled Reports“.


Every time you see a new “Awareness Enabled Report“, do tweet / share / post in social media and support to spread the awareness.


Thanks in advance for your support and thanks for your time reading through this far, to create awareness with “Awareness Enabled Reports“.

ByHariharan Rajendran

How to handle date in R

Date data type is very important for all programming languages. To handle the date in a proper way, we need to apply some formatting logic.

This is the case for R programming language as well. This post explains you, how we can handle the Date data in R. There are different functions available in R to handle date.

To get the today’s date, use Sys.Date()


> Sys.Date()

[1] “2018-05-22”


To get date and time, use date()

> date()[1] “Tue May 22 15:29:18 2018”


Check the below list of symbols to play with date and time format.

Symbol Meaning Example
%d day as a number (0-31) 01-31
abbreviated weekday
unabbreviated weekday
%m month (00-12) 00-12
abbreviated month
unabbreviated month
2-digit year
4-digit year


> today <- Sys.Date()> format(today, format=”%B %d %Y”)[1] “May 22 2018”


Date Conversion

In a real-time scenario, we will not get date as date data type. Always, we need to convert to proper date data type. To convert to date data type, we need to use as.Date() function.


as.Date(x, “format”)

x – Field or column

format – use the above symbols to frame the proper format.


DateVec<- c(“01/05/2018”, “08/16/2018”)


[1] “01/05/2018” “08/16/2018”


Length     Class        Mode

2            character   character

dates <- as.Date(DateVec, “%m/%d/%Y”)


[1] “2018-01-05” “2018-08-16”


Min.      1st Qu.       Median         Mean      3rd Qu.         Max.

“2018-01-05” “2018-03-01” “2018-04-26” “2018-04-26” “2018-06-21” “2018-08-16”