Home Dashboard Directory Help
Search

DTSWizard in SQL 2012 SP1 no longer recognizes nvarchar/varchar data types when source is a query by K Dahl


Status: 

Closed
 as Fixed Help for as Fixed


140
0
Sign in
to vote
Type: Bug
ID: 772761
Opened: 11/29/2012 10:47:28 AM
Access Restriction: Public
7
Workaround(s)
view
51
User(s) can reproduce this bug

Description

When using the import/export tasks from SQL Management Studio after SP1 has been applied, you can no longer use a query as a data source if it includes data types of varchar/nvarchar. These are no longer recognized from the SQL->SSIS data type mapping, and show up as 200 (varchar) and 202 (nvarchar). nchar also now maps to nvarchar, and binary maps to varbinary, when they previously mapped to their exact data types prior to SP1 being applied.
Details
Sign in to post a comment.
Posted by Slacktose_Intolerant on 6/19/2014 at 7:52 AM
I installed SQL2012 SP2 just a few minutes ago and that does appear to have finally solved the problem, just in case anyone is looking for a solution.
Posted by Keith Spitz on 6/18/2014 at 9:37 AM
Still broken with SQL Server 2012 Cumulative Update 9 applied. DTSWizard product version 11.0.3412.0. Why is this marked as closed/fixed?
Posted by Theguru on 5/25/2014 at 5:30 PM
yes, still broken and thousands (if not hundreds of thousands) of man hours are wasted due to someone not testing properly at Microsoft.
Posted by SQLIZR on 5/5/2014 at 11:57 AM
This is still broken in SQL 2012 SP1, DTSWizard.exe version 2011.110.3000.0. In the export dialog, if you click "Edit Mappings" on the "Configure Flat File Destination" screen and just touch / click inside any of the fields in the type column, just once, then click OK to close the Column Mappings screen, the export will work as if nothing is wrong.
Posted by Marc K 4096 on 4/24/2014 at 7:25 AM
This has been fixed for SQL server 2008 R2 in Cumulative update package 12 (CU12) for SQL Server 2008 R2 Service Pack 2 (http://support.microsoft.com/kb/2938478)
Posted by Kevin R Hawkins on 3/19/2014 at 4:45 AM
This isn't fixed even in SSMS 2012 with the latest cumulative update CU8.
Posted by Developer_46038 on 3/11/2014 at 2:47 PM
Doesn't seem to be working yet. :( I'm trying to go from database to database on the same server. No mixed version issues
Posted by DarrenJensen1 on 3/5/2014 at 12:27 PM
So is this fixed or not. If it is then how do we get the fix?
Posted by Michael J. Brown on 12/27/2013 at 2:00 PM
Seriously MS, no fix? This is a major issue and breaks the import/export wizard. How can this stuff go to production code? They don't QA anything anymore - on their most expensive product?
Posted by RasheedK on 12/3/2013 at 3:21 AM
Team Microsoft,

I can see the Status: Closed as Fixed, could please let us know this change is included in which CU/SP ?
Posted by Marc K 4096 on 10/29/2013 at 6:39 AM
This problem still exists in SQL Server 2008 R2 CU9 10.50.4295.
Posted by sushi.at on 10/17/2013 at 4:11 AM
Fast approaching a year since this got reported and still no fix?
Posted by Bryant E. Byrd on 9/30/2013 at 10:06 AM
I have confirmed that this issue is *not* fixed in CU5 for SQL Server 2012 SP1.

Could someone from Microsoft let us know when this fix is going to materialize?
Posted by rosirosss on 9/6/2013 at 12:49 PM
This issue can now be reproduced in SQL server 2008 R2 10.50.4276. But does NOT reproduce in build SQL server 2008 R2 10.50.2550.0
Posted by cameron_eldridge on 7/25/2013 at 12:25 AM
What version was this fixed in?
Posted by Aditya Christian on 7/21/2013 at 7:16 PM
Hi Microsoft Team...

this bug already fixed in this cumulative update package or not ?
http://blogs.msdn.com/b/sqlreleaseservices/archive/2013/07/16/cumulative-update-5-for-sql-server-2012-sp1.aspx

i really need this bug to be solved.. thanks..
Posted by Microsoft on 6/21/2013 at 9:10 AM
Hello K Dahl. Thanks for bringing this to our attention. This has been fixed. The change will be included in a future release or servicing release for SQL Server.

-Walter A Jokiel, Program Manager, SQL Server (wajokiel@microsoft.com)
Posted by ttl1903 on 6/7/2013 at 4:48 PM
Is there a fix for this yet?
Posted by SQLNoobie on 4/16/2013 at 12:57 PM
I have the exact same issue. I had been working with SQL Server 2012 Express edition and exported to csv was working just fine, once I installed SP1 via Windows Update a few days ago, I can no longer export due to the error "Source data type "200" was not found in data type mapping file" for any columns with a varchar type.

My workaround is to click the "Edit Mappings..." button on the "Configure Flat File Destination" page, then change the Type to "string [DT_STR]" from the incorrect Type "byte stream [DT_BYTES]" for each of the affected columns. This is such an inconvenience to have to do every time I export!
Posted by Uncle Cheese on 4/12/2013 at 3:39 PM
I am just reiterating John Soloman's point below. I export data every day from ad hoc queries - can I create views for them? Sure. It is absurd that I should need to do so. This should have been fixed months ago.
Posted by John Soloman on 4/12/2013 at 6:14 AM
That this issue still exists is beyond ridiculous. I'm wasting time every day to work around it.
Posted by The Gabe on 4/1/2013 at 9:43 AM
Has anyone received any feedback from Microsoft? I can not find where they have even acknowledged the problem exists. We have been wanting to switch over but with every SP1 and the 3 CUs more stuff has broken.

It sounds like Quality Control and Testing from Microsoft has tanked.
Posted by mbourgon on 3/14/2013 at 3:14 PM
Got this after installing SSMS from the ISO for 2012 with SP1.
Posted by jorgeghr on 3/14/2013 at 10:46 AM
I am experiencing the same issue. The suggested workaround is not a solution for me.
Posted by allmhuran on 3/3/2013 at 3:57 PM
Gotta watch out for those sneaky arcane and rarely seen data types like varchar...

Confirmed still broken in SP1. Creating a view works but is not really a workaround. If I didn't need parameters it would have been a view, but I do need parameters, that's why it's a function...
Posted by swh1 on 1/15/2013 at 1:26 AM
We have experienced the same issue. Fortunately, we have only got this installation to test so we have reinstalled 2012 without SP1 to allow us to continue without having to create unnecessary views.
Posted by trepur on 1/14/2013 at 5:14 AM
Evidently a problem with SP1 - uninstalling 2012 and re-installing without applying SP1 works
Posted by K Dahl on 12/11/2012 at 2:22 PM
You can also likely just save the package and go and edit it in DataTools, as the problem doesn't seem to occur there. Or just build the package in DataTools in the first place. However, that seems to defeat the lightweight intention of the DTSWizard.
Posted by Nirav Patel SQ on 12/7/2012 at 9:44 AM
Quick workaround to this is to create a view for query and it works
Posted by Nirav Patel SQ on 12/7/2012 at 9:42 AM
Same issue here. Only when query as a source. works fine with tables. also it does recognizes int, numeric data types but issue with varchar, char data types.
Sign in to post a workaround.
Posted by Slacktose_Intolerant on 6/19/2014 at 7:53 AM
SQL 2012 SP2 will solve it but just in case you cannot install it at this time, this post looks promising: http://imukai.com/post/SQL-2012-Import-Unknown-column-type-conversion
Posted by tomlinjm on 3/2/2014 at 4:26 AM
Tested Jerome Pion's workaround and it worked great. Thanks Jerome.
Posted by Jerome Pion on 11/21/2013 at 9:35 AM
It seems that casting CHAR types to SQL_VARIANT in the query and then changing the destination type in "Edit Mappings" (if creating a new table) appears to work around the issue.
Posted by Scott Faculak on 5/31/2013 at 6:47 AM
Use SSMS 2008 as the problem is isolated to SSMS 2012 and not the engine.
Posted by James Parker on 5/20/2013 at 8:44 AM
Same problem. However exporting to a CSV file works.
Posted by mbourgon on 4/18/2013 at 1:39 PM
Naves showed a partial workaround in this thread:
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/97ff1f01-c02a-4c9a-b867-8eaecc464cfb/
Posted by digitallnet on 12/10/2012 at 8:01 AM
The workaround specified by Nirav Patel SQ worked for me - create a view of the data needed for exporting and then the mappings are successful and you can export.