提取SQL Azure的联邦数据库数据仓库与SSIS [英] Extract SQL Azure Federated Database to Data Warehouse with SSIS

问题描述

我想我们的生产数据传输到数据仓库进行报告。我试着从 SSIS的导入到联合会一节Azure和混​​合以下数据移动，但我需要把数据从我的联合会移动到数据仓库中。我还发现在 SQL Server的中央一个很好的资源，但我还是能'不像是会弹出联合表中的数据流向导。我也不能在源向导中的ODBC（需要一个SQL Azure的数据库连接型）SQL命令添加使用FedDB声明。

I am trying to transfer our production data to a data warehouse for reporting purposes. I've tried following the "Importing to Federations" section from the SSIS for Azure and Hybrid Data Movement, but I need to move data from my federations to the data warehouse. I've also found a good resource at SQL Server Central, but I still can't seem to bring up the federated tables in the data flow wizards. Nor can I add a Use FedDB statement in a SQL command in the ODBC (connection type needed for a SQL Azure DB) source wizard.

推荐答案

我建了一个原型包，根据我的垂直分片的假设（在多个实例相同的Schema $ P $垫）

I built out a prototype package, based on my assumption of a Vertical sharding (same schema spread across multiple instances)

什么你需要做的就是创建一个ADO.NET连接管理器和作为供应商，选择.NET提供\\ ODBC数据提供者。

What you'll want to do is create an ADO.NET Connection Manager and as the Provider, select ".Net Providers\Odbc Data Provider."

连接字符串将类似于以下。正如你所提供的第一个链接显示，确定你已授权的IP地址和您指定的数据库

The connection string will look something like the below. As the first link you provided indicates, be certain that you have authorized the IP and that you specify the Database

Driver={SQL Server Native Client 11.0};Server=tcp: abcdefghi.database.windows.net;Database=romulus;

控制流程

Control flow

我有一个 Foreach循环容器的设置，使我可以通过我的联盟所有的实例不胜枚举。每次通过循环生成的连接字符串当前实例。我分配一个成String类型的变量， SourceConnectionString 。

I have a Foreach Loop Container set up so that I can enumerate through all the instances in my federation. Each pass through the loop generates the connection string to the current instance. I assign that into a Variable, SourceConnectionString of type String.

然后我有一个防爆pression对ADO.NET连接管理器设置设定的ConnectionString 属性 @ [用户:: SourceConnectionString ] 。这将确保我们的连接枚举过程中实际的修改的

I then have an Expression set on ADO.NET Connection Manager to set the ConnectionString property to @[User::SourceConnectionString]. This will ensure that our connection actually changes during enumeration.

一个数据流通过保持对周围的源和目标的元数据标签严格源于它的性能。您将要为每个表创建你需要抗衡的数据流。有策略运行多个数据流在这里我不解决并行。我敢肯定，安迪·伦纳德覆盖在他的阶梯，你已经发现集成服务系列。

A Data Flows derives its performance by keeping strict tabs on the metadata surrounding the source and destination. You will want to create a data flow per table you need to contend with. There are strategies for running multiple data flows in parallel which I am not addressing here. I'm sure Andy Leonard covers it in his Stairway to Integration Services series that you've already found.

当你链接的SSC文章中看到我已经结构化矿多

I've structured mine much as you see in the linked SSC article

您有源部件基本上任何OLE DB或ADO.NET组件。因为我们正在使用Azure的工作，我们需要的ADO NET源的组成部分。

You have for source components basically either OLE DB or an ADO.NET component. Since we're working with Azure, we'll need the "ADO NET Source" component.

查找组件可以使用OLE DB连接管理器或缓存连接管理器。既然你推到上premises实例（在我的截图拼错了），您可以使用OLE DB连接管理器来处理您的查询。

Lookup Components can use an OLE DB Connection Manager or a Cache Connection Manager. Since you are pushing to an on premises (misspelled in my screenshot) instance, you can use an OLE DB Connection Manager to handle your lookups.

说真的，除了源，并通过联邦枚举，但这个答案，什么是文章中差别很小的。

Really, except for the source and the enumeration through the federation, there's very little difference between this answer and what's in the article.

这篇关于提取SQL Azure的联邦数据库数据仓库与SSIS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！