The Import Flat File feature within SQL Server Management Studio (SSMS) was introduced in December 2017 (14.0.17213.0). This is a streamlined version of the Import Data feature and it minimises the user intervention during a file import.
Unlike the Import data feature, you don’t have the option to save the import as a .DTS package. This makes it a good tool for quick imports of individual files to SQL, but not so much if it’s a repetitive task you want to automate.
Below is a guide on using the Import Flat File feature, with a (realistic) twist.
How to Import Flat File SQL Server
The steps below are an example of a CSV file import to a SQL Server 2019 database.
1. Create a database.
2. Right-click your destination database, select Tasks and Import Flat File as shown below.
3. Click Next to continue.
4. Browse to the file, enter the desired table name and amend the schema name accordingly.
5. Preview the data on the next window and click Next when happy.
6. Review Column Names/Primary Keys/NULLs, and take as much time as you need when selecting Data types!
wizard_amend_datatype_a.png
7. Hit Next & Finish when ready.
wizard_finish_a.png
8. Oh wait. I wasn’t expecting this:
wizard_error_a.png
This is something that has to be included as it happens to us all. You won’t know if there are exceptions within the data, so data type conversion errors are a frequent thing in the business of importing & exporting data. We have to use any useful information we can find within the error.
9. If you don’t know the column name, search the .csv file for ‘101.8936’ as quoted within the error message.
10. Going back within the wizard to Modify Columns, there’s only one Decimal Data Type, so it would have been easy to see which column needs changed.
11. Now finish the wizard and hope for the best!
12. Let’s have a look at the new table.
Leave a Reply