SSMS 17.3

SSMS 17.3 was just released (download link), and it has a couple of new features worthy of mention:

  • Import Flat File Wizard
  • XEvent Profiler

Import Flat File Wizard

If you’ve worked with SQL Server for very long, you have most likely needed to import flat files from time to time. SSMS 17.3 utilizes a new import technology (Microsoft calls it the “Microsoft Program Synthesis using Examples” (PROSE) SDK). This new technology can take raw, semi-structured data in .csv or .txt files, and it identifies patterns to do “predictive file splitting” to allow the wizard to learn about the file, including column names, types, delimiters, etc.

This wizard is available from the Tasks context menu of a database node:

In the wizard that comes up, you simply specify the input file, and the destination table. The new table name will default to the name of the flat file. The wizard then steps you through previewing the data and specifying column schema.

Testing the Import Flat File Wizard

One of the biggest issues that I’ve dealt with is dealing with lines that have quoted text. So, let’s try this new wizard out. We’ll use this import file:

Stepping through the wizard, we first specify the file being imported:

The next step of the wizard let’s you preview the data:

This preview shows that most of the lines handled the quoted text (and other columns) correctly. I expected the last line to have ended with a double-quote, and it doesn’t. However, lines with double-quotes embedded in the line did keep those double-quotes.

In the next step, you specify the schema for the new table:

After this step is a summary screen that tells you simply the server / database name, table name, and file name being imported. Clicking Finish here creates the table and imports the file into the table.

For this simple test, it worked pretty well, and it should work well for most of the requirements that you have. Time will tell how reliably this new feature does work.

The Import Flat File is available when connecting to SQL Server version 2005 or higher. I haven’t tried this on a lower version, but I don’t see any reason why it wouldn’t work there either. You can read more about this feature in Microsoft’s documentation.

XEvent Profiler

The Extended Events (XE) that were introduced in SQL Server 2008 are intended to replace usage of the SQL Profiler. They are not as invasive (resource-wise) on your system as SQL Profiler is. There are a lot of people still don’t use them because they feel that it’s faster to get a profiler trace running than an XE session. The new XEvent Profiler overcomes this. According to the Microsoft blog post introducing this feature, they even want to know if there is a gap that requires you to use SQL Profiler instead of this new XEvent Profiler.

The XEvent Profiler gives you the ability to start and watch (by live target view) either of two predefined XEvent sessions. These provide you with a quick view capability into your system. It provides:

  • Only the two sessions.
  • No filtering.
  • Only the predefined events in those sessions, and only the events available in SQL Profiler.

Since the XEvent Profiler replaces SQL Profiler, it only has the events that are available in SQL Profiler. This means that none of the newer features that need XEvents to monitor are available in these sessions. I predict that you’ll either love or hate this feature – and I know people who are on both sides of this feature already!

The XEvent Profiler is available in SSMS just under the SQL Server Agent node:

From here, you can start and stop the session. When you start (launch) the session, it will open up the XEvent Live Data Viewer.

The XEvent Profiler is available when connecting to SQL Server versions 2012 or higher. You can read more about this feature in Microsoft’s documentation.