Using the New OData Source in SQL Server Integration Services. Problem. 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 Share. Point lists. This tip will walk you through the installation and configuration of this brand new component. Solution. 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 Share. Point, but also by other companies like SAP and e. Bay. This protocol is recommended for the Open Government initiative. I've searched around, but haven't been able to find anyone else with this same question. I'm working with SQL Server (2008 R2). Let's say I have the following three rows of data coming back from my. Before you install the Microsoft SQL Server Reporting Services extensions, you must complete the following procedures. Verify that you have the required permissions to install the Reporting Services extensions To. When configuring the database engine, make sure you select Mixed Mode (SQL Server authentication and Windows authentication) and define a password for the SQL Server system administrator (sa) account. You also need to specify. Development resources, articles, tutorials, samples, codes and tools for.Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc. For more information, see the related Wikipedia article. One of the most anticipated uses of this source is the ability to read Share. Point lists. Since those lists are accessible through OData, this component now provides a native built- in method to draw data from Share. Point, while previously you had to rely on 3rd party products such as the Share. SQL Server Reporting services provides a decent reporting framework, and in 2008 release of SQL server, it is even better than previous versions. SSRS allows you to use many different types of data sources in your reports, but. Visual display of SQL Server jobs along with other SQL Server events across the enterprise Event Manager for SQL Server. Visual display of SQL Agent jobs along with other events across the enterprise; Alerting and Response. Attunity SQL Server-CDC for SSIS efficiently captures and replicates data changes in databases. Point 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 2. The 2. 01. 2 version is a separate download, while the 2. SQL Server 2. 01. Feature Pack. You have the option to download a 3. Since SSDTBI (the Visual Studio shell/templates for developing business intelligence projects) is a 3. The installation itself is very straightforward. First you have the 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. 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. Note: If you have a 6. Otherwise you are forced to run the package in 3. Reading From an OData Source. Let's put the new source to work. First of all we are going to read from a Share. Point List. These lists can be exposed as an OData feed, so they are the perfect guinea pigs for our new source. In my Share. Point site, I have a list called Olympic Disciplines, with three columns that interest me: Discipline. ID, Discipline and Sport. 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. Clicking on New.. For reading Share. Point Lists, it takes the following format: http: //my. Share. Point. Server/my. Site/. Listdata is not the name of some Share. Point list. Windows Authentication can be used or you supply a username and password. If the Share. Point 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 Share. Point list you wish to read from. In our case, we need to select Olympic. Disciplines. At the bottom of the editor, you get a preview of the full URL of the OData resource you are going to read from. At this point, you can click on Preview.. You might notice there are a lot of metadata columns that come along with the Share. Point list. We'll do something about that later on. The Error Output is the same as in any other data flow transformation. 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 SSISThis 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 http: //services. Northwind/Northwind. First we need to create a new connection manager. Since it is public, you don't need to specify a user name and password. We are going to use the following resource path: Customers('ALFKI')/Orders(1. Order. This tells the OData protocol to retrieve the order details of order #1. ID 'ALFKI'. 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. The strange part is that those resource paths do work when used directly in some browsers, such as Chrome, but not in Internet Explorer. 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=Discipline. ID,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. 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. Collection. Name: the name of the collection chosen in the dropdown menu. Although sometimes a text stream is used to import string data (see next paragraph). This is the only property that is not configurable through the GUI. All properties, except Default. String. Length, 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. It can extract data from publicly available OData feeds, but also very easily from Share. Point 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. Last Update: 5/7/2. About the author. Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
January 2017
Categories |