ssis导出数据性能

In this article, I am going to explain how we can split the data within the excel file and upload it to the tables created on the Azure SQL database.

在本文中，我将解释如何在excel文件中拆分数据并将其上传到在Azure SQL数据库上创建的表中。

To demonstrate the process, I have created two tables in the “AzureDatabase” database, which is hosted on the Azure SQL Server instance. The names of the tables are “AmericanCountries” and “AsianCountries.” Following is the Create Table script.

为了演示该过程，我在“ AzureDatabase ”数据库中创建了两个表，该数据库托管在Azure SQL Server实例上。 该表的名称为“ AmericanCountries ”和“ AsianCountries” 。 以下是“创建表”脚本。

CREATE TABLE ASIANCOUNTRIES(COUNTRYID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED ,COUNTRYNAME VARCHAR(300),COUNTRYTYPE VARCHAR(200),LATESTRECORDEDPOPULATION INT,CONTINENT VARCHAR(100),REGION VARCHAR(50),SUBREGION VARCHAR(100),)GO    CREATE TABLE AMERICANCOUNTRIES(COUNTRYID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED ,COUNTRYNAME VARCHAR(300),COUNTRYTYPE VARCHAR(200),LATESTRECORDEDPOPULATION INT,CONTINENT VARCHAR(100),REGION VARCHAR(50),SUBREGION VARCHAR(100),)GO

I have created an excel file named “Application_Countries“, (see attached below), that has details of countries. To export data to multiple tables, we are going to do the following:

我创建了一个名为“ Application_Countries ”的excel文件（请参见下面的附件），其中包含国家/地区的详细信息。 要将数据导出到多个表，我们将执行以下操作：

1. AmericanCountries” table AmericanCountries ”表中
2. AsianCountries” table AsianCountries ”表中

To filter the data, we are going to use

为了过滤数据，我们将使用

1. The conditional split component of the SQL Server integration services
   SQL Server集成服务的条件拆分组件
2. Excel file as a Source
   Excel文件作为源
3. ADO.NET as a destination
   ADO.NET作为目标

First, let’s configure the Excel Source.

首先，让我们配置Excel Source。

配置Excel源 (Configure Excel Source)

First, Open the SQL Server data tools Create a new SSIS project named “ExportData.sln.” In the SSIS package, Drag and drop the “Data Flow Task” in the Control Flow window and rename it to “Export Counties to Azure SQL.” See the following image:

首先，打开SQL Server数据工具创建一个名为“ ExportData.sln ”的新SSIS项目。 在SSIS包中，将“ 数据流任务 ”拖放到“ 控制流”窗口中，并将其重命名为“ 将县出口到Azure SQL”。 ”，请参见下图：

Double-click on “Export Countries to Azure SQL.” On the “Data Flow” window (Screen 1), Drag Excel Source and rename it to Countries. Double click on “Countries.” On the “Excel Source Editor” dialog box (Screen 2), Click on New. On Excel Connection Manager (Screen 3), click on Browse to locate the file and click ok to close the Excel Connection Manager dialog box and click OK to close the Excel Source editor dialog box. See the following image:

双击“ 将国家/地区导出到Azure SQL。 在“ 数据流 ”窗口（屏幕1）上 ，拖动Excel Source并将其重命名为Countrys 。 双击“国家”。 在“ Excel Source Editor”对话框（屏幕2）上 ，单击“ New”。 在Excel Connection Manager （屏幕3）上 ，单击“浏览”以找到文件，然后单击“确定”关闭Excel Connection Manager对话框，然后单击“确定”关闭Excel Source编辑器对话框。 见下图：

Select “Table or view” from the Data access mode drop-down box and select Sheet1$ in the “Name of the excel sheet” drop-down box. See the following image:

从“ 数据访问方式”下拉框中选择“ 表或视图 ”，然后在“ Excel表格的名称 ”下拉框中选择Sheet1 $ 。 见下图：

Now, to export data in different tables based on the condition, we must use conditional Split transformation.

现在，要基于条件将数据导出到不同的表中，我们必须使用条件式Split转换 。

配置条件拆分组件 (Configure the conditional split component)

