none
Importing > 4000 characters in 1 column!

    Question


  • I have a simple text file which has more than 4000 characters in one column. 
    With the Import feature (Locale = English(United States), Unicode - Unchecked, Code Page = 1252 (ANSI Latin I), 

    I can only get 4000 in a column, even though the Destination Column is defined as varchar(max). 

    The problem is that the Source Input Column has to be defined in the importer wizard, and the only 'max' Source setting in the importer appears to be 4000 max. There's no varchar(max) setting for the Source Column in the importer. 

    Anyone have any ideas? Workarounds, etc...
    thx
    f
    Monday, November 04, 2013 1:13 AM

Answers

  • In the SSIS designer there is an artificial limit of 4000 characters for the output of a DT_STR column. You'll need to go into the advanced editor of the source and change output type for the column to DT_TEXT.
    Monday, November 04, 2013 10:37 PM
  • Hi Spatio,

    As Jim posted, you can avoid this issue by change the data type of the input column through the Advanced tab of he “Choose a Data Source” step of the SQL Server Import and Export Wizard. Then, you can set the DataType of the column to “text stream [DT_TEXT]”. In SSIS, the DT_TEXT data type supports an ANSI/MBCS character string with a maximum length of 231-1 (2,147,483,647) characters. Please see the following screenshot:

    Regards,


    Mike Yin
    TechNet Community Support

    Wednesday, November 06, 2013 3:56 PM
    Moderator

All replies

  • DT_STR has a max of 8000 chars. Can't you change it manually in the package? Or is the 'importer wizard' a requirement?


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

    Monday, November 04, 2013 3:02 PM
    Moderator
  • In the SSIS designer there is an artificial limit of 4000 characters for the output of a DT_STR column. You'll need to go into the advanced editor of the source and change output type for the column to DT_TEXT.
    Monday, November 04, 2013 10:37 PM
  • Hi Spatio,

    As Jim posted, you can avoid this issue by change the data type of the input column through the Advanced tab of he “Choose a Data Source” step of the SQL Server Import and Export Wizard. Then, you can set the DataType of the column to “text stream [DT_TEXT]”. In SSIS, the DT_TEXT data type supports an ANSI/MBCS character string with a maximum length of 231-1 (2,147,483,647) characters. Please see the following screenshot:

    Regards,


    Mike Yin
    TechNet Community Support

    Wednesday, November 06, 2013 3:56 PM
    Moderator