Post reply

Data getting truncated while exporting to flat file

  • May 17, 2011 at 8:39 am

    #237624

    All,

    Apologies if the question looks very simple as I am very new to SSIS and have a aggresive deadline to meet. I have a stored procedure which has "SELECT @var" as the last statement - VARCHAR(MAX)

    I have built data by concatenating several columns from multiple tables and assigning them to this variable. Then I am exporting the value to a flat file in SSIS. However, the file doesn't have the complete data. It gets truncated and I suppose around 8000 characters (guess).

    Reques to help in this regard

    Thanks,

    Sudarsan

  • May 17, 2011 at 9:19 am

    #1325874

    Have a look in the data flow to see where the truncation is occuring, its possible that you are using the DT_STR data type which can only handle 8000 chars, use DT_Text instead.

  • May 17, 2011 at 9:28 am

    #1325882

    Hi SteveB,

    The datatype is set as DT_Text, but still I face the same situation.

    Thanks,

    Sudarsan

  • May 17, 2011 at 9:34 am

    #1325890

    Where is it set to DT_TEXT ?

    In the data source, in all the transformations, in the data destination?

  • May 17, 2011 at 9:36 am

    #1325891

    In the Data Source, Output is set to DT_Text and in the FlatFile destination, the input is DT_Text

  • May 17, 2011 at 9:44 am

    #1325899

    Sudarsan Srinivasan (5/17/2011)

    In the Data Source, Output is set to DT_Text and in the FlatFile destination, the input is DT_Text

    did you get that information from the Advanced property tab?

  • May 17, 2011 at 9:54 am

    #1325907

    Yes, from advanced property

  • May 17, 2011 at 10:38 pm

    #1326234

    I have checked now and the size is 31947 characters in the file and the size is 32KB if those numbers make any sense...

    Thanks,

    Sudarsan

  • May 18, 2011 at 1:03 am

    #1326248

    I tried to export that to another table in the database with OLEDB destination and the complete data was exported. the column had 137000 characters....

    Should i check something in the flatfile properties as the size is always 32KB and hte characters exported are 31947

  • May 18, 2011 at 5:21 am

    #1326335

    Hi, It was a simple oversight. When I had created a Flatfile connection, I had set the file as "Fixed Width" instead of "Delimited". Now this is resolved.

    Thanks to SteveB for helping.

  • May 18, 2011 at 6:36 am

    #1326382

    I'm glad you got this sorted, thanks for posting the solution

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy