SQL Server Management Studio 17.3中的新增功能； 导入平面文件向导和XEvent Profiler

SQL Server Management Studio is an integrated graphical user interface tool that is used to perform wide range of administration and development tasks on the SQL Server instances, hosted locally on the user machine, remotely on a Windows or Linux server or on SQL Azure or Azure Data warehouse instances hosted in the cloud. You can use SQL Server Management Studio to easily administrate, develop, deploy, configure and monitor the SQL Server instances.

SQL Server Management Studio是一个集成的图形用户界面工具，用于在SQL Server实例上执行各种管理和开发任务，这些实例在用户计算机上本地承载，在Windows或Linux服务器或SQL Azure或Azure Data上远程承载云中托管的仓库实例。 您可以使用SQL Server Management Studio轻松管理，开发，部署，配置和监视SQL Server实例。

In my previous article, What’s new in SQL Server Management Studio 17.2; Authentication methods, scripting options and more, we discussed deeply the new features and enhancements that are included in the SQL Server management Studio 17.2 release, and mentioned that starting from SQL Server 2017 version, SQL Server Management Studio starts taking its own versioning track, that starts with 17.X rather than taking the same SQL Server Engine version number for SQL Server 2017, which is 14.0.

在我以前的文章中， SQL Server Management Studio 17.2的新增功能； 身份验证方法，脚本选项等 ，我们深入讨论了SQL Server Management Studio 17.2发行版中包含的新功能和增强功能，并提到从SQL Server 2017版本开始，SQL Server Management Studio开始采用自己的版本控制轨道，即以17.X开头，而不是为SQL Server 2017使用相同SQL Server Engine版本号14.0。

SQL Server Management Studio 17.3 is released now, with build number 14.0.17199.0. In this new build, Microsoft updated the SQL Server Management Studio by fixing a number of bugs in the previous SSMS versions, and added two new features to the current version.

SQL Server Management Studio 17.3现在发布，内部版本号14.0.17199.0。 在此新版本中，Microsoft通过修复以前的SSMS版本中的许多错误来更新了SQL Server Management Studio，并在当前版本中添加了两个新功能。

After downloading the SSMS 17.3 from the Microsoft Download website, and installing it to your machine, as a new SSMS side by side with the old versions already installed in the machine, as in the simple installation window below:

从Microsoft下载网站下载SSMS 17.3并将其安装到计算机后，作为新的SSMS与计算机中已经安装的旧版本并排，如下面的简单安装窗口所示：

You can browse the current version number, to make sure that you are using the latest SSMS version, from the About choice of the Help menu as shown below:

您可以从“ 帮助”菜单的“ 关于”选择中浏览当前版本号，以确保您正在使用最新的SSMS版本，如下所示：

A large number of reported bugs in the previous SQL Server Management Studio are fixed in the SSMS 17.3 version. Some of these important fixed bugs include:

SSMS 17.3版本修复了以前SQL Server Management Studio中大量报告的错误。 这些重要的固定错误包括：

• Watch Live Data” when the default database is not master. 监视实时数据 ”中的问题。
• Job Activity Monitor. 作业活动监视器 ”中禁用的作业的图标不一致。
• An issue with the “Generate database script” that is not working when the user has a paused DW database on the server.
  用户在服务器上拥有暂停的DW数据库时，“生成数据库脚本”问题不起作用。
• Custom date range in the “Top Resource Consuming Queries” or “Regressed Queries” graphical reports for Query Store, where the report will ignore the custom date range input and returns information for the past 24 hours instead. 自定义日期范围时出现的问题，其中该报告将忽略自定义日期范围输入并返回过去24小时的信息。
• Match Script Settings to Source” option is set to True, the resulting comments in the script are misleading. 脚本设置与源代码匹配 ”选项设置为True，但是脚本中的结果注释会产生误导。
• Scripting for “Alter” or “Execute” on some database objects is not allowed when connected to an Azure SQL database. 编写脚本 “ Alter”或“ Execute”。
• Intellisense issue when working against Azure SQL databases.
  使用Azure SQL数据库时出现Intellisense问题。
• Linux AG when the primary is on Windows is not possible. Linux AG 。
• Out of Memory” errors when running queries in SSMS. 内存不足 ”错误。
• SQL Server Profiler is not working with SQL Server 2005.
  SQL Server Profiler不适用于SQL Server 2005。
• Activity Monitor is not working when running the SQL Server instance on Linux.
  在Linux上运行SQL Server实例时，活动监视器无法正常工作。

In addition to the enhancements that are performed and the bugs that are fixed in the SSMS 17.3 version, two new features are added to that SQL Server Management Studio release include:

