Source Type 200 in SQL Server Import and Export Wizard?

sql-server type-conversion ssms-2012
kabichan picture kabichan · Feb 12, 2013 · Viewed 30.3k times · Source

I am trying to import data from one database in a server to a new table in a different server, using SQL Server import and Export Wizard. (SQL Server Management Studio 2012)

In the wizard, I checked "Write a query to specify the data to transfer", and the SQL statement returns data containing the following four columns:

+-----------------------------------------------------------------------------+
| ID(varchar(100))  |  Title(text)  |  Description(text)  |  IsActive(tinyint)|
+-----------------------------------------------------------------------------+

I want to change the types for the new table to

+----------------------------------------------------------------------------------------+
| ID(varchar(4))  |  Title(varchar(200))  |  Description(varchar(2000))  |  IsActive(bit)|
+----------------------------------------------------------------------------------------+

So, in the "Column Mappings" page(In the "Select Source Tables and Views" page, I clicked "Edit Mappings..."), I changed the Destination Type to the above types. Then, after clicking "Next", in the "Review Data Type Mapping" page, I get an error that says "Found 3 unknown column type conversion(s). You are only allowed to save the package"

The Data type mapping shows the following information:

icon    Source Column    Source Type    Destination Column    Destination Type    Convert
----------------------------------------------------------------------------------
error   ID               200            ID                    varchar
error   Title            200            Title                 varchar
error   Description      201            Description           varchar
warning IsActive         tinyint        IsActive              bit

Even if I don't change the data type in the "Edit Mappings..." page, I get the same error.

I don't understand what "200" means in the context of a data type, and how can I import this data to a new table in a different server?

I appreciate any help.

Answer

James Sampica picture James Sampica · Sep 27, 2013

With a bit of experimentation this error only seems to occur when you have a query as the source. The accepted answer did not work for me as copying to a flat file would result in the same error.

To solve this I put my query into a View then selected Copy From one or more Tables Or Views instead of Write a query....

I went through the wizard normally after that and my data went through with no error

Related questions

How do you change the datatype of a column in SQL Server?

I am trying to change a column from a varchar(50) to a nvarchar(200). What is the SQL command to alter this table?

Read More
How to convert Nvarchar column to INT

I have a nvarchar column in one of my tables. Now I need to convert that column values to INT type.. I have tried using cast(A.my_NvarcharColumn as INT) and convert (int, N'A.my_NvarcharColumn') When I ran …

Read More
How to convert number of minutes to hh:mm format in TSQL?

I have a select query that has DURATION column to calculate number of Minutes . I want to convert those minutes to hh:mm format. Duration has values like 60, 120,150 For example: 60 becomes 01:00 hours 120 becomes 02:00 hours 150 becomes 02:30 hours Also, this is how …

Read More