Importing Flat Files into SQL Server with SSMS

SQL Server Management Studio (SSMS) offers a feature called Import Flat File, which is a streamlined version of the Import Data feature. It minimizes the need for user intervention during a file import.

This feature does not include the option to save the import as a .DTS package, which makes it more suitable for quick imports of individual files but not for repetitive tasks that need automation.

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.

Import a CSV File to SQL Server

To import a file into a SQL Server database table using SQL Management Studio (SSMS), we first have to identify the database we are restoring to. When you know this, right-click the database in the Object Explorer and select Tasks, then Import Flat File.

SSMS Import Flat File Option

In the prompted Import Flat File wizard we will be configuring our settings for the SQL file import.

Step 1: Browse to the local file you want to import, enter the desired table name and amend the schema name accordingly.

SSMS Import Flat File Location

Step 2: Preview the data and click Next when ready.

SSMS Flat File Import Data Preview

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

SSMS File Import DataTypes

Step 4: Hit Next and Finish up the wizard when ready.

SQL Import Flat File

Step 5: Handle Errors

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.

SSMS Import Flat File Error

For the above error, I was able to find the value “101.8936” within the CSV file and update it so it conforms to the MSSQL datatype rules.

Step 6: Check the new table

If your flat file data import was successful, you can check the new table by querying it.

SQL File Import Operation Complete
SQL Query Imported Flat File

After the import process is complete, it is a good practice to check the new table to ensure that the data has been imported correctly.

Leave a Reply

Your email address will not be published. Required fields are marked *