除了执行的增强功能和SSMS 17.3版本中修复的错误外，SQL Server Management Studio发行版还添加了两个新功能：

• The Import Flat File wizard that provides an easy way with the minimal required effort to import CSV or TXT files.
  导入平面文件向导提供了一种简便的方法，只需最少的工作即可导入CSV或TXT文件。
• The XEvent Profiler node in the SSMS Object Explorer.
  SSMS对象资源管理器中的XEvent Profiler节点。

In this article, we will go through these two new features in details.

在本文中，我们将详细介绍这两个新功能。

导入平面文件向导 (Import Flat File Wizard)

The Import Data wizard, existing in the SQL Server Management Studio, provides us with a wide range of import capabilities, by configuring the different available options that are required to build the SQL Server Integration Service package. However, in order to import files with a complex row, semi-structured data structure, such as the flat files, it is better to use a simple task that requires the minimal user configuration effort.

SQL Server Management Studio中存在的“导入数据”向导通过配置构建SQL Server集成服务包所需的各种可用选项，为我们提供了广泛的导入功能。 但是，为了导入具有复杂行，半结构化数据结构的文件（例如平面文件），最好使用需要最少的用户配置工作的简单任务。

The new Import Flat File wizard provides us with a simple way to copy data from a specified flat file, with CSV and TXT extensions, to a destination table, that we used to perform using the Import Data wizard, reducing the amount of effort required from the user to import the complex flat file structure. The Import Flat File wizard simplifies the flat files import process using an intelligent framework known as Program Synthesis using Examples, also called PROSE SDK. The PROSE technology identifies the input file structure by analyzing the file’s data pattern to specify the column names, types and delimiters, minimizing the knowledge required and effort performed by the user to import the flat file. You only need to provide the wizard with the input flat file and a destination table name. The Import Flat File wizard can work when connecting to SQL Server 2005 instance and higher.

全新的“导入平面文件”向导为我们提供了一种简单的方法，可以将数据从具有CSV和TXT扩展名的指定平面文件复制到目标表中，该表以前是使用“导入数据”向导执行的，从而减少了工作量用户导入复杂的平面文件结构。 “导入平面文件”向导使用称为示例的“程序综合”（也称为PROSE SDK）的智能框架简化了平面文件的导入过程。 PROSE技术通过分析文件的数据模式以指定列名，类型和定界符，从而最大程度地减少了用户导入平面文件所需的知识和工作量，从而确定了输入文件的结构。 您只需要向向导提供输入平面文件和目标表名称。 连接到SQL Server 2005或更高版本实例时，“导入平面文件”向导可以工作。

The Import Flat File wizard can be accessed by going through the following steps:

可以通过执行以下步骤来访问“导入平面文件”向导：

• Connect to the SQL Server instance using the SQL Server Management Studio.
  使用SQL Server Management Studio连接到SQL Server实例。
• Databases node and select the database to which you need to import the flat file data. 数据库节点，然后选择要将平面文件数据导入到的数据库。
• Tasks and choose 任务”，然后选择“ Import Flat File option as shown below: 导入平面文件”选项，如下所示：

The first page that will appear after choosing the Import Flat File option is the Introduction page. This page will display a brief description for the Import Flat File process. If you do not want to see it again, click on the Do not show this page again checkbox then click Next:

选择“导入平面文件”选项后将显示的第一页是“简介”页面。 此页面将显示导入平面文件过程的简短描述。 如果您不想再次看到它，请单击“不再显示此页面”复选框，然后单击“ 下一步” ：

As the name indicates, the Specify Input File window allows you to choose the flat file, with .csv and .txt extensions, that you manage to import, by clicking on the Browse button, and a unique name for the new table, to which that data will be imported. Specify both the flat file location and the new table name then click Next:

顾名思义，“指定输入文件”窗口允许您通过单击“ 浏览”按钮以及新表的唯一名称来选择要导入的扩展名为.csv和.txt的平面文件。该数据将被导入。 指定平面文件位置和新表名，然后单击下一步 ：

After specifying the input flat file, the wizard will analyze the input file structure and generate a preview that allows you to view the first 50 rows of the flat file in a friendly shape. If you find that everything looks good and there is no problem, click Next to continue:

指定输入平面文件后，向导将分析输入文件的结构并生成预览，使您能够以友好的形状查看平面文件的前50行。 如果您发现一切看起来不错，并且没有问题，请单击“ 下一步”继续：

