SSIS Package failing with New structure of Flatfile

SSIS package is just importing from txt file to sql database. when we made the package were using old file and its executing fine.the old source file got (10 columns) the new source file got 15 columns. when the source file changed its failing. [Flat File Source [1]] Error: Data conversion failed. The data conversion for column "Column 10" returned status value 4 and status text "Text was truncated or on开发者_运维技巧e or more characters had no match in the target code page.". Variation in the columns made a problem how to resolve this in better way? If both old and new format files need to be processed with same Package.

Thanks

---

If I understand your question correctly, you have a file (same file name I assume) that either has the old or new file format and fails because your flat file source has the old (10 column) data file schema only? If this is the case, I would create a boolean variable and call it something like isOldFormat. I would then use a script task in your control flow to determine if it has 10 or 15 columns. The pseudocode would be something like this:

1)Open flat file 2) Count the columns based on your delimiter 3) Condition Statment:

If columns.Count = 10 isOldFormat = True Else If columns.Count = 15 isOldFormat = False Else throw error

Then I would create another data flow that would have the new file format schema (now basically you have two data flows-one with your old file format and one with the new one).

After this step, you would then drag precedence constraint from your script task to the newly created data flow and one to the old data flow. By double clicking on your predence constraint, you would then set the evaluator operation property to Expression and type in the Expression box @isOldFormat == true for the constraint going to the data flow that contains the old flat file source and @isOldFormat == false for the other data flow. What this will do will only execute one or the other data flow based on variable that is set in your script task.

Hope this helps.