SQL Server Import and Export Wizard Does Not Recognise Varchar and NVarchar

SQL Server Import and Export Wizard Does Not Recognise Varchar and NVarchar by Julie Koesmarno

Active

Type:	Bug
ID:	775897
Opened:	1/5/2013 11:01:52 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

When importing data retrieved via a query using the SQL Server Import and Export Wizard in SQL Server 2012, the VARCHAR Type is recognized as "200" while NVARCHAR Type as "202". This cause the wizard to show "Review Data Type" warning/error that looks similar to the following:
Found 14 unknown column type conversion(s)
You are only allowed to save the package

This means that the data transfer cannot be executed and the wizard will only be able to save the transfer as an SSIS package.

The issue seem to only appear if the data source is a query - i.e the "Write a query to specify the data to transfer" in the Specify Table Copy or Query section of the SQL Server Import and Export Wizard.

Choosing "Copy data from one or more tables or views" option in the Specify Table Copy or Query section seems to recognize the VARCHAR and NVARCHAR data type properly.

Details (expand)

English

SQL Server 2012 - Developer Edition

Developer Tools (SSDT, BIDS, etc.)

Windows Server 2012 Standard

US English

1. Open SQL Server Management Studio.
2. Connect to a SQL Server 2012 instance with AdventureWorksDW2012 database ready to use.
3. Right click on the AdventureWorksDW2012 database and choose Tasks > Import Data
4. Click Next and provide SQL Server 2012 database connection that is the same database, i.e. AdventureWorksDW2012. (i.e our source and target are the same database). Click Next
5. Click Next to accept that the database target is the same as the source target.
6. Choose "Write a query to specify the data to transfer" in the Specify Table Copy or Query section.
7. Type in the following statement, then Click Next.
SELECT [EmployeeKey]
     ,[ParentEmployeeKey]
     ,[EmployeeNationalIDAlternateKey]
     ,[ParentEmployeeNationalIDAlternateKey]
     ,[SalesTerritoryKey]
     ,[FirstName]
     ,[LastName]
     ,[MiddleName]
     ,[NameStyle]
     ,[Title]
     ,[HireDate]
     ,[BirthDate]
     ,[LoginID]
     ,[EmailAddress]
     ,[Phone]
     ,[MaritalStatus]
     ,[EmergencyContactName]
     ,[EmergencyContactPhone]
     ,[SalariedFlag]
     ,[Gender]
     ,[PayFrequency]
     ,[BaseRate]
     ,[VacationHours]
     ,[SickLeaveHours]
     ,[CurrentFlag]
     ,[SalesPersonFlag]
     ,[DepartmentName]
     ,[StartDate]
     ,[EndDate]
     ,[Status]
     ,[EmployeePhoto]
FROM [dbo].[DimEmployee]
8. Click Edit Mappings on "Select Source Tables and Views". Here you will see Type = 202 instead of NVARCHAR.
See attached screenshot "SSMS_ImportExportWizard_ColumnMappings_TypeIssue.png"

Continuing the steps to reproduce, here are the actual results:
9. Click OK and Click Next to advance to the "Review Data Mapping" section where an error / warning that looks similar to the following is shown:
Found 14 unknown column type conversion(s)
You are only allowed to save the package
See attached screenshot "SSMS_ImportExportWizard_ReviewDataTypeMapping_Error"
10. Continuing the wizard by saving it as an SSIS package will save the erroneous data type (200 / 202). See attached screenshot "SSIS_SavedFromImportExportWizard"

VARCHAR columns are recognized as Type 200
NVARCHAR columns are recognized as Type 202

The Import and Export wizard does not let you to continue running the transfer. You can only save the SSIS package.

VARCHAR columns are recognized as Type VARCHAR
NVARCHAR columns are recognized as Type NVARCHAR

The Import and Export wizard continues successfully and runnable.

X64

File Name	Submitted By	Submitted On	File Size
SSMS_ImportExportWizard_ColumnMappings_TypeIssue.png	Julie Koesmarno	1/5/2013	39 KB
SSMS_ImportExportWizard_ReviewDataTypeMapping_Error.png	Julie Koesmarno	1/5/2013	64 KB
SSIS_SavedFromImportExportWizard.png	Julie Koesmarno	1/5/2013	270 KB
SSMS_ImportExportWizard_ColumnMappings_TypeIssue.png	Julie Koesmarno	1/5/2013	39 KB
SSMS_ImportExportWizard_ReviewDataTypeMapping_Error.png	Julie Koesmarno	1/5/2013	64 KB
SSIS_SavedFromImportExportWizard.png	Julie Koesmarno	1/5/2013	270 KB

It looks like a similar issue has been submitted here: https://connect.microsoft.com/SQLServer/feedback/details/772761/dtswizard-in-sql-2012-sp1-no-longer-recognizes-nvarchar-varchar-data-types-when-source-is-a-query#details

Please vote from the above link. This issue can be closed as it's a duplicate one.

Thank you,
Julie