SSIS 2012 - Using the Flat File Source with different number of columns in rows

Question

Hi,

In SSIS 2012, the 'Flat File Source' supports files that have different number of columns in different rows. That being said, it appears as though when creating the connection, the number of columns defined is obtained from the first row only. For example, the following flat file would process correctly:

Peter,45,a,b,c,d,e
Lois,f,h,i,j
Brian,7,k,l,m,n,o
Chris,q,r,s,t
Meg,15,u,v,x,y
Stewie,2,z,b,c

because each of these rows have the same or less number of columns than the first row. However this file doesnt seem to process correctly:

Peter,45,a,b,c,d,e
Lois,40,f,g,h,i,j,dd
Brian,7,k,l,m,n,o
Chris,16,p,q,r,s,t,aa,ab
Meg,15,u,v,w,x,y
Stewie,2,z,a,b,c,d

because lines 2 and 4 have more columns that the first row. The package does not error, its just the last column will contain 'jdd' for row 2 and 'taaab' for row 4.

Is there a configuration setting that I missed that will handle this automatically or will I need to write a custom source component to handle this? I am able to determine the maximum number of columns in any row and add the columns in the Flat File Connection Manager manually.

Answer 1

You can edit the flat file connection manager and add columns:

---

Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

Answer 2

Hi Russ,

Thanks for your response, but I did mention that I could "add the columns in the Flat File Connection Manager manually". I take it that the component will not handle this dynamically?

Answer 3

Hi James,

It works fine if you place the column header as following

1,2,3,4,5,6,7,8,9
Peter,45,a,b,c,d,e
Lois,40,f,g,h,i,j,dd
Chris,16,p,q,r,s,t,aa,ab
Brian,7,k,l,m,n,o
Meg,15,u,v,w,x,y
Stewie,2,z,a,b,c,d

or second option if you place the max columns rows on top like:

Chris,16,p,q,r,s,t,aa,ab
Peter,45,a,b,c,d,e
Lois,40,f,g,h,i,j,dd
Brian,7,k,l,m,n,o
Meg,15,u,v,w,x,y
Stewie,2,z,a,b,c,d

i know, it's weired and flat file yet not supporting the dynamic column behaviour.

Kind Regards,

Zaim Raza.

Answer 4

Missed that point.  I assume that it won't.  I think that they look at the first row for the number of columns and then if you like it uses the parsed values to scan for the data type.

---

Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

Answer 5

If you look into the following tutorial then they use the term "Ragged-Right" which i assume that header must contains the all the required header details:

http://blogs.msdn.com/b/mattm/archive/2011/07/17/flat-file-source-changes-in-denali.aspx

Answer 6

Thanks Zaim. At this point I am going to write a custom component to do this, as carrying out your method is not really suitable with the real data I'm working with. When/If I finish it I'll post it on codeplex unless someone beats me to it.

Thanks everyone for your help.

Answer 7

Hi James,

Please have a look into the following article. which explain the custom way to import the dynamic columns:

http://www.timmitchell.net/post/2013/01/14/ragged-flat-file-processing-in-ssis/

regards,

Zaim Raza