SSMS Import Flat File Feature

The Import Flat File feature in SSMS (SQL Server Management Studio) was introduced in December 2017 (14.0.17213.0). This is a streamlined version of the Import Data feature and it minimizes the user intervention during a file import.

Unlike the Import and Export Data Feature, you don’t have the option to save the import as a .DTS package for SSIS. This makes the Import Flat File method a good one for quick imports of individual files to SQL Server, but not so much if it’s a repetitive task you want to automate it.

In this guide, we will walk through the process of importing a CSV file into a SQL Server database using the Import Flat File feature!

---

How to a Import Flat File to SQL Server

To import a flat file into a SQL Server database table, we can use the Import Flat File Feature in SQL Management Studio (SSMS).

Once you have SSMS open, we need to identify the database we are restoring to. For this demo, I’m creating a new test database:

Now, onto the Flat File import.

Step 1: Right-click your destination database, select Tasks and Import Flat File.

Step 2: Click Next to continue in the Flat File Wizard.

Step 3: Browse to the file, enter the desired table name and amend the schema name accordingly.

Step 4: Preview the data on the next window and click Next when you are happy.

Step 5: Review Column Names/Primary Keys/NULLs, and take as much time as you need when selecting Data types!

Step 6: Finish up the Import Flat File Wizard.

Uh-oh! You might see an error message like this.

Data type conversion errors are common when importing and exporting data. It is important to pay attention to any error messages that appear during the import process. In case of errors, you can search the .csv file for the value mentioned in the error message, to identify the column that needs to be modified.

Error inserting data into table. (Microsoft.SqlServer.Import.Wizard)
The given value of type Decimal from the data source cannot be converted to type decimal of the specified target column. (System.Data)
Parameter value '101.8936' is out of range.

I’m now searching the CSV for this value.

Step 7: Go back in the wizard to Modify Columns and adjust the data type as needed (e.g., increase decimal precision).

Step 8: Now finish the wizard and hope for the best!

Step 9: Verify the data is accurate.

If the data isn’t good, you can try go back and review the data types within the wizard. We should really be using Import and Export Data Feature for the more complex file imports, or SQL Server Integration Services (SSIS).

I hope all this has been useful for you, and you have successfully imported a file to SQL Server!