Dyncamically change Column lenght in Foreach Loop container of SSIS

Question

Dyncamically change Column lenght in Foreach Loop container of SSIS

sql server developer

SSCarpal Tunnel

Points: 4746
More actions
July 3, 2014 at 11:57 am

#287569

Hi All,
I'm trying to load data from multiple files into SQL server using Foreach Loop Container in SSIS 2008. All the files are in the same format with same number of columns. But the issue i'm having is that the data in the files are of different length in different files because of which i'm getting truncation errors. SSIS is not automatically changing the datatype length based on the data in the files. If the first file has ColA with max length of 100 it is working fine but if the second file ColA has max length of 150 it is failing.
How can I overcome this issue and make SSIS automatically change source datatype length based on data in the columns?
Thanks in advance!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

sql server developer (7/3/2014)
Hi All,
I'm trying to load data from multiple files into SQL server using Foreach Loop Container in SSIS 2008. All the files are in the same format with same number of columns. But the issue i'm having is that the data in the files are of different length in different files because of which i'm getting truncation errors. SSIS is not automatically changing the datatype length based on the data in the files. If the first file has ColA with max length of 100 it is working fine but if the second file ColA has max length of 150 it is failing.
How can I overcome this issue and make SSIS automatically change source datatype length based on data in the columns?
Thanks in advance!

You can't. Datatypes and column lengths are static in SSIS.

But you can increase the length of the columns in SSIS to accommodate your maxima.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

sql server developer SSCarpal Tunnel Points: 4746 More actions · Answer 2

Thanks for your reply!

Where can I increase the length of the columns in SSIS?

Phil Parkin SSC Guru Points: 246695 More actions · Answer 3

Double click on your flat file connection manager, click on 'Advanced' in the left window.

Increase 'Output Column Width' for each column that needs it.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

sql server developer SSCarpal Tunnel Points: 4746 More actions · Answer 4

Thanks for you reply. I'm working with Excel file not flat file. How can make that change for excel file?

sql server developer SSCarpal Tunnel Points: 4746 More actions · Answer 5

Hi All,

Here is what i've done to overcome this issue

I took EXECUTE SQL TASK instead of Data Flow task in ForEachLoop Container and used dynamic SQL query that will generate a SQL insert statement as below. In the below query ExcelFileName changes based on the name from ForEach Loop container when it loops through source file folder. In this way there is not issue with the source columns datatypes or length as in Data Flow task. Hope it will be helpful to others.

INSERT INTO dbo.TempTable

(

Col1,Col2,Col3

)

SELECT Col1,Col2,Col3

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0 Xml;HDR=YES;Database=C:\TestFolder\ExcelFileName.xlsx',

'SELECT * FROM [SheetName$]'

)