Using the OData Source in SQL Server Integration Services

By:   |   Comments (65)   |   Related: More > Integration Services Development


Microsoft recently released the OData source adaptor for Integration Services (SSIS). Using this source component, it becomes really straightforward to read data from OData sources, such as SharePoint lists. This tip will walk you through the installation and configuration of this brand new component.


OData is an open data access protocol to provide access to a data source on a website. It was initially defined by Microsoft. It is used for example in Azure and in SharePoint, but also by other companies like SAP and eBay. This protocol is recommended for the Open Government initiative. For more information, see the related Wikipedia article. One of the most anticipated uses of this source is the ability to read SharePoint lists. Since those lists are accessible through OData, this component now provides a native built-in method to draw data from SharePoint, while previously you had to rely on 3rd party products such as the SharePoint List Source and Destination from Codeplex.

Installation of the OData Source for SQL Server

First of all you need to download the component, which is available for both SQL Server 2012 and 2014. The 2012 version is a separate download, while the 2014 one is located in the SQL Server 2014 Feature Pack. You have the option to download a 32-bit or a 64-bit component. Since SSDTBI (the Visual Studio shell/templates for developing business intelligence projects) is a 32-bit application, we will download this one.

Download from Feature Pack

The installation itself is very straightforward. First you have the welcome screen:

Welcome screen

In the next screen you have to read and accept to the License Terms.


And that's it. Just click Install.


The installation itself is over very quickly.


And we're done.

Installation finished

If you open up an SSIS project, you can find the new source component in the Common section - not in the Sources where you'd expect it.

SSIS Toolbox

Note: If you have a 64-bit server, you'd want to download the 64-bit version of the component and install it on the machine. Otherwise you are forced to run the package in 32-bit mode.

Reading From an OData Source

Let's put the new source to work. First of all we are going to read from a SharePoint List. These lists can be exposed as an OData feed, so they are the perfect guinea pigs for our new source. In my SharePoint site, I have a list called Olympic Disciplines, with three columns that interest me: DisciplineID, Discipline and Sport.

My SharePoint List

Add a data flow if you haven't already and drag the OData source to the data flow canvas. The first thing to do when opening the editor is creating an OData Connection Manager.

The OData Source editor

Clicking on New... opens up the OData Connection Manager Editor.

OData Connection Manager Editor

You have to provide a name for the connection manager and the Service Document Location. For reading SharePoint Lists, it takes the following format:


The last part, _vti_bin/listdata.svc is always fixed; it is the endpoint the source communicates with to retrieve the OData feeds. Listdata is not the name of some SharePoint list. Windows Authentication can be used or you supply a username and password. If the SharePoint site is in another domain, you can pass this as well in the user name field. Click Test Connection to make sure you have supplied the correct parameters. Hit OK to create the connection manager and to go back to the source editor.

In the editor, you have to specify either a collection or a resource path. For now we'll use the collection method, I'll come back to resource paths later in this tip. In the Collection drop-down list, select the SharePoint list you wish to read from. In our case, we need to select OlympicDisciplines.

Selecting a collection

At the bottom of the editor, you get a preview of the full URL of the OData resource you are going to read from.

Editor is finished

At this point, you can click on Preview... to take a look at the data.


As with all other source components, you can go to the columns pane and either rename columns or remove them from the output. You might notice there are a lot of metadata columns that come along with the SharePoint list. We'll do something about that later on. The Error Output is the same as in any other data flow transformation.

Columns Pane

The configuration of the component is done and you can now extract data from the OData feed by running the package!


Advanced Configurations for the OData Source Component in SSIS

This is not everything the OData source has up its sleeve. There are some more advanced features we can take advantage off.

Resource Paths

When you select a collection in the editor, you retrieve the columns of the feed and that's it. With resource paths you have more control over the data that is being returned. You can for example select specific IDs or you can link related feeds together. To illustrate this concept, we shall use the publicly available OData feed of the Northwind database. It is located at First we need to create a new connection manager. Since it is public, you don't need to specify a user name and password.

Northwind connection manager

