We are developing a process to import data from a CSV file that we are designing. Although right now our CSV file has 23 columns, over time we will gradually be adding more columns (roughly 1 to 2 more columns per month). Although the process has worked great I have run into a snag. When we initially set up the connection manager we went in to the "Advanced" section to specifically set theoutputcolumnwidthfield sizes (they all defaulted to 50). The problem is that whenever we modify this connection manager to accept the new columns that we add to the CSV file it resets all of theoutputcolumnwidth values to 50 no matter how we try it. Is there some way to make them keep the values that we set? For example, our first field is "ID" and we initially changed it from "50" (the default) to "15". The next month when we added two new columns, the "15" got changed back to "50" and we had to set it again. This happens for all of the already-specified fields. If there is any additional information that I can provide please let me know.

My solution is to use Data Conversion transformation just after "Flat File Source" \ OLE DB Source \ XML Source etc transformations. In your Data Conversion transformation you can define all types and column sizes (with "Ignore on truncate") which dont change even if you modify your source transformation. Hope that will help you.

The problem is when there are 474 columns in your input file (like mine today!). Manually changing the column lengths takes ages. "Suggest Types" works when there is only a single file to import that SSIS can look through - what if there is a set of 10 (or 100) files? In the case of multiple input files Suggest Types only goes so far... Microsoft really needs to come up with ways of improving its basic Import from Flat File functionality...

I had a similar grumble regarding changing the column length - it does indeed take ages. I didn't spend too much time on investigating, but instead just edited the XML of the dtsx file (using a good old fashioned Find/Replace). I dare say not MS best practice but it certainly did the job for me!! Perhaps raise a feature request in connect.microsoft.com/SqlServer?every day is a school day

but instead just edited the XML of the dtsx file (using a good old fashioned Find/Replace). Ah nice! Wish I'd known that yesterday! Will keep in mind though...

Also I've raised (actually bumped up an existing) request on connect, and I suggest you/others who read do too and Vote it up so that Microsoft will fix this as soon as possible: https://connect.microsoft.com/SQLServer/feedback/details/361258/changing-flat-file-columns-to-match-target-schema?ppud=0&wa=wsignin1.0#

I can't recall this happening to me - but if it is to you, that's maddening. I believe there's a tool in BIDSHelper that makes specifying column sizes on Flat File Connection Managers easier... check it out. Talk to me now on

Thanks for the tip on BIDS Helper - looks like a useful plugin to install. http://bidshelper.codeplex.com/