there are a number of posts by Jeff Moden to do this via BCP; he refer's to the issue as "ragged right";

here's one example he posted to address the issue...it's not SSIS, but it's worth looking at:

--Originally posted by Jeff Moden somewhere in the sands of time:

BCP file import headless ragged right

Heh... that's known as a "Headerless Ragged Right" file and they're a bugger to import correctly. BCP and BULK insert require that all the rows have the same number of delimiters so you can't do an import of such a file with either.

However... with a little help from a couple of DOS commands and a little prestidigitation with a text based linked server, it can be done with very little pain.

First, I assume you have a file, like in your original post, in C:\Temp called Test01.txt and it looks like this...

col1,col2,col3

col1,col2

col1,col2,col3,col4

col1,col2,col,3,col4,col5

Next, let's setup a linked server and give it the necessary login privs...

--===== Create a linked server to the drive and path you desire.

EXEC dbo.sp_AddLinkedServer TxtSvr,

'Jet 4.0',

'Microsoft.Jet.OLEDB.4.0',

'C:\Temp',

NULL,

'Text'

--===== Set up login mappings.

EXEC dbo.sp_AddLinkedSrvLogin TxtSvr, FALSE, NULL, Admin, NULL

Here comes the fun part... if we just read the file directly...

--===== Query one of the files by using a four-part name.

SELECT *

FROM TxtSvr...[Test01^#txt]

... we get an awful mess that looks like this...

F4 F5 col1 col2 col3

col1 col2

col4 col1 col2 col3

col4 col5 col1 col2 col3

(3 row(s) affected)

notice that the first row was used as a header and that the data has been rearranged AND that two of the headers have been made up! We can fix that with a couple of simple DOS commands...

--===== Create a header that identifies the columns we want... (any col names will do)

EXEC Master.dbo.xp_CmdShell 'ECHO HdrCol1,HdrCol2,HdrCol3,HdrCol4,HdrCol5 > C:\Temp\Header.txt'

--===== Create a new working file that has the header and the original file as one

EXEC Master.dbo.xp_CmdShell 'COPY C:\Temp\Header.txt+C:\Temp\Test01.txt C:\Temp\MyWork.txt'

Then, accessing the data in the file is a breeze... do with it what you want because it can now be referenced as a table with the correct column names...

--===== Read the csv text file as if it were a table

SELECT *

FROM TxtSvr...[MyWork^#txt]

HdrCol1 HdrCol2 HdrCol3 HdrCol4 HdrCol5

col1 col2 col3

col1 col2

col1 col2 col3 col4

col1 col2 col3 col4 col5

(4 row(s) affected)

If you need to drop the linked server after than, the following command will do nicely...

EXEC dbo.sp_DropServer 'TxtSvr', 'DropLogins'