Import date with SQL Server Management Studio

SQL Server Management Studio (SSMS) has the option to import data to a database. This functionality is part of the SQL Server Import and Export Wizard. Using this wizard, importing and exporting data becomes very easy. However, the SQL Server Import and Export Wizard needs clean and perfect import files. Importing dates into SQL Server databases might cause some headaches, therefore I will also offer a solution using the SQL DataTool to import dates into SQL databases.

In this blogpost, I will first show how to use the wizard, and which errors you might see while importing date fields. After that, the SQL DataTool is described and how it will help you importing files that cause errors when importing date fields using the SQL Import wizard.

Using SQL Import and Export Wizard

When you want to import data into a database, you will first need to select a database. In this case, I created a database called 'test' without any tables.

When you right-click on the database ('test' in my case) the context menu opens. In the context menu, go to Tasks and then Import Data... Do not look at the other options for now.

When you have selected the option 'Import Data...', the SQL Server Import and Export Wizard will pop up. In this screen, you can select the data source. Or the Welcome screen opens, then just click on 'Next' to go to the 'Choose a Data Source'-screen. The 'Choose a Data Source' screen allows you to select a source and the details of that source from where you want to import the data.

Import date fields

In this blog, I want to import a text file (or a .csv-file). Therefore, I choose the 'Flat File Source' option as the Data source. I selected the file in the File name box using the Browse-button. Click on Next (or on Columns) to go to the columns definition. Here you will also see a preview of the data. In this screen, you can also select the Row delimiter and Column delimiter. You can select these options using the dropdown menus or by typing in the characters.

To get further details and to tweak the column definitions, you can review the settings in the Advanced screen. To preview data, you can select the Preview screen on the left hand side. Since this blog is about the date import, I only change the type of the 'DATE' column to date. I will just leave the column definition as is defined by SQL Server Import wizard. The screenshot below shows the settings of the 'DATE' column. Changing the column to a date-type will result in a datetime datatype in the imported table.

When you click on Next, the wizard will go to the Choose a Destination screen. This screen allows you to change the destination of the import. Since I have started the wizard from the database I want the data to be imported to, the right server and database are already preselected (I cleared the server name in the screenshot).

The next screen is a screen in which you can select the source and destination tables. When importing text (or .csv-) files with the SQL Server Import and Export Wizard, it is only possible to import one file at once. Therefore the overview will only show one source and one destination table.

In this screen, you can use the Edit mappings button to change some settings, like data type and method of import.

When you click on next, a screen opens where you can review the data types and the mapping. As you can see in the screenshot, the 'DATE' field will result in a DATETIME column type. All the other columns are kept as VARCHAR datatype. The DATETIME datatype is validated while importing. The VARCHAR datatype columns have less validation rules (basically only the length).

When you click on Next, you will be asked whether you want to execute the import immediately. Just click on Next twice (or directly on Finish), and the data will be imported.

However... an error occurs. When I click on Messages, I will get the error report, showing there are validation errors:

Messages
Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "DATE" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
 (SQL Server Import and Export Wizard)
 
Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Source - zztest_txt.Outputs[Flat File Source Output].Columns[DATE]" failed because error code 0xC0209084 occurred, and the error row disposition on "Source - zztest_txt.Outputs[Flat File Source Output].Columns[DATE]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
 (SQL Server Import and Export Wizard)
 
Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "C:\Temp\zztest.txt" on data row 2.
 (SQL Server Import and Export Wizard)
 
Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on Source - zztest_txt returned error code 0xC0202092.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.
 (SQL Server Import and Export Wizard)

As you can see, the error report gives some information about which column imposed the error. In this case, I imported a file with 9 lines, so finding the error would be easy. However, can you imagine going through a file that has thousands or millions of records manually?

Using SQL DataTool to import data

SQL DataTool helps you importing plain text files with date columns. When you start SQL DataTool and connect to the database, you can start importing the data. Learn how you can connect to a database.

When the SQL DataTool is connected to a database, you can prepare the data model for the file that you want to import. An article on how to add a data model is also available. In this case, I have prepared a data model that fits the file I want to import. I put the field 'DECIMAL' as a varchar, since we are not looking at this data type in this article. I will discuss importing decimals in a different article.

When the data model is available in the SQL DataTool, you can start importing. I will use the exact same file that I used during import with the SQL Import wizard.

When the import is finished, you will see an information pop up showing whether the import was successful or not. In this case, all rows have been imported successfully.

When the import is not successful, the rows with errors are written away in a different file for review. All other rows will be imported to the database. Look into the manual how to solve errors.

So in short, SQL DataTool recognizes the date format. You can set the date format manually.

Besides that, rows with errors will be written away into a separate error file. The import will not stop, and the rows with errors can be solved an appended to the existing table.

To get your trial version of the SQL DataTool, go to the download section.