SSIS SharePoint Data Source

This post shows you how to use Office 365 SharePoint as data source in SSIS Data Flow.

Pre-requisite:

1. You already have basic knowledge on SSIS.

2. You have installed SharePoint SDK to your development environment https://chanmingman.wordpress.com/2018/10/07/ssis-could-not-load-file-or-assembly-microsoft-sharepoint-client/

3. SQL Server Data Tool is installed https://chanmingman.wordpress.com/2014/08/02/sql-server-data-tool-ssdt/.

4. You have Office 365 SharePoint online login.

It used to have a codeplex SharePoint data source but it just didn’t last for very long. A lot of people are still looking for a SSIS SharePoint data source. In this post, I will show you how to create a SSIS SharePoint Source with the combination of SharePoint SDK and Script Component in SSIS.

1. Create a SSIS project.

2. Drag Data Flow Task to Control Flow page. Double click the Data Flow Task. Drag Script Component to Data Flow page. When Select Script Component Type pops up then select Source.

3. Double click the Script Component. Click on Inputs and Outputs.

4. Expand the Output 0.Click the Add Column. In this example, I will add 2 output columns, CategoryIdOut and ProductNameOut.

5. Click the Script. Click Edit Script…

6. Replace the CreateNewOutputRows method with the following code. Of course, you need to change the siteUrl, password, and login name.

public
override
void CreateNewOutputRows()

{

    string siteUrl = https://ming.sharepoint.com/sites/dev”;

    ClientContext clientContext = new ClientContext(siteUrl);

    string password = “password”;

    SecureString securePassword = new SecureString();

    foreach (char c in password)

    {

        securePassword.AppendChar(c);

    }

    clientContext.Credentials = new SharePointOnlineCredentials(“ming@ming.onmicrosoft.com”, securePassword);

    SP.List oList = clientContext.Web.Lists.GetByTitle(“ProductList”);

    CamlQuery camlQuery = new CamlQuery();

    camlQuery.ViewXml = “<View><RowLimit>100</RowLimit></View>”;

    ListItemCollection collListItem = oList.GetItems(camlQuery);

    clientContext.Load(collListItem,

        items => items.Include(

        item => item.Id,

        item => item[“CategoryId”],

        item => item[“ProductName”]));

    clientContext.ExecuteQuery();

    foreach (ListItem oListItem in collListItem)

    {

        Console.WriteLine(“ID: {0} \nId: {1} \nName: {2}”, oListItem, oListItem.Id, oListItem[“ProductName”]);

        Output0Buffer.AddRow();

        Output0Buffer.ProductNameOut = oListItem[“ProductName”].ToString();

        Output0Buffer.CategoryIdOut = oListItem[“CategoryId”].ToString();

        }

}

7. Finally drag a OLE DB destination. For me, I am using the Northwind database, Products table.

Advertisements
Posted in .Net, Cloud, Community, Computers and Internet, Data Platform | Tagged , | Leave a comment

SSIS Could not load file or assembly ‘Microsoft.SharePoint.Client

This post shows you one of the possibilities your SSIS cannot load the Microsoft.SharePoint.Client dll.

When you wanted to connect to SharePoint Online in your SSIS project then you copied Microsoft.SharePoint.Client and Microsoft.SharePoint.Client.Runtime to a folder and point your Script Component code to it. You have gotten the following error when running it.

“Couild not load file or assembly ‘Microsoft.SharePoint.Client, Version=16.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c’ or one of its dependencies. The system cannot find the file specified.”

You cannot just copy the dll files to a folder and refer to it. You need to install the SharePoint SDK. The SDK for SharePoint 2016 as below.

Install SharePoint Server 2016 Client Components SDK, https://www.microsoft.com/en-us/download/confirmation.aspx?id=51679.

Posted in .Net, Cloud, Community, Computers and Internet, Data Platform, Office 365 | Tagged , , | 1 Comment

Cyber attacks on seaports

This post highlights the Cyber attack involve 4 well known seaports in the world.

Below are the links to the Maersk, COSCO, Port of Barcelona, Port of San Diego Cyber Attack.

Moller-Maersk puts cost of cyber attack at up to $300m

https://www.ft.com/content/a44ede7c-825f-11e7-a4ce-15b2513cb3ff

Long Beach Port terminal hit by ransomware attack

https://www.presstelegram.com/2018/07/24/long-beach-port-terminal-hit-by-ransomware-attack/

Port of Barcelona suffers a cyberattack that impacted many of its servers

https://cyware.com/news/port-of-barcelona-suffers-a-cyberattack-that-impacted-many-of-its-servers-5f22c204

Port of San Diego Hit by Cyberattack

https://maritime-executive.com/article/port-of-san-diego-hit-by-cyberattack

Most of them were attacked by ransomware. One obvious fact in these attacks was the ransomware targeted Windows 7 and below.

The better solution for now is upgrade the machines to Windows 10 as many as possible. Alternatively, you can convert some of the machines to Linux. Of course, it does not mean ransomware will not attack Windows 10 and Linux forever. It is just to lower the risk for now.

Posted in Community, Computers and Internet | Tagged | Leave a comment

SQL Server Management Studio offline installation

There are many posts related to this in the Internet. This post is just highlight one of the points that Microsoft should really listen.

Microsoft stream a lot of installation and making many counties with lower speed Internet. Those regions always fail the installation and keep retrying. This is unproductive to the business.

Microsoft should keep up on those applications that can download a full installation file and then do a local installation. I downloaded SQL Server Management Studio, S, SMS-Setup-ENU.exe, with 826MB at https://docs.microsoft.com/en-us/sql/ssms/sql-server-management-studio-changelog-ssms?view=sql-server-2017. The installation in my VM completed within 20 minutes.

Posted in .Net, Cloud, Community, Computers and Internet, Data Platform | Tagged , | Leave a comment

SQL Server 2016 Download Setup files In Progress

This short blog shows you one of the possibilities the SQL Server 2016 stuck at Download Setup files is In Progress.

I have a Hyper-V running. The Virtual Switch Manager I configured External network connect to my Wifi. I could browse Internet without issue but when I was installing SQL Server 2016 it stuck at the Download Setup files.

I resolved the problem by changing the Virtual Switch Manager to point the Virtual Network Interface to Internal network then shared my host NIC to this virtual NIC. That worked for me.

I believe this is the bug of Hyper-V and Microsoft needs to resolve the root cause of this.

Posted in .Net, Cloud, Community, Computers and Internet, Data Platform | Tagged | Leave a comment

Windows Server 2016 Antivirus

This post is showing you if you have Windows Server 2016 then you do not need to buy a separate Antivirus software

Obviously in Windows Server 2016 you can see it is updating Antivirus during the Windows updating process. So you might want to stay with it for a while before spending extra cost for other Antivirus software. As till now, there is no hard evident to proof other Antivirus software is catching virus better than the Windows Defender.


You can find the Antivirus Windows Defender showing below.

Posted in .Net, Cloud, Community, Computers and Internet | Tagged | Leave a comment