0

I have a flat file which is ',' separated, the problem is my final destination table has fixed length for columns and those cannot be changed and the data from text file is also same length but during importing the data the default length is larger than the length it actually needs which is causing truncation error. Is there any way that I can load data without this error from a float file.

For example Text1.txt file has 4 columns 

A,B,C,D
1,44,777,1000
2,55,888,1100
3,66,999,1200

My Final table design is like 

A   nvarchar(1)
B   nvarchar(2)
C   nvarchar(3)
D   nvarchar(4)

Text file is defaulting the length to large text which is causing the issue of truncation.

Illuminati
  • 555
  • 2
  • 7
  • 34
  • 1
    Change the length defined in the Flat File Connection Manager. Once done, double click the Flat File Source and it'll reset the lengths – billinkc Aug 05 '15 at 15:06
  • Thank you so much for the response. we receive this file every day and has about 100 columns is there any other way? manually changing is a very hectic process. – Illuminati Aug 05 '15 at 15:20
  • You'd fix the column definition once in the package. I don't understand the "manually changing is a very hectic process" – billinkc Aug 05 '15 at 15:23
  • So you said I need to change the length defined in Flat File Connection Manager Advanced section isnt it? so for every new file we receive we need to update it isnt it? – Illuminati Aug 05 '15 at 15:27
  • You shouldn't need to reconfigure the package every time. Make the configuration changes to the SSIS package once, and simply re-run the package when you have a new file. – Brent D Aug 05 '15 at 15:41
  • You could also address this with a derived column transformation that creates columns that meet the destination columns definitions, and pass those to the destination instead of the original columns. – Tab Alleman Aug 05 '15 at 15:49
  • *I* would use Biml if I were in your shoes. I get a new file in, I specify the column definition and click the button and the same package (with new definitions) pops into existence. [Example 1](http://stackoverflow.com/a/19261634/181965) [Example 2](http://stackoverflow.com/a/29521178/181965) [Example 3](http://stackoverflow.com/a/19957728/181965) [Example 4](http://stackoverflow.com/a/29480939/181965) [Example 5](http://stackoverflow.com/a/29521178/181965) – billinkc Aug 05 '15 at 16:02

0 Answers0