The next step is modifying the schema of the new table, to which the flat file data will be imported. You need to have a deep look at the name of the columns to meet you naming convention, the columns data types to be suitable for the inserted data, the column or columns that will participate in the Primary Key constraint and if these columns will allow inserting NULL values or not. After checking the columns properties and performing the changes that meet your requirements click Next to continue:

下一步是修改新表的架构，将向其中导入平面文件数据。 您需要深入了解列的名称以满足命名约定，适合插入数据的列数据类型，将参与主键约束的一个或多个列以及这些列是否允许插入是否为NULL值。 检查列属性并执行符合您要求的更改后，单击下一步继续：

In the Summary page, you can review your current configurations. If there is any change required, you can go back to the previous configurations. If you are OK with all configurations, click Finish to start the import process:

在“摘要”页面中，您可以查看当前配置。 如果需要任何更改，则可以返回到先前的配置。 如果您对所有配置都满意，请单击“ 完成”以开始导入过程：

The Result page shows whether the import process failed or was completed successfully. If it failed, you need to review the input flat file or review your configurations for the reported error:

“结果”页面显示导入过程是失败还是成功完成。 如果失败，则需要检查输入的平面文件或检查配置是否报告了错误：

Expand the Tables node under your database, you will find the newly created table with the schema specified in the Import Flat File wizard as shown below:

展开数据库下的Tables节点，您将找到具有在Import Flat File向导中指定的架构的新创建的表，如下所示：

You can also check that the imported data is same as the flat file data previewed within the Import Flat File wizard by performing the SELECT statement below on the created table:

您还可以通过在创建的表上执行以下SELECT语句，检查导入的数据是否与“导入平面文件”向导中预览的平面文件数据相同：

XEvent事件探查器 (XEvent Profiler)

SQL Server Extended Events were introduced the first time in SQL Server 2008 as an alternative event handling system to replace the SQL Profiler tool, due to its impact to the SQL Server instance performance. Although the Extended Events provide a flexible definition and views, it still suffers from the lack of a quick view capability that is available in the SQL Profiler.

由于SQL Server扩展事件会影响SQL Server实例性能，因此它作为替代事件处理系统在SQL Server 2008中首次引入，以替代SQL Profiler工具。 尽管扩展事件提供了灵活的定义和视图，但是它仍然缺少SQL Profiler中提供的快速查看功能。

The XEvent Profiler is a new customizable feature, integrated into the SQL Server Management Studio 17.3 version, which provides a quick live streaming view for the diagnostics extended event. The XEvent Profiler differs from the SQL Profiler in that it is integrated into the SQL Server Management Studio on the top of the SQL Server scalable Extended Events technology. In addition, the customizable session that is created to view the Extended events is less intensive on the SQL Server instance and less resource consumer than the SQL trace session created using the SQL Profiler. The result displayed in the XEvent Profiler can be also shared as a .viewsetting file. The XEvent Profiler can work when connecting to the SQL Server versions that support the XEvents.

XEvent Profiler是一项新的可自定义功能，已集成到SQL Server Management Studio 17.3版本中，该功能为诊断扩展事件提供了快速的实时流视图。 XEvent Profiler与SQL Profiler的不同之处在于，它已集成到SQL Server可扩展扩展事件技术顶部SQL Server Management Studio中。 此外，与使用SQL事件探查器创建SQL跟踪会话相比，为查看扩展事件而创建的可自定义会话在SQL Server实例上的强度较低，并且资源消耗较少。 XEvent Profiler中显示的结果也可以作为.viewsetting文件共享。 连接到支持XEventSQL Server版本时，XEvent Profiler可以工作。

To access the XEvent Profiler, connect to the SQL Server instance using the SQL Server Management Studio. In the Object Explorer, you will find the new extendable XE Profiler node under the SQL Server Agent node as shown below:

要访问XEvent Profiler，请使用SQL Server Management Studio连接到SQL Server实例。 在对象资源管理器中，您将在SQL Server代理节点下找到新的可扩展XE Profiler节点，如下所示：

When you expand the XE Profiler node, you will find two predefined session templates: Standard and TSQL, with no available option to add extra ones by the user. From these two sessions, double-clicking on them, you will create a new XEvent dedicated session; QuickSessionStandard and QuickSessionTSQL XEvent session accordingly, and be able to start it and open the Live Data View directly in the SSMS, from where you can watch the SQL Server Extended events for troubleshooting purposes with no need for any extra configuration.

展开XE Profiler节点时，将找到两个预定义的会话模板： Standard和TSQL ，没有可用的选项由用户添加额外的模板。 在这两个会话中，双击它们，您将创建一个新的XEvent专用会话。 相应地使用QuickSessionStandard和QuickSessionTSQL XEvent会话，并可以直接在SSMS中启动它并打开实时数据视图，从那里可以观看SQL Server Extended事件以进行故障排除，而无需任何额外配置。