We are going to use the following resource path: Customers('ALFKI')/Orders(10643)/Order_Details. This tells the OData protocol to retrieve the order details of order #10643 placed by customer with ID 'ALFKI'.

Returning order details

However, at the time of writing the source component is behaving erratically when using resource paths. Some work, as illustrated above, while others don't. For example, the resource path Customers('ALFKI') always returns an error (although it is part of the longer resource path that does work). Referencing a collection directly with a resource path, for example Customers, always returns results.

Bad Request Error

The strange part is that those resource paths do work when used directly in some browsers, such as Chrome, but not in Internet Explorer.

Long live the browser!

I hope these issues with the resource paths get resolved soon, as resource paths can be really powerful to string multiple OData feeds together. For more information about resource paths, take a look at the OData documentation.

Query Options with OData Data Sources

Another very useful feature are the query options, the last field of the editor. With those query options, you can manipulate and shape the results returned by the OData feed. For example, with $select=DisciplineID,Discipline,Sport you select only those three columns. This is very important, as you want to minimize all the columns returned in order to improve the efficiency of the buffers of the data flow. Too much unnecessary columns means a slower data flow!

There are many other options, such as filtering, skipping rows, sorting, taking the top n rows and so on. Let's expand our query options and also sort descending on the sport column.

Using the query options

For more information about the various query options, check out the OData documentation.

Source Properties

There are some custom properties of the source component you can configure.

Custom properties
  • CollectionName: the name of the collection chosen in the dropdown menu.
  • DefaultStringLength: this specifies the length of string columns that have no maximum length. Although sometimes a text stream is used to import string data (see next paragraph). 4000 characters is quite long, so be aware that this can effect the buffer usage of the data flow. This is the only property that is not configurable through the GUI.
  • Query: the query options.
  • ResourcePath: the resource path used to retrieve the data.
  • UseResourcePath: specifies if the property CollectionName or ResourcePath is used.

All properties, except DefaultStringLength, can be set through expressions. For more information about the custom properties, see the following MSDN article: OData Source Properties.

The BLOB Effect

When the metadata of the OData feed is not defined explicitly - in the Olympics Discipline list for example the columns are defined just as string - it is possible SSIS will import the columns with the data type DT_NTEXT, which is a Unicode text stream data type.

Nothing to be seen here...

This data type can sometimes be difficult to work with in SSIS, but luckily there is an easy fix: simply go to the advanced editor of the source and set the data type of the columns to a more acceptable data type.

Convert to regular strings

Problem solved!

Problem solved!


The new SSIS OData source provides an easy way to read OData sources. It can extract data from publicly available OData feeds, but also very easily from SharePoint lists which might be its biggest strength. You can configure query options to manipulate the result set, but using resource paths seems to be a bit tricky.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, June 6, 2019 - 3:26:16 AM - Koen Verbeeck Back To Top (81342)

Hi McF,

normally since SSIS 2016, the OData Source should be included by default in VS.
Make sure you have the latest version of SSDT. It's also not listed under the sources, but under the "Common" section.


Wednesday, June 5, 2019 - 5:17:21 AM - McF Back To Top (81333)

I can't find ODATA connection on my Visual Studio 2017 while developing SSIS. 

What I need to install to be able to use ODATA Source in SSIS?

Friday, November 16, 2018 - 2:07:25 AM - Koen Verbeeck Back To Top (78270)


what is the error?

Thursday, November 15, 2018 - 4:18:15 PM - Stan Back To Top (78265)

I'm building a SSAS model that connects to a SharePoint 2016 list.  Using Visual Studio 2015, deploying to a CU8 2014 SSAS server.

In visual studio I can connect to a list and even preview it, but when I try to process or bring the list data down, I receive an error. 

So you know what versions of Microsoft.Data.DataFeedClient.dll are compatible with SharePoint 2016?  I'm wondering if the server is out of date?

Tuesday, October 23, 2018 - 6:25:20 AM - singi reddy premakar Back To Top (78033)


i need example for how to load multiple paths information in odata source.


how to loop handle


Friday, September 14, 2018 - 3:06:46 AM - manjunatha Back To Top (77559)