To configure conditional split transformation, drag and drop the Conditional Split transformation from SSIS data tools to the Data Flow Task window. See the following image:

要配置条件拆分转换，请将“ 条件拆分”转换从SSIS数据工具拖放到“数据流任务”窗口。 见下图：

As I mentioned, we want to copy the data of the “Asia” continent to the “AsianCountries” table and data of the “North America” continent to the “AmericanCountries” table. To do that, we will configure the conditional split transformation as follows:

如前所述，我们想将“ 亚洲 ”大陆的数据复制到“ AsianCountries ”表中，并将“ 北美 ”大陆的数据复制到“ AmericanCountries ”表中。 为此，我们将配置条件拆分转换，如下所示：

1. Asia,” then the data will be stored in the AsianCountries table Asia ”，则数据将存储在AsianCountries表中
   1. Case 1: [Continent]==”Asia” 情况1：[大陆] ==“亚洲”
2. America,” then the data will be stored in the America ”，那么数据将存储在AmericanCountries table AmericanCountries表中
   1. Case 2: 案例2： [Continent] == “North America” [大陆] ==“北美”

To configure it, double-click on “Divide the data based on the condition“. Conditional Split transformation editor opens. In the editor, configure the condition cases as explained above. See the following image. Click OK to close the editor.

要配置它，双击“ 根据条件划分数据 ”。 将打开条件拆分转换编辑器。 在编辑器中，如上所述配置条件情况。 参见下图。 单击确定关闭编辑器。

The Conditional split transformation is configured, let us configure the ADO.NET destination now.

配置了条件拆分转换，现在让我们配置ADO.NET目标。

配置ADO.NET目标 (Configure ADO.NET destination)

To configure the ADO.NET, drag and drop the two ADO.Net destination components in the data flow window. Rename the first component to “AsianCountries” and second component to “AmericanCountries.” See the following image.

要配置ADO.NET，请将两个ADO.Net目标组件拖放到数据流窗口中。 将第一个组件重命名为“ AsianCountries ”，将第二个组件重命名为“ AmericanCountries” 。 参见下图。

Now the process of configuring ADO.Net destination is the same; hence I will explain the process to configure the ADO.Net component AsianCountries.

现在，配置ADO.Net目标的过程相同。 因此，我将解释配置ADO.Net组件AsianCountries的过程 。

To do that, double click on it.

为此，请双击它。

As you can see, when you double click on it, you will receive an error that says we did not provide any input columns to the ADO.Net component. We can ignore the error and click on “Yes.” A dialog box “ADO.Net destination editor” opens (Screen 1). On the “ADO.NET Destination Editor” dialog box, click on New. The dialog box, “Configure ADO.Net Connection Manager,” opens (Screen 2). Click on New on it. See the following image:

如您所见，当您双击它时，您将收到一条错误消息，指出我们没有为ADO.Net组件提供任何输入列。 我们可以忽略该错误，然后单击“ 是。 将打开一个对话框“ ADO.Net目标编辑器 ” （屏幕1） 。 在“ ADO.NET目标编辑器”对话框上，单击“ 新建” 。 对话框“ 配置ADO.Net连接管理器 ”打开（屏幕2） 。 单击“ 新建 ”。 见下图：

Another dialog box, “Connection Manager” opens. In the Dialog box, Provide the Azure SQL Server name in the “Server Name” text box. You can find the server name on the Azure Portal. Login to Azure Portal Open Azure SQL Database resource pool page.

另一个对话框“ Connection Manager ”打开。 在对话框的“服务器名称”文本框中，提供Azure SQL Server名称。 您可以在Azure门户上找到服务器名称。 登录到Azure门户打开“ Azure SQL数据库”资源池页面。

In the authentication drop-down box, select “SQL Server Authentication.” Enter the user name and password, which is used to connect the Azure SQL Database. Select the database name from the “select or enter database name” drop-down box. See the following image.

在身份验证下拉框中，选择“ SQL Server身份验证”。 输入用于连接Azure SQL数据库的用户名和密码。 从“选择或输入数据库名称”下拉框中选择数据库名称。 参见下图。

Once the connection is configured, Click OK on Test connection. You will see the following error message.

配置连接后，在“测试连接”上单击“确定”。 您将看到以下错误消息。

