ssis导入xml_使用XML文件配置SSIS包

ssis导入xml

XML configuration file To create an XML configuration file in Business Intelligence Development Studio, for the existing SSIS package, the Control Flow tab needs to be opened, and in the Properties pane, under the XML配置文件要在Business Intelligence Development Studio中为现有SSIS包创建XML配置文件，需要打开“控制流”选项卡，然后在“属性”窗格中的“ Misc menu, expanding the 杂项”菜单下，展开“ Configurations option like shown in the image below 配置”选项，如图所示。下面

Package Configurations Organizer window. In order to create the file, the 程序包配置管理器”窗口。 为了创建文件，必须选中“ Enable package configurations box must be checked. To create the new file, the user needs to click the 启用程序包配置”框。 要创建新文件，用户需要单击“ Add button like shown in the image below 添加”按钮，如下图所示。

Add button will open the 添加按钮将打开 Package Configuration Wizard where the user can choose the type of the configuration file. In the 软件包配置向导 ，用户可以在其中选择配置文件的类型。 在“ Configuration type drop down list there are various types such as: XML configuration file, Environment variable, Registry entry, Parent package variable, and SQL Server type The name of the file must be specified in order to move on through the wizard, which is explicitly requested by the application in the message box at the bottom of the 配置类型”下拉列表中，有多种类型，例如：XML配置文件，环境变量，注册表项，父包变量和SQL Server类型。必须指定文件名才能继续进行向导，应用程序在“程序 Package Configuration Wizard window. Clicking the 包配置向导”窗口底部的消息框中明确请求。 单击 Browse button the name and the location of the file can be specified, like shown in the image below 浏览按钮，可以指定文件的名称和位置，如下图所示

Next button, like shown in the image below 下一个按钮，如下图所示

Edit button in the 程序包配置管理器窗口中的“ Package Configurations Organizer window, or manually change the property value using BIDS. The SSIS package used in this blog imports the 编辑”按钮，或使用BIDS手动更改属性值。 本博客中使用的SSIS包将 Excel worksheet data into the database table. Therefore, the Excel connection and the Excel工作表数据导入数据库表。 因此，已经创建了Excel连接和 SQL Server Destination connection has been created All properties for both of the connections will be in the object list, and also, the properties for all objects from the package, as the SQL Server Destination连接。这两个连接的所有属性都将在对象列表中，并且包中所有对象的属性都将在 Executables or the Executables或 Variables. The property included in the configuration file is checked as shown in the image below. At least one of the box from any of the property/object must be checked in order to move on forward to the next step Using the created XML configuration file, the name of the table in the destination database will be changed from Variables中 。 如下图所示，检查了配置文件中包含的属性。 必须选中任何属性/对象中的至少一个框，才能继续进行下一步使用创建的XML配置文件，目标数据库中表的名称将从 “ExcelData1” to “ ExcelData1”更改为 “ExcelData2”, and the Excel worksheet data will be imported to a new table. Of course, the “ ExcelData2” ，Excel工作表数据将导入到新表中。 当然，在修改文件之前必须正确创建 ExcelData2 table must be properly created before the file has been modified. As shown in the image below, the current value of the selected object is ExcelData2表。 如下图所示，所选对象的当前值为 [dbo].[ExcelData1] [dbo]。[ExcelData1]

Next button will move the process to the next step. This is a summary for the entire process where the name for the configuration must be specified. The name will be automatically updated in the 下一步”按钮会将过程移至下一步。 这是整个过程的摘要，其中必须指定配置名称。 该名称将在“ Preview box In the same box are: type of the file, location, and all the properties previously selected, that will be included in the configuration file. After the configuration name is specified, and all the information from the 预览”框中自动更新。在同一框中：文件类型，位置和所有先前选择的属性，这些属性将包含在配置文件中。 指定配置名称，并检查并确认“ Preview box is being reviewed and confirmed, clicking the 预览”框中的所有信息后，单击“ Finish button will close the 完成”按钮将关闭“ Package Configuration Wizard window, and points to the 软件包配置向导”窗口，并指向“ Package Configuration Organizer window. The created file should be listed in this window. If it is so, the window can be closed 软件包配置管理器”窗口。 创建的文件应在此窗口中列出。 如果是这样，则可以关闭窗口

ExcelData1 table like shown in the image below. The ExcelData1表，如下图所示。 ExcelData2 table is still empty ExcelData2表仍然为空

ExcelData2 table like shown in the image below ExcelData2表，如下图所示。

Configured Value has been changed, the file needs to be saved. An Excel worksheet will be redirected to the new table at the next run of the SSIS package, like shown in the image below 配置值”后，需要保存文件。 下一次运行SSIS包时，Excel工作表将被重定向到新表，如下图所示。

有用的资源： (Useful resources:)

• Package configurations套餐配置
• Adding the configuration file to an existing package将配置文件添加到现有软件包
• Deploying package with XML configuration使用XML配置部署程序包

翻译自: https://www.sqlshack.com/using-xml-file-configure-ssis-package/

ssis导入xml