Hi Koen Verbeeck,

Using OData in SSIS package, able to fetch data from sharepoint and load to sql server table, I have few below issues -

1. sharepoint character data is converting to numeric

2. Getting HTML tags along with sharpoint data (<div class=1233.......> My sharepoint test data </div>), I need only string ' My sharepoint test data'

How to get charcter data and how to remove HTML tags

Appreciate your help on this.



Tuesday, August 28, 2018 - 7:28:57 AM - Tarak Back To Top (77317)

I have a scenario like i have to pull Data from ALM to the sql server. ALM is a .jsb web server (JavaServer Pages (JSP)). any idea folks. help needed.

Tuesday, August 14, 2018 - 9:51:03 AM - Koen Verbeeck Back To Top (77173)

Hi Tarak,

if you want to run SSIS packages outside Visual Studio, you need to install Integration Services on the machine (using the SQL Server set-up).


Tuesday, August 14, 2018 - 7:25:10 AM - Tarak Back To Top (77171)


Do we need to have SQL server running on the system to run Odata task from visual studio. My senario is like i have been given access to a server which has only Data tools installed on it. I have to pull data from a sharepoint so i installed odata and and eveything is ok when execcuted in visual studio, when i tried to run it in CMD with Dtexec it gave an error like "Code: 0xC000F427 Source: Data Flow Task SSIS.Pipeline Description: To run a SSIS package outside of SQL Server Data Tools you must install OData Source of Integration Services or higher." pls help

Tuesday, May 15, 2018 - 5:20:45 AM - Koen Verbeeck Back To Top (75944)


Pitca, that is correct. This is a source so it only reads.
There's a SharePoint List destination somewhere on Codeplex (which is deprecated), but I think it isn't maintained anymore. I'd take a look at the SharePoint APIs.

Monday, May 14, 2018 - 10:47:43 AM - PITCA Back To Top (75938)

 I assume this only allows reading of data from a SP list, not the posting of data into a sharepoint list.  Is that accurate? Thanks

Wednesday, April 11, 2018 - 6:00:42 AM - Ericson Dagatan Back To Top (75672)

Encounter an error in ODataSource

[OData Source [299]] Error: Microsoft.Data.DataFeedClient.DataFeedException: An error occured when detect the document payloadkind.

   at Microsoft.Data.DataFeedClient.DataFeedODataReaderV3.DetectPayloadKind(ODataMessageReader odataMessageReader, ODataFormat& odataFormat)

   at Microsoft.Data.DataFeedClient.DataFeedODataReaderV3.<>c__DisplayClass2.<.ctor>b__1()

   at Microsoft.Data.DataFeedClient.DataFeedODataReaderV3.InterceptODataException(Action action)

   at Microsoft.Data.DataFeedClient.DataFeedODataReaderV3..ctor(IODataResponseMessage responseMessage, DataFeedConnectionParameters connectionParameters, DataFeedODataEdmWrapperV3 odataEdmWrapper, Uri requestUrl)

   at Microsoft.Data.DataFeedClient.ODataHelper.CreateODataV3OrV4Reader(IWebResponse webResponse, Boolean applySlashUpdatingBaseUriResolver, DataFeedConnection connection, Uri commandRequestUri)

   at Microsoft.Data.DataFeedClient.DataFeedCommand.ExecuteDbDataReader(CommandBehavior behavior)

   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)

   at Microsoft.SqlServer.IntegrationServices.OData.ODataSource.GetSchemaTable()

   at Microsoft.SqlServer.IntegrationServices.OData.ODataSource.PreExecute()

   at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100 wrapper)

Thursday, March 8, 2018 - 8:36:39 AM - Koen Verbeeck Back To Top (75375)


Hi David,

if the lists are always the same, you can use a for each loop to loop over the 1500 sites.

If not, you can try to create the packages using Biml (which will generate a package based on metadata). You can find a tutorial on this site about Biml.

Wednesday, March 7, 2018 - 7:31:31 PM - David Narramore Back To Top (75367)

 Thank you for the article. I have 1500 sites with similar list that I need to load into the SQL database. Do you know of anyway to do this with an ssis package?




