So, I came to share because I did not find this solution in any forum and could come to serve the professionals who are going through this problem at the moment or even that they happen in the future. Well, after 15 days I finally got the package to run and blabber, the problem was only ONE parameter. On 14/08 I posted a question about an error I was receiving when I use the ODataSource connector in SSIS to extract data from a Sharepoint list. More importantly, this will help you get SharePoint data both on-prem and in the cloud into a central data warehouse. Lists are exposed as Collections, so if you want to work with list data, you can then select the list from the list of Collections.Īt this point, the data source will act like any other SSIS data source, you can select and transform columns at will. Once the client components are in place, and the option is selected, the data source should be able to connect to the source, and the connection manager can be closed. In order to authenticate to Office 365, the machine must have the SharePoint Server 2013 Client Components SDK installed on it. If the Online Services Authentication option is disabled, or greyed out, as it was for me when I first tried to use it, it’s because a prerequisite is missing. In order to authenticate to Office 365, you must first select the “All” button in the toolbar, and set the value of “Microsoft Online Services Authentication” to true. This is due to Office 365’s “unique” authentication mechanism.
If you are using Office 365, and you click Test Connection at this point, you’ll receive an error “Test connection failed –> The remote server returned an error: (400) Bad Request.”
If the connection is on premises, you can use Windows Authentication, but if it is Office 365, you must use a stored name and password. It takes the form of the URL of the site, along with the suffix /_vti_bin/listdata.svc. The Service document location is the OData endpoint. The connection will be common to all lists and libraries within a site, so something based on the name of the site is likely appropriate. You’ll first need to configure an OData Connection Manager, and you’ll do that by clicking the New button. In the SSIS Toolbox, You should see the OData Source.ĭrag the tool on to the design surface, and double click to configure it.
Once you install the OData Source, you open up SQL Server Data Tools, open an SSIS project, and add or edit a data flow task. There is, however a trick to getting it working. It’s also an official Microsoft product, and is fully supported. The OData services require the same authentication, but the the new OData Source supports it. While SOAP web services are supported in Office 365, the adapters don’t support the Office 365 authentication mechanism, which effectively renders them useless. These CodePlex adapters have more recently been bumping into another limitation. We have used them for years, and they work very well, however, they are a CodePlex project, and therefore not fully supported. These adapters plug in to SSIS and wrapper the SharePoint SOAP web services, and therefore do not need to be installed on a SharePoint server. I’ve written before about how SharePoint data can be extracted into a data warehouse using SSIS and the SharePoint List Source and Destination Adapters, available from CodePlex. This matters to those of us in the SharePoint world because any SharePoint list data can be expressed as OData.
Until now it was necessary to code OData connections using the script object. What is it? It allows SQL Server Integration Services (SSIS) to use an OData feed as a first class citizen data source in the same manner as SQL Server, Oracle, etc. Last week, Microsoft released the OData Source for Microsoft SQL Server 2012.