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: 246979 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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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: 246979 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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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$]'

)