Monday, February 19, 2018 - 2:49:42 AM - Koen Verbeeck Back To Top (75240)

Hi Eric,

I haven't tried it myself. From what I read online, the SharePoint List Source will probably be a better choice but it seems it's not updated anymore.
Maybe through an API?



Friday, February 16, 2018 - 2:07:13 PM - eric81 Back To Top (75229)


Great article, but I was wondering the old Sharepoint Adapter Source (SSIS) you used to be able to specify Site List View field.  Is there a way to perform this operation in OData Source?


Thursday, February 1, 2018 - 4:08:21 PM - sravya Back To Top (75080)


 Hi , Does any know how to use share point list destination ?

Wednesday, October 4, 2017 - 5:42:25 PM - Toraj Khavari Back To Top (66906)

 Awsome write up.

Thanks you, Toraj


Thursday, August 24, 2017 - 3:51:49 PM - Devster Back To Top (65250)

Has anyone figured how to use a SharePoint Online list as a destination?  i.e.  On prem sync to online.   

Wednesday, June 14, 2017 - 3:31:18 AM - Koen Verbeeck Back To Top (57398)


you can download it from Codeplex:

Monday, June 12, 2017 - 3:31:36 AM - Krishna Back To Top (57233)


 Do we have any SSIS destincation to update sharepoint List in 2014

Friday, May 12, 2017 - 8:16:31 AM - Koen Verbeeck Back To Top (55693)

Hi Pasquele.

If you have only the OData source and nothing else in the data flow (maybe a multicast after the source), how long does it take?

Friday, April 28, 2017 - 12:06:04 PM - Pasquale Back To Top (55283)

Hi Koen,

I'm trying to use the OData source component in SSIS 2014 to read OData data exposed by a Dynamics Nav solution: to read about 500.000 rows it takes 1 hour and 27 minutes. It's a time too high! Is it possible to improve the read performances?


Tuesday, February 7, 2017 - 2:51:46 AM - Koen Verbeeck Back To Top (46067)

Hi Paul,

sorry, but I have no idea. A quick Google search shows me that there are other people with this error as well, but mostly in the context of SharePoint Online.

Is it an option to loop over the list using a For Each Loop container and extract just 10,000 items at a time?

Wednesday, February 1, 2017 - 11:20:40 AM - Paul Back To Top (45784)

I am using a OData Source to read sharepoint data.  I have about 45 packages moving files from SharePoint to a new SharePoint over CRM implementation.  I am having problems with one of the packages and it is probably due to sheer volume.  The package in question is trying to move over 175,000 files from Sharepoint. 

The buffer loads just over 9,900 files at a time for processing.  After the second batch, the connection appears to close (19832 files processed).  I still have over 150,000 files left to process.  I tried changing some OData Source configuration settings but got the same error.


Any suggestions on how to keep the connection open so I can process all 175K+ rows?


Error Message:

Error: 0xC02090F5 at XXXX, OData Source [22]: The OData Source was unable to process the data. Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.
Error: 0xC0047038 at XXXXX, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on OData Source returned error code 0xC02090F5.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.


Tuesday, October 4, 2016 - 3:19:17 AM - Koen Verbeeck Back To Top (43484)


this might be caused by server side paging.
Check out this article:

Monday, October 3, 2016 - 9:17:16 AM - Shiva Abhishek Back To Top (43480)


 Hi All,


I am retreving the data from sharepoint online using OData source in SSIS. There are total of 80000 row but it is only giving me around 40900 rows. Is there any limitation while pulling the data on rows or if you can give me some input on increasing the rows limit.



Thursday, June 9, 2016 - 2:13:23 AM - Koen Verbeeck Back To Top (41648)

Hi Pedro,

check out the comments of this article:

Someone says you have to copy 2 dlls to a folder in order to work with claims based authentication.

Wednesday, June 8, 2016 - 5:02:35 AM - Tibor Szabo Back To Top (41637)

 Hi Koen,

When I'm setting the connection, testing it, I've got an error message: "The remote server returned an error: (403) Forbidden". My document is ".../_vti_bin/ListData.svc.".

