peterlukerow
commented
6 years ago Definitely there should be an option in the context menu on a database.
Everything on some dashboard is the thing I hate most about SOS. If I want to import a flat file into a database I assert I'm not going to pick a random database I'm going to know exactly which one.
Given SOS seems to be ripping up the rule book, so to speak of how SSMS works at least give config options for the feature so that you can choose to have it on the context menu on a database.
I want to use SOS mainly because it properly implements a dark theme, and some of it's functionality is nicer but it is missing so much functionality and changing so much of the way things use to work there needs to be a little give and take, i.e. have settings so long time SSMS users can have things closer to how they use to be as well as having access to the new bits. Especially important now that MS is seemingly dumping SSMS (a bit like their actions imply on full fat .NET)
dzsquared
yualan
DaveDustin
rdymade
ndwivedi
Hermholtz
abusk1
BolunZhu
But my .txt file looks like this.
gracielaPosadas
dvlsg
williamthiago
pnhughes
anand348
mdougherty3
iAlexanderMoon
amsteel
kburtram
rainabba
JohnA43
andyleedev
TalariaxKhin
As part of Microsoft's Hackathon, the engineering team created an initial prototype extension for the SQL Server Import extension, otherwise known as Import Flat File Wizard.
The SQL Server Import extension can import any .txt or .csv file into a database and convert into a SQL table, and this marks our first import experience in SQL Operations Studio. The beauty of this feature is the user is not overwhelmed with complex configuration options when all they want to do is import a flat file.
Harnessing the power of AI, the Import Wizard uses a Microsoft Research technology known as Program Synthesis Using Examples, or PROSE. PROSE can automatically detect a file's data types, delimiters, column names, and file structure without the user having to explicitly define the configuration. See an end-to-end demo in the GIF below.
For most users, SQL Server Import turns into a 5 click experience to go from flat file to SQL table. Let's go into a bit more detail.
Requirement
In order to launch the wizard, make sure you have an active connection. Otherwise, error is thrown.
UI Entry Point
After downloading the SQL Server Import Extension (in a future Insider's Build or on August 22nd), the user has two entry points:
We are still experimenting the best entry point. Things we are considering and would appreciate feedback:
New Table Details
In SSMS, the user can only select a file, table name, and schema. We added Server, which detects only active servers, and Databases, which detects databases in the selected server. Since our current implementation allows the wizard to be opened anywhere and not restricted to a database, we allowed user flexibility to open wizard any time. Let us know if this is an issue.
We don't want to daunt the user of having too many configuration options on first page, but please let us know if we should include additional items. Next is disabled until all fields are filled. Table name is auto-filled, but will throw error on client-side if table name already exists.
Preview Data
image
This page makes a call to our PROSE backend and pops out a preview. The PROSE learning is applied to the first 200 rows, but for now we just display the first 50 rows as a sanity check for the user. User does not do anything except view data.
One thing we are considering is adding override options on this page so that the user can:
The idea is that the user can automatically generate a new preview with new override options. Let us know if this is important to you.
Modify Columns
This page allows the user to change Column Names, Data Types, Primary Key, and Nullability. PROSE automatically can learn the data types of the file and will choose best option. As a user, you have an editable dropdown to choose from T-SQL valid data types, and also change number of characters such as varchar(999) in case you have a long string value on row 9999. Let us know if this UI is helpful
We have heard feedback that a clusterable and columnstore checkbox would be helpful when dealing with those server instances. Let us know how important that is to you.
Summary
Once you click Next, PROSE will import the whole file into a SQL table that will be added to your database. If it is successful, a success message will appear and tell you how many rows were successfully inserted. Let us know if you would like a bigger checkmark or more colorful indicator.
The more interesting scenario is for error cases. For now, we are working on an error message that says "error at lines 10-20," but due to limitations of SQL Bulk Copy, we can't get any more granular than that. Let us know how high of a priority it is to have of error scenarios you have run into the past so that we can figure out how to best share this information.
Import New file
This is a nice button on the summary page, but this will remember your server and database you selected and right away you can import another file. Makes it easy to import multiple files. We would eventually like to import directories of files or make this scriptable, but there are current complications and bandwidth issues, so that is lower priority for now.
Future work
We captured some additional scenarios in the walkthrough above, but there are some plans in the works:
We can't wait to get this extension in your hands this month, but please let us know how we can improve the UI for this wizard. Let us know what you prioritize, and we can work together to incorporate into our plans. Please spread the word about this conversation.
Let us know what you would like to see in SQL Server Import in this issue. We would love to hear your feedback as we continue to experiment with Open Design so that we are building features that our users love. Thank you.