odata数据绑定
介绍 ( Introduction )
A few days back I was looking at ways to access raw data from within Microsoft Dynamics CRM in an effort to extract the raw data and to place it in our data warehouse. I started to explore utilizing OData and SSIS to pull the necessary data from the cloud to our local warehouse.
几天前，我正在研究从Microsoft Dynamics CRM内部访问原始数据的方法，以提取原始数据并将其放置在我们的数据仓库中。 我开始探索利用OData和SSIS将必要的数据从云中拉到我们的本地仓库中。
Whilst there are known authentication issues between Dynamics CRM and theMicrosoftOData SSISdata source(and thus we could not utilize this access method), I thought it to be so very powerful, that I began looking for other constructive manners in which to utilize the OData Source.
虽然Dynamics CRM和MicrosoftOData SSIS数据源之间存在已知的身份验证问题（因此我们无法利用这种访问方法），我认为它是如此强大，以至于我开始寻找其他利用OData Source的建设性方式。
By now you may be asking what is OData?
现在，您可能会问什么是OData？
David Chappell describes the concept quite well in an article that may be found at the following link.
David Chappell在以下链接中的文章中很好地描述了该概念。
/en-us/data/hh237663.aspx
/zh-CN/data/hh237663.aspx
In the article he states:
他在文章中指出：
解决方案：OData提供什么 (The Solution: What OData Provides)
OData defines an abstract data model and a protocol that let any client access information exposed by any data source. Figure 1 shows some of the most important examples of clients and data sources, illustrating where OData fits in the picture.
OData定义了抽象数据模型和协议，该协议允许任何客户端访问任何数据源公开的信息。 图1显示了一些最重要的客户端和数据源示例，说明了OData在图中的位置。
Courtesy of David Chappell
由David Chappell提供
In today’s “get together” we shall be looking at obtainingweb service based dataand loading this data into SQL Server.
在今天的“聚在一起”中，我们将着眼于获取基于Web服务的数据并将此数据加载到SQL Server中。
In the following “get together”, we shall see how a similar implementation may be utilized to obtain valuable data from your enterprise SharePoint Servers.
在下面的“聚在一起”中，我们将看到如何利用类似的实现方式从您的企业SharePoint Server获取有价值的数据。
Without further ado, let’s get started.
事不宜迟，让我们开始吧。
入门 ( Getting Started )
Prior to jumping into any development, it is necessary to download the Microsoft OData source adapter from the Microsoft website. Should you have already downloaded the adapter, please feel free to jump forward.
在进行任何开发之前，必须从Microsoft网站下载Microsoft OData源适配器。 如果您已经下载了适配器，请随时跳转。
The link to download the control is
下载控件的链接是
/en-us/download/details.aspx?id=42280
/zh-cn/download/details.aspx?id=42280
When installing the adapter we choose the 32 Bit version as SSIS often has a problem dealing with 64-bit processing and yields the ugliest error messages.
安装适配器时，我们选择32位版本，因为SSIS在处理64位处理时经常遇到问题，并产生最丑陋的错误消息。
Once installed, then we are ready to proceed.
安装完成后，我们就可以继续了。
In today’s exercise, we shall be looking at data that originates from the Adventure Worksweb service. In order to access web based services, we must obtain the URL of the “web servicedocument”.
在今天的练习中，我们将研究源自Adventure WorksWeb服务的数据。 为了访问基于Web的服务，我们必须获取“Web服务文档”的URL。
Performing a quick Google search, we locate the “AdventureWorks” OData Feed at the following link.
执行快速的Google搜索时，我们在以下链接中找到“ AdventureWorks” OData Feed。
/releases/view/94064
/releases/view/94064
We are brought to the “code complex” and find the “AdventureWorks” service link highlighted in blue (see above).
我们进入“代码复合体”，找到以蓝色突出显示的“ AdventureWorks”服务链接（见上文）。
爬进去 ( Climbing in )
Opening SQL Server Management Studio we begin by creating a new Integration Service Project.
打开SQL Server Management Studio，我们首先创建一个新的Integration Service项目。
We give our project the name “AdventureWorks” and click OK to continue.
我们将项目命名为“ AdventureWorks”，然后单击“确定”继续。
We find ourselves on our normal Integration Services work surface.
我们发现自己处于正常的Integration Services工作环境中。
We begin by dragging a “Data Flow Task” to our working surface (see below).
我们首先将“数据流任务”拖到工作表面（见下文）。
We now click on the “Data Flow” tab to bring up the “Data Flow” work surface (see below).
现在，我们单击“数据流”选项卡以调出“数据流”工作界面（请参见下文）。
Looking at the screenshot (immediately above) AND assuming that your installation of the OData Source was successful, you will note an OData source “connection” in the “Common” portion of the SSIS toolbox (see above and to the left).
查看屏幕快照（紧接在上方），并假设OData Source的安装成功，您将在SSIS工具箱的“ Common”部分中注意到一个OData Source“连接”（请参见上方和左侧）。
By right-clicking in the “Connection Manager” portion of the work surface and by selecting the “Connections” option, we select the OData Option from the drop down (see below).
通过右键单击工作表面的“连接管理器”部分并选择“连接”选项，我们从下拉列表中选择OData选项（见下文）。
We click “Add” (see above).
我们点击“添加”（见上文）。
The “OData Connection Manager Editor” dialog box is brought into view (see above).
进入“ OData Connection Manager编辑器”对话框（请参见上文）。
We set the “Service Document location”.
我们设置“服务凭证位置”。
/AdventureWorksV3/AdventureWorks.svc
/AdventureWorksV3/AdventureWorks.svc
This “Service Document” for all intents and purposes acts as a gateway/portal to the underlying data. The “Service Document” is in XML format and “tells the OData data source” what data is available and the document acts as an interface between the “OData source” (itself) and the underlying data (see Chappell’s sketch above).
出于所有意图和目的，此“服务文档”充当基础数据的网关/门户。 “服务文档”采用XML格式，并“告诉OData数据源”可用的数据，并且该文档充当“ OData源”（本身）与基础数据之间的接口（请参见上面的Chappell草图）。
In the screenshot above we have entered the location of the Service Document and have successfully tested the connection. We click “OK” to accept and are returned to our work surface (see below).
在上面的屏幕截图中，我们输入了服务文档的位置，并成功测试了连接。 我们单击“确定”接受并返回到我们的工作界面（见下文）。
Having our connection created, we are now in a position to drag an “OData source” onto our working surface (see below).
创建我们的连接后，我们现在可以将“ OData源”拖到工作表面上了（见下文）。
Opening the “OData” source we find ourselves in the “OData Source Editor” (see below).
打开“ OData”源，我们可以在“ OData源编辑器”中找到自己（见下文）。
We select the “AdventureWorksOData” connection as seen above.
如上所述，我们选择“ AdventureWorksOData”连接。
Having accepted this connection, the astute reader will note that the “Collection’ drop down box contains a list of all the “tables” and “views” available to our data source.
接受了这种联系后，精明的读者会注意到，“集合”下拉框包含了我们数据源可用的所有“表”和“视图”的列表。
We shall select the “CompanySales” table (see above).
我们将选择“ CompanySales”表（见上文）。
Clicking on the “Columns” tab (see above) we are able to see which fields we may pull. Further, by clicking on the preview button we are able to preview some of the data returned by the service (see below).
单击“列”选项卡（见上文），我们可以看到我们可以拉出哪些字段。 此外，通过单击预览按钮，我们可以预览服务返回的一些数据（请参见下文）。
Now, the folks at SQLShackFinancial (who have asked us to obtain this data) are only interested in having the data and ONLY for Q1. We set the “Query Options” to
现在，SQLShackFinancial的人员（要求我们获取此数据）仅对拥有数据感兴趣，仅对第一季度感兴趣。 我们将“查询选项”设置为
$filter=OrderYear eq and OrderQtr eq‘Q1’(see below).
$ filter = OrderYear eq 和OrderQtr eq'Q1'（请参见下文）。
We click OK to accept and begin to configure the “OLE DB destination”.
我们单击“确定”接受并开始配置“ OLE DB目标”。
We now drag an “OLE DB destination” control to the work surface (see below).
现在，我们将“ OLE DB目标”控件拖到工作表面上（见下文）。
Opening the “OLE DB Destination” we configure the component to place the data (from the web service) into a new table within the SQLShackFinancial” database (see below).
打开“ OLE DB目标”，我们配置组件以将数据（来自Web服务）放置到“ SQLShackFinancial”数据库中的新表中（请参见下文）。
As always, we select our standard “Connection Manager” to the SQLShackFinancial” database (see above). The “OLE DB Destination Editor” now appears.
与往常一样，我们选择标准的“连接管理器”到SQLShackFinancial数据库（请参见上文）。 现在将出现“ OLE DB目标编辑器”。
We click the “New” button to the right of the “Name of the table or the view” drop-down box. The “Create Table” dialog box now appears. We call our table “ODataCompanySales” (see above).
我们单击“表或视图的名称”下拉框右侧的“新建”按钮。 现在将出现“创建表”对话框。 我们将表称为“ ODataCompanySales”（见上文）。
Clicking on the “Mappings” tab, we map the source fields to the destination fields (see below).
单击“映射”选项卡，我们将源字段映射到目标字段（请参见下文）。
We click OK and OK to exit the dialog boxes and find ourselves back on our work surface (see below).
我们单击“确定”，然后单击“确定”退出对话框，并回到工作台上（参见下文）。
让它运行！ ( Giving it a run! )
Now that we have constructed all that we need to pull the necessary data, we can give our project a “test run”. We click on the “Debug” tab and select ‘Start Debugging” (see below).
现在，我们已经构建了提取必要数据所需的所有内容，我们可以对项目进行“测试运行”。 我们单击“调试”选项卡，然后选择“开始调试”（见下文）。
Processing begins and we find that 8 rows have been extracted (see below).
处理开始，我们发现已提取8行（请参见下文）。
Meanwhile back in SQL Server Management Studio, we find that those data rows were in fact inserted into our table. The rows inserted are for the year and for quarter 1 (see below).
同时，回到SQL Server Management Studio，我们发现这些数据行实际上已插入到我们的表中。 插入的行针对和第一季度（请参见下文）。
结论 ( Conclusions )
Oft times we find that the data that we require is associated with a web service or perhaps located in the cloud. With the advent of Microsoft OData and the OData source for SQL Server Integration Services, importing the data into our databases and data warehouse is now as quick as 1-2-3.
通常，我们发现所需的数据与Web服务相关联，或者可能位于云中。 随着Microsoft OData的出现和SQL Server Integration Services的OData源的出现，现在将数据导入我们的数据库和数据仓库的速度已达到1-2-3。
There are known issues with trying to extract data from Dynamics CRM due to the authentication limitations of the data source HOWEVER this control may be constructively utilized for other more vibrant tasks.
由于数据源的身份验证限制，尝试从Dynamics CRM中提取数据存在一些已知问题，但是此控件可能会被建设性地用于其他更生动的任务。
In the final portion of this series, we shall see how the same techniques may be utilized to pull valuable data from your SharePoint instances.
在本系列的最后一部分，我们将看到如何利用相同的技术从SharePoint实例中提取有价值的数据。
In the interim, happy programming.
<
在此期间，编程愉快。
<
翻译自: /import-data-using-odata-source-for-sql-server-integration-services/
odata数据绑定