The server uses claim-based authentication. How can I connect to the ListData.svc to get it working? (SP 2013, SQLServer 2014, SSDT-BI VS1013)

(on SharePoint with Windows Authentication it works like a charm)




Thursday, May 26, 2016 - 4:27:34 PM - Priyanka Back To Top (41570)


 Trying to use the OData source in a project. 

  How to refer to a particular view of a sharepoint list?

Thursday, April 14, 2016 - 7:12:16 AM - Koen Verbeeck Back To Top (41220)


which issue are you talking about?

Wednesday, April 13, 2016 - 6:48:41 PM - avi Back To Top (41213)


I'm facing the same problem here. Did you resolve the issue?


Tuesday, February 23, 2016 - 11:17:55 PM - raj Back To Top (40764)

Hi Koen,

Yes I did tried executing it in Designer and it is running. But when I used dtexec.exe utility I am receiving error. 

The connection type "ODATA" specified for connection manager "OData Source" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.

Tuesday, February 23, 2016 - 5:08:09 PM - Koen Verbeeck Back To Top (40760)

Hi Dario,

you can certainly read from OData using SSIS. There are also connectors for Dynamics CRM, so that should be possible as well.

Maximum rate of import? Impossible to answer by me. It depends on too many factors:

* speed of the source
* speed of the destination (depends on the connectors as well)
* specification of the machine running the SSIS package: CPU, IO, RAM memory, ...
* network bandwith
* contention on the destination database
* ...

I've seen packages go from 1 million rows per minute to only 10,000 rows per minute.

Tuesday, February 23, 2016 - 5:04:18 PM - Koen Verbeeck Back To Top (40759)

Hi Raj,

did you execute the package on your own computer with dtexec?

Monday, February 22, 2016 - 4:03:22 AM - Raj Back To Top (40742)

Hi Koen,


Sorry for the delay. We are using file system deployment. using DTExec.exe utility to execute .dtsx package. 


Friday, February 19, 2016 - 7:00:20 AM - Dario Back To Top (40730)



just a couple of questions.

My Master is a DataVirtualization JBoss (OData) and Slave i MS CRM Dynamics.

Can i use SSIS as ETL?

What is the max rate of import (Record/sec) for 1:1 integration (OData View -> Entity CRM)?



Friday, January 22, 2016 - 2:30:40 AM - Koen Verbeeck Back To Top (40471)

Hi Raj,


are you using the project deployment model or the package deployment model?
Secondly, where are you trying to execute the package using dtexec? On your own machine?

Thursday, January 21, 2016 - 4:05:22 AM - raj Back To Top (40465)

Hi All,


I have installed 64bit OData source connector and my package is able to pull the data form O365 list in VS2010 developer.

But when I create a batch file and use .DTExec.exe utility to execute the package. I am getting issue with connection manager(Odata Source) as not recognised. Please help

Monday, January 11, 2016 - 9:52:23 AM - Gerald Britton Back To Top (40395)

Trying to use the OData source in a project.  Two problems:


1. How to refer to a particular view of a sharepoint list? (e.g. Customer View)

2. How to select properties whose names have embedded blanks (e.g. Customer Name)

Thursday, December 17, 2015 - 9:57:13 AM - Ken Back To Top (40267)


I think i figured it out, why i have connection issues even after connection test is successfull.

1. Make sure to get your SharePoint structure correct in the sense, Have Collections> List> Sub-title for each item in list.

2. oData doesn't like if there is a item in the list starting with number for instance you named the list item "172 Priority list", oData has problems with this list item. Try to start the list item name with an alphabet.

3. If the list item has to start with number then create a subtitle for each list item and then your actual list item can start with number("172 Priority list").

Issue that i was facing, oData couldn't see the collection/list assoicated because list item's had numbers in the title and resolved it by creating sub-title for the items in the list. This happened in VS 2012(not sure about SharePoint version) and irrespective of 32/64 bit oData maybe it's not a problem with VS 2014 or higher versions of SharePoint.


Friday, December 11, 2015 - 9:24:08 AM - Ken Back To Top (40238)