The QuickSessionStandard session replaces the Standard template in the SQL Profiler, capturing all stored procedures and T-SQL batches running to monitor the general database activities of the SQL Server instance, by collecting XEvents such as login, logout, rpc_completed, sql_batch_completed and sql_batch_starting.

QuickSessionStandard 会话通过收集XEvent（例如登录，注销，rpc_completed，sql_batch_completed和sql_batch_starting）来捕获SQL事件探查器中的标准模板，从而捕获所有存储过程和正在运行的T-SQL批处理，以监视SQL Server实例的常规数据库活动。

Double-click on the Standard XE Profiler template, a QuickSessionStandard session will be started as follows:

双击Standard XE Profiler模板，将如下所示启动QuickSessionStandard会话：

Viewing all extended events in that session, with a detailed view for each selected events as shown below:

查看该会话中的所有扩展事件，以及每个选定事件的详细视图，如下所示：

The QuickSessionTSQL session replaces the TSQL template in the SQL Profiler, capturing all submitted T-SQL statements in order to debug the client applications, by collecting XEvents such as login, logout, rpc_starting and sql_batch_starting.

QuickSessionTSQL 会话通过收集XEvent（例如登录，注销，rpc_starting和sql_batch_starting）来替换SQL Profiler中的TSQL模板，捕获所有提交的T-SQL语句以调试客户端应用程序。

Double-click on the TSQL XE Profiler template, a QuickSessionStandard session will be started as below:

双击TSQL XE Profiler模板，将如下所示启动QuickSessionStandard会话：

Displaying all logged T-SQL statements currently running, with detailed view for each selected events as shown below:

显示当前正在运行的所有已记录的T-SQL语句，以及每个选定事件的详细视图，如下所示：

The corresponding Extended sessions created for both the Standard and TSQL XEvent Profiler templates can be browsed by expanding the Management node to check all Sessions under the Extended Events node as shown below:

通过展开“ 管理”节点以检查“ 扩展事件”节点下的所有会话 ，可以浏览为Standard和TSQL XEvent Profiler模板创建的相应扩展会话，如下所示：

You can also use the predefined XE Profiler templates while creating a new XEvent session using the New Session wizard below:

在使用下面的“新建会话”向导创建新的XEvent会话时，还可以使用预定义的XE Profiler模板：

Take into consideration that the XEvent sessions that are created for the Standard and TSQL XEvent Profiler templates will be created with no specified target, as you can see from the XEvent session properties window below:

考虑到为Standard和TSQL XEvent Profiler模板创建的XEvent会话将在没有指定目标的情况下创建，如从下面的XEvent会话属性窗口中看到的：

However, these two sessions will be deployed as any regular XEvent session that is clear from the SQL Server XEvents templates folder shown below:

但是，这两个会话将作为任何常规的XEvent会话进行部署，这在下面显示SQL Server XEvents模板文件夹中是显而易见的：

结论 (Conclusion)

SQL Server Management Studio is a GUI tool that is mainly used to perform different administration and development tasks in the SQL Server instances. In this article, we mentioned a number of important bugs that are fixed in the newly released SQL Server Management Studio 17.3 version. In addition, we described in details the two new features that are included to that SSMS 17.3 version, that are Import Flat File wizard and XEvent Profiler.

SQL Server Management Studio是一个GUI工具，主要用于在SQL Server实例中执行不同的管理和开发任务。 在本文中，我们提到了新发布SQL Server Management Studio 17.3版本中修复的许多重要错误。 此外，我们详细描述了该SSMS 17.3版本中包含的两个新功能，即“导入平面文件”向导和XEvent Profiler。

Previous article in this series:

本系列的上一篇文章：

• What’s new in SQL Server Management Studio 17.2; Authentication methods, scripting options and more
• SQL Server Management Studio 17.2中的新增功能； 身份验证方法，脚本选项等

Next articles in this series:

本系列的下一篇文章：

• What’s new in SQL Server Management Studio 17.4; Always On AG dashboard enhancements and more
• SQL Server Management Studio 17.4中的新增功能； Always On AG仪表板增强功能及更多
• What’s new in SQL Server Management Studio 17.5; Data Discovery and Classification and more
• SQL Server Management Studio 17.5的新增功能； 数据发现和分类等

翻译自: https://www.sqlshack.com/whats-new-sql-server-management-studio-17-3-import-flat-file-wizard-xevent-profiler/