Error message:

错误信息：

Test connection failed because of an error in initializing provider. Cannot open server ‘companyemployees’ requested by the login. Client with IP address ‘43.228.96.58’ is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.

由于初始化提供程序时发生错误，测试连接失败。 无法打开登录名请求的服务器“ companyemployees”。 IP地址为“ 43.228.96.58”的客户端不允许访问服务器。 若要启用访问，请使用Windows Azure管理门户或在主数据库上运行sp_set_firewall_rule为该IP地址或地址范围创建防火墙规则。 此更改最多可能需要五分钟才能生效。

The error message clearly says that we must add the IP of the server in the Azure firewall rule to allow the incoming connections. You can configure it from the “Firewall settings” page of the Azure portal. To add the client IP, open the Firewall settings page and click on “Add client IP” It automatically adds the IP address. You can also provide the range of the IP address in “Start IP” and “End IP” text box. See the following image:

该错误消息清楚地表明，我们必须在Azure防火墙规则中添加服务器的IP，以允许传入连接。 您可以从Azure门户的“ 防火墙设置 ”页面进行配置。 要添加客户端IP，请打开防火墙设置页面，然后单击“添加客户端IP”。它将自动添加IP地址。 您也可以在“起始IP”和“结束IP”文本框中提供IP地址的范围。 见下图：

Once the firewall has been configured, click on the “Test Connection” button. The connection will be established successfully.

配置防火墙后，单击“测试连接”按钮。 连接将成功建立。

On the “ADO.Net destination editor” dialog box, select “AsianCountries” from the table or view name drop-down box. See the following image:

在“ ADO.Net目标编辑器”对话框上，从表或视图名称下拉框中选择“ AsianCountries ”。 见下图：

Similarly, configure the “AmericanCountries” ADO.Net destination editor. The only difference is that you must select “dbo.AmericanCountries” from Use a table or view drop-down box. See the following image.

同样，配置“ AmericanCountries ” ADO.Net目标编辑器。 唯一的区别是，您必须从“使用表或视图”下拉框中选择“ dbo.AmericanCountries ”。 参见下图。

Now to configure the data path, drag the blue data path arrow from the Divide the data based on the condition to the AsianCountries ADO.Net destination. When we connect the data path to the ADO.Net destination, a dialog box will appear, which will allow us to choose the output. Using it, we can determine the destination where we want to direct the output. The first output of the conditional split will be directed to the “AsianCountries“, hence choose “Asia” from the “Input Output Selection” dialog box. See the following image:

现在，要配置数据路径，请将蓝色的数据路径箭头从基于条件划分数据的角度拖动到AsianCountries ADO.Net目标位置。 当我们将数据路径连接到ADO.Net目标时，将出现一个对话框，允许我们选择输出。 使用它，我们可以确定要将输出定向到的目的地。 条件拆分的第一个输出将被定向到“ AsianCountries ”，因此从“ Input Output Selection ”对话框中选择“ Asia”。 见下图：

The second output will be exported to the AmericanCountries table hence select “America” from the Output drop-down box. See the following image:

第二个输出将导出到AmericanCountries表，因此从“ 输出”下拉框中选择“ America ”。 见下图：

The entire Data Flow Task looks like the following image:

整个数据流任务如下图所示：

Now click on Execute to run the package. If package executes successfully, it should look like the following image:

现在单击执行以运行该程序包。 如果包成功执行，则它应如下图所示：

You can verify the data by querying the Azure SQL database. Execute the following queries to review the output:

您可以通过查询Azure SQL数据库来验证数据。 执行以下查询以查看输出：

SELECT * FROM AMERICANCOUNTRIESGOSELECT * FROM AsianCountriesGO

The following screenshot shows the output of the AsianCountries table:

以下屏幕截图显示了AsianCountries表的输出：

And below is the output of the AmericanCountries table.

下面是AmericanCountries表的输出。

摘要 (Summary)

In this article, I have explained how can we use the SSIS package to split the data within the excel file and upload it to the tables created in the Azure SQL database.

在本文中，我已经解释了如何使用SSIS包在excel文件中拆分数据并将其上传到Azure SQL数据库中创建的表中。

翻译自:

ssis导出数据性能