A few days ago Microsoft has released an OData Source for SSIS 2012 / 2014. Downloads can be found in the links below:
Documentation is available in the Books Online OData Connection Manager
Currently I am using SQL 2008 and 2012. There is no OData Source for SQL 2008. I’ve downloaded the OData Source for SQL 2012. Installed the msi file on my SQL test server and on my SQL Development PC with VS 2012.
Add an Odata Source to SSIS package in VS 2012
In a solution with the Project deployment model. Open the the Solution explorer, right click on Connection Manager, select New Connection Manager.
Select ODATA, Click on Add…
The OData Connection Manager Appears
For Service document location I have used the sample sources from odata.org.
Use Windows authentication to make the connection.
With the Odata Version 4 sample, the connection manager generated an error. Version 3 worked.
The connection managers Tab shows the OData Source
Use the OData Source in a Data Flow task
IN the SSIS package with the OData Source, Create a new Data Flow task.
In the Data Flow Task add an OData Source. In the SSIS Toolbox the OData Source can be found under the Common tools, not under the Other Sources.
I cannot help but notice the return of the coloured icons (in contrast to the VS 2012 B&W scheme)
Drag the OData Source onto the Data Flow Task surface.
Edit the OData Source.
OData connection manager: OData Source
Use collection or resource path: Collection
Collection: Products (in the dropdown you will see all available collections (Products, Categories, Suppliers, etc…)
Click on preview to see the Products data (up to 20 first)
In the OData Source Editor look at the columns
Text columns are in Unicode text stream format, Dates are in database timestamp format.
For testing purposes I created a SQL table to accept the products:
In the SSIS Dataflow task I can now convert the data from the OData source to the format of the destination table in SQL server.
Next, an ADO.NET Destination is added.
The SSIS OData Source is a nice addition to SQL SSIS 2012 an d2014. It works with OData version 3.0. Now all we need is an OData Destination component.
In my next post I have tried adding query options to SSIS OData source