Thanks for your response Koen but I don't think this has to do with 32/64 bit, in fact I have both now and still the same issue. If this is case why would things work out perfectly with one SharePoint site and not with SharePoint site. I don't believe it has to do with 32/64 bit. Started thinking out of box now..

I think there are some features(such as SQL integration services or reporting services) which are not enabled/not installed on SharePoint server/site

I'm not  a SharePoint admin or guru but something tells me that SQL integration services feature is not installed or not enabled on the server or SharePoint site.

Let me know your thoughts on this.


Thursday, December 10, 2015 - 9:27:25 AM - Koen Verbeeck Back To Top (40232)

Hi Ken;

1) You can check in the program files folder. At the path "Microsoft SQL Server\120\DTS\Connections" you should find a .dll called Microsoft.SqlServer.IntegrationServices.ODataConnectionManager. If it's at the regular program files, you have the x64 version. If it's at the Program Files (x86), you have the 32-bit version. If it's at both locations, you have both versions.

Tuesday, December 8, 2015 - 10:06:33 AM - Ken Back To Top (40215)

Thank for your response Koen.

1. How would I know which version I have installed.

2. Either ways this doesn't work because lets say I have installed 32 bit oDATA then I'm running the project on 32 bit environment by setting 64 bit environment to False then I'm having connection issues when setting up odata source(connection is successful thru connection manager always). Same happens when I run with/thru 64 bit.

3. I'm little surprised how come this was not an issue with other SharePoint site and how come things are different with SharePoint site.

There got be something I'm missing somewhere.

Tuesday, December 8, 2015 - 4:14:06 AM - Koen Verbeeck Back To Top (40211)


which OData source adaptor did you install? The 32-bit one or the 64-bit one? Or both?

If you only installed the 32-bit one, it would explain your problem. SSDT-BI is 32-bits, so it can use the 32-bit OData source in the preview. When you run the package, the 64-bit environment is launched, but there is no 64-bit OData source installed.

You can test this by turning the Run64Bitruntime property off in the project properties.

Monday, December 7, 2015 - 4:39:50 PM - Ken Back To Top (40208)


Hello Melvin,

oData Connection manager: Test connection was successful.

oData Source: [oData Source [91]]: Cannot acquire a managed connection from the run-time connection manager.

I don't understand why I get above after test connection was successful.

Note: I have full control on SharePoint site.

FYI: Again I was able to implement something similar in the past although SharePoint site was different but has access levels are similar.

Please help, there are some access issues on SharePoint site maybe ??


Monday, December 7, 2015 - 2:45:12 AM - Koen Verbeeck Back To Top (40204)


the OData source is listed under Common in the SSIS toolbox, not under sources.
Did you check there? 

Thursday, December 3, 2015 - 2:05:15 AM - Melvin Back To Top (40186)


I've tried installing the OData Source 32bit and 64bit but it doesn't show up in visual studio 2013 as a SSIS toolbox item.

I've got SQL Server 2014 installed the both from the features link you mentioned.

I've obviously restarted the apps/services and computer several times with no success.

Also I've checked the SQL Server install directory and the 3 OData DLL's are installed under the 120 folder.

Not sure what the issue is, any help would be much appreciated.



Tuesday, November 17, 2015 - 10:13:40 AM - Koen Verbeeck Back To Top (39084)

Hi Pritesh Patel,

did you download the OData component from the SQL Server 2014 feature pack?
I just tried and everything went as expected. 

Tuesday, November 17, 2015 - 9:22:20 AM - Koen Verbeeck Back To Top (39081)

Hi Kirk,

I would have to test myself with an OData source with dates, so I'll have to get back to you on that.

What are the errors exactly?



Friday, November 13, 2015 - 5:24:10 PM - Kirk Back To Top (39070)

I'm having trouble getting dates to flow into my SQL Server table using the OData source via an SSIS package.  I've tried to convert.  The string data is working great, but not dates.  Any ideas??

Tuesday, October 27, 2015 - 10:11:05 AM - Pritesh Patel Back To Top (38970)


