Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SSIS can't export varchar() to nvarchar()

Unanswered SSIS can't export varchar() to nvarchar()

  • Saturday, January 26, 2013 9:16 PM
     
      Has Code
    Sign In to Vote
    0

    I'm writing a pretty simple SSIS package in SSMS for exporting SQL table data to a plain text file. My source table has the following definition for the column Telephone:

    [Telephone] [varchar](max) NULL,

    Other columns in the table are NVACHARs. The file I'm preparing is also to be written in Unicode. But when my wizard runs this package (created by far on his own), I get  the following error:

    Messages
    Error 0xc020802f: Data Flow Task 1: The data type for "input column "Telephone" (121)" is DT_TEXT, which is not supported with Unicode files. Use DT_NTEXT instead and convert the data to DT_TEXT using the data conversion component.
     (SQL Server Import and Export Wizard)
     
    Error 0xc0202094: Data Flow Task 1: Unable to retrieve column information from the flat file connection manager.
     (SQL Server Import and Export Wizard)
     
    Error 0xc004701a: Data Flow Task 1: component "Destination - Participants-J_txt" (85) failed the pre-execute phase and returned error code 0xC0202094.
     (SQL Server Import and Export Wizard)
     

    I don't understand what it wants from me. Could you please tell me what to do to have this file created without this issue?

     

All Replies

  • Saturday, January 26, 2013 9:33 PM
     
     
    Sign In to Vote
    0

    Rather than exporting from a table, you need to change it to a query with all of the columns in the order you need.  However, rather than just outputing the Telephone column directly you will need to use an expression (adjust the length of the NVarchar to the length of the data in your table).

    cast(Telephone as NVarchar(100)) as Telephone

    The problem is that exporting a large object directly to a file will not work (NVarchar is a large character object).  You need to convert it to a "normal" nvarchar in order to be able to export.

    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

     
  • Saturday, January 26, 2013 10:00 PM
     
     
    Sign In to Vote
    0

    Hi,

    Don't quite understand why exporting of nvarchar(max) won't work.

     
  • Saturday, January 26, 2013 10:12 PM
     
     
    Sign In to Vote
    0
    I misread your post and I was remembering wrong information.  The problem is that you are trying to put a varchar (not unicode) into nvarchar.  This always causes an error.  SSIS (and the import export wizard) won't convert automatically from varchar to nvarchar.  So you can try Cast (Telephone as nvarchar(max)) as Telephone.

    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

     
  • Saturday, January 26, 2013 10:17 PM
     
     
    Sign In to Vote
    0

    Hi,


    New questions are coming:

    1. Changed my SQL query that extracts data for cast(Telephone as NVarchar(100)) as Telephone - saw no error when clicked on Parse button but weird warning against Source Query complaining about "column Telephone cannot convert between unicode and non-unicode". I wander what this warning is about?

    2. The results of export still the same - complains about Telephone column. What's wrong with it?

     
  • Saturday, January 26, 2013 11:12 PM
     
     
    Sign In to Vote
    0
    Already tried - the same result...
     
  • Saturday, January 26, 2013 11:31 PM
    Moderator
     
     
    Sign In to Vote
    0
    Already tried - the same result...

    After adding the CAST to the source query, you have to uncheck all changed columns in the source and then check them again. This will refresh the meta data...

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

     
  • Saturday, January 26, 2013 11:38 PM
     
     
    Sign In to Vote
    0
    don't quite understand how I can do it in Visual Studio. Could you please shed the light on this option?
     
  • Saturday, January 26, 2013 11:46 PM
    Moderator
     
     
    Sign In to Vote
    0
    don't quite understand how I can do it in Visual Studio. Could you please shed the light on this option?

    After you add the cast in the OLE DB Source component, you have to go to the Columns pane and uncheck and recheck the changed columns.

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


     
  • Sunday, January 27, 2013 8:11 AM
     
     
    Sign In to Vote
    0

    After I unchecked/checked back all my columns I got new error in export execution:

    Error: Input column "" (114) has lineage ID 17 that was not previously used in the Data Flow task.

    I'm out of understanding what's going on...

    screen with the error

    my DTSX package after editing in VS

    the SQL definition of my sorce table

    • Edited by Senglory Sunday, January 27, 2013 8:19 AM added links
    •  
     
  • Sunday, January 27, 2013 8:20 AM
    Moderator
     
     
    Sign In to Vote
    0

    After I unchecked/checked back all my columns I got new error in export execution:

    Error: Input column "" (114) has lineage ID 17 that was not previously used in the Data Flow task.

    I'm out of understanding what's going on...


    The column got a new ID... edit the transformation with the red cross. It will come with a screen to change it.

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

     
  • Sunday, January 27, 2013 9:57 AM
     
     
    Sign In to Vote
    1

    Hi

    As it is very simple package it might be easier to to start from the beginning.

    1) Create connection (I recommend to use .sqlclient it handles data type changes at source, OLEDB does not handle data type changes...). Ensure you have correct data types (nvarchar) etc for more info check the following video SSIS sqlClient Source 

    2) Once source is ok (with data types) create Text File Destination. They can be tricky so I recommend this video SSIS Text File Destination

    3) To learn more visit our SSIS Playlist which has nearly 100 videos using SSIS 2012 but almost all work with SSIS 2005 and 2008

    Hope that helps... Your post gave me ideas for 3 new videos ;) I'll upload links to your post once I have them.

    Regards

    Emil

    p.s. 

    I played with Wizard (export data) in SQL Server 2012 it seems to be much more stable than in previous versions.

    The only time I could get the same error in Visual Studio was when I renamed field name so maybe when you used cast the field name didn't match or column ID was changed (I think they improved that in SSIS 2012).

    • Edited by Katie and Emil Sunday, January 27, 2013 3:27 PM extra info
    •  
     
  • Sunday, January 27, 2013 4:04 PM
     
     
    Sign In to Vote
    0

    Ok, ultimately I managed to get the TXT file as an export output, though I'm very upset that standard wizard from SSMS can't do it at all. Now I'm trying to do import of that TXT file into that table via this DTSX. Surprisingly, it gives me no error at all as well as 0 rows of import. I wonder what it doesn't like at this time?
     
  • Tuesday, January 29, 2013 9:20 AM
     
     
    Sign In to Vote
    0

    The wizard is not that good (but seems to work better with 2012).... It's good you haven't tried spreadsheets... that is more mission impossible ;)

    With your important it is rather unusual that it doesn't give you an error... Try to go to data flow. Open TXT source and there should be option to preview.. check if there is data. I suspect you path to file is static (does not use expression) so it should be pointing to the correct file.

    Also might be worth watching this to see if there is anything that can help you SSIS txt Source

    Take care

    Emil 

     
  • Wednesday, January 30, 2013 6:44 PM
     
     
    Sign In to Vote
    0
    For some stupid reason SSMS recognizes every column as "Unicode string [DT_WSTR]" with length as big as 50 symbols. Not a Unicode stream. Having assumed all data fits that 50 chars (and ignoring that input file was with column | delimiter set explicitly) I got import broken :(