I think you may have misunderstood what I was suggesting just a little bit.

I suggest that you use SSIS to organize the data in the files into the 2/3/4 "containers" first before you load any data into any tables.

You'll need to create a variable to hold the column count in your files, for now we'll make it package level and call it varColumnCount (int data type). You'll also need to create a few more package level variables - 1 to hold the original filename (varOrgFile), 1 for the path to your original directory (varOrdDir), and 1 for the path to your 2/3/4 directories (varDestDir). Seed these variables with real default values if possible.

You'll need to create a 1 Foreach Loop and put a data flow task and file system task inside the Foreach Loop. You'll also need to set the

value of the User::varOrgFile variable in the Foreach Loop.

For each of your intial files use the data flow task to bring the entire record into the dataflow as one column - I'll call it WholeRecord for now.

To simplify these suggestions - and since I don't have SSIS in front of me to verify/test things - I'll not suggest you bring in the first record of each file for now. Go ahead and bring in all the records from the files like you normally would.

Then use a derived column transformation to get the count of delimters (sounds like comma in your case). In the Derived column transformation write an expression similar to what your select statement looks like - something like this (I have not tested syntax nor results of the following expression - I'm also sure there are other expressions you could use to get the count of delimiters):

(len([WholeRecord]) - len(replace([WholeRecord], ",", "")) + 1

Select the user::variable to assign the results of the expression to - i.e. User::varColumnCount

Direct the ouput from this Derived Column transform to the new one you are about to create...

Create another Derived Column transform and use experssions and your variables to format the path to the 2/3/4 destiantion directories - something similar to:

user::varOrgDir + "\" + user::varColumnCount

Select the user::variable to assign the results of the expression to - i.e. User::varDestDir

Direct output of the Derived Column transform to a Recordset Destination or something similar - we don't care about this data yet, it served our purpose and we got the count of columns.

Back in the Control Flow - in the Foreach loop, use the File System Task to copy the file to the destination 2/3/4 directory. Use Expressions and your variables to determine which file to move (varOrgFile and varOrgDir) and where to move it (varDestDir and varOrgFile).

Now create 3 more Foreach loops - 1 each that will use a data flow task to loop through and process the files for each of your 2/3/4 directories.

That's about as much suggestion I can make from here on out, I hope it gives you enough to work with - or for others to read and suggest improvements 🙂

Good luck!