SSMS SQL Server Import & Export Wizard does not determine correct field maxlength for Flat File Import - by nimblebit

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


1
0
Sign in
to vote
ID 3118566 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 1/11/2017 7:36:28 AM
Access Restriction Public

Description

UPDATE: 14/11/ 2017 - SQL Server Management Studio 2017 (17.3) now has this feature in the new Import Flat File Wizard :)
https://channel9.msdn.com/Shows/Data-Exposed/Introducing-the-new-Import-Flat-File-Wizard-in-SSMS-173

-----------------------------------------------------------------------
When importing a CSV file with Headers  
AND Using SQL Server Native Client 11.0 Provider
The suggested Column Mappings for Create Destination Table (Edit Mappings button) step are wrong.
By default they are all varchar(50)
As a result if you continue the import without changing each field maxlength, the SQL Server Flat File Import will fail due to a Data Truncation Error.

- This then requires additional administration as we have to determine where the import failed and drop the table if it was a new table.

This can easily be prevented by automatically detecting the maxlength of each column in the csv file. If it exceeds the default varchar(50), use the new suggested size.

SQL Server Import Wizard should read the CSV file and determine the MAXLENGTH of EACH Column and automatically generate the correct size maxlength for each column based on the data on each column.

- This would significantly make the import more reliable
- This would reduce the steps for the database admin to perform the import
- It is a more intelligent way of performing the import. 

It is an Import Wizard so should be smarter in suggesting field lengths to avoid these errors.
Sign in to post a comment.
Posted by nimblebit on 11/14/2017 at 3:14 AM
SQL Server Management Studio 17.3 now has this feature in the new Import Flat File Wizard

https://channel9.msdn.com/Shows/Data-Exposed/Introducing-the-new-Import-Flat-File-Wizard-in-SSMS-173
Posted by Microsoft on 7/9/2017 at 7:42 PM
Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SSIS. The reasons for closing these bugs are following:
1.     The fix is risky to implement in the current version of the product
2.     Scenarios reported in the bug are not common enough


If the issue is a critical business interruption, please call CSS (Customer Support Services).

Thanks again for reporting the product issue and continued support in improving our product.