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 10 (of 10 total)

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