I am having issues with installing the Odata source on my dev environment. I have SSDT FOR Visual Studio 2013 installed, but when I try to install the 64 bit version of the component, I get a message stating that 1. Either Integration services or SSDT has to be installed . 2. The version of these components has to be SQL Server 2012 (or future SPs).

I am not sure what version of SSDT or SSIS do I need to run this? I am assuming, it would run with SSDT 2013. Any help is appreciated.


Thank you


Monday, August 3, 2015 - 3:52:39 AM - Koen Verbeeck Back To Top (38349)

@Bharat: does the OData work when you browse it for example with Internet Explorer?

Tuesday, July 14, 2015 - 4:22:13 PM - Bharat Patel Back To Top (38207)

Getting the following errors using OData Source pull info from Project Server 2013 SQL server 2012 R1


[OData Source [2]] Error: The OData Source was unable to process the data. An error occured when detect the document payloadkind.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on OData Source returned error code 0xC02090F5.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

Am I do something wrong? Please help

Tuesday, June 23, 2015 - 2:45:09 PM - Koen Verbeeck Back To Top (37998)

@Bharat, you need to install this on the machine where you will be developing the SSIS packages on and on the server that will run those packages.

Tuesday, June 23, 2015 - 1:11:32 PM - Bharat Patel Back To Top (37996)

Good Afternoon,

          Can this run from your local desktop I have SQL Management Studio 2012 installed on my desktop or does it need to be install on the SQL 2012 server? We are trying to extract Project Server 2013 list data and are newbie's at this.

Thank you for any help you can provide. 

Wednesday, March 25, 2015 - 7:04:04 AM - Koen Verbeeck Back To Top (36681)

Hi Michelle,

since you mentioned feature pack, I assume you downloaded the OData Adaptor for SSIS 2014. SSIS 2014 works with Visual Studio 2013, but SSIS 2012 works with Visual Studio 2010/2012.

This means that you need to download and install the SSIS 2012 version of the OData adaptor to get it into VS 2012.

Wednesday, March 25, 2015 - 3:34:36 AM - Michelle Back To Top (36677)



I've followed the install instructions for the 32 bit feature pack, but can't seem to see the odata source in the ssis toolbox. I've installed the 64 bit feature pack and still have the same problem. I copied the dll's to the 64bit and 32bit folders as specified here:

Now I can see the component in visual studio 2013 shell, but still not in visual studio 2012. Any ideas please?



Monday, October 13, 2014 - 2:48:09 AM - Koen Verbeeck Back To Top (34939)

@casey: as I have mentioned in the article, the OData adaptor sometimes behaves erratically. I too had some queries that worked fine in the browser, but did not give the desired result in SSIS.

Friday, October 10, 2014 - 11:20:58 AM - casey Pruden Back To Top (34924)

Great article thanks.  We have been looking for an easy method to get data out of sharepoint 2010.  One thing I noticed is that Lookup fields are ignored.  I have a list with a person Field Manager and even though I expand the field SSIS doesn't seem to pick up on the field eg. I add the query options $select=Manager/LastName&$expand=Manager and no data is returned.  However if I add $select=Dept,Manager/LastName&$expand=Manager  then I see the list of all departments but no Manager field?  Any idea's would be helpful.  If i run the query directly from browser everything returns as expected.

Thursday, September 11, 2014 - 12:03:01 PM - Alex Diaz Back To Top (34498)

Great article. Saved my day because the Codeplex SharePoint list adapter doesn't work with VS2013

Thanks a lot!

Friday, July 25, 2014 - 8:58:12 AM - Koen Verbeeck Back To Top (32881)

@Matt: did you install the 32-bit version?

Wednesday, July 23, 2014 - 4:10:05 PM - Matt Back To Top (32844)

I have installed the SQL 2014 file indicated but the source won't show up in my project.  Any help to get this working?

Monday, July 14, 2014 - 10:35:34 AM - Joost Back To Top (32694)

Nice post!

Thursday, May 8, 2014 - 5:10:11 AM - Nikhil Back To Top (30665)

It is very userful information and this is what I was trying to do now all is set .Thanks

get free sql tips
agree to terms