SSIS OData Source available from Microsoft

A few days ago Microsoft has released an OData Source for SSIS 2012 / 2014. Downloads can be found in the links below:

Microsoft OData Source for Microsoft SQL Server 2012

Microsoft SQL Server 2014 Feature Pack

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.

http://services.odata.org/V3/OData/OData.svc/

Use Windows authentication to make the connection.

With the Odata Version 4 sample, the connection manager generated an error. Version 3 worked.

Click OK

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:

CREATE TABLE [dbo].[ImportProduct](

    [ProductId] [int] NOT NULL,

    [ProductName] [nvarchar](255) NULL,

    [ProductDescription] [nvarchar](255) NULL,

    [ReleaseDate] [datetime] NULL,

    [DiscontinuedDate] [datetime] NULL,

    [Rating] [int] NULL,

    [Price] [decimal](12, 4) NULL,

 CONSTRAINT [PK_ImportProduct] PRIMARY KEY CLUSTERED 

(

    [ProductId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

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 result after running the package: 11 rows are inserted into the SQL table.

Conclusion

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