I've started learning SSIS and I'd like to replace our import tool with an
SSIS project. The data warehouse is populated from flat files (fixed width
format) - there are a handful of different formats and all the files are
quite wide ~300 columns. I already have custom format files used by our
current import tool - these are stored as csv files that specify column
names, start, end and length of each column. e.g.

; Comments at start of file starting with a semi-colon
columnname, start, end, length
MyColumnName, 1, 2, 2
MyColumnName2, 3, 6, 4

Is there an easy method to create a flat file connection from these format
files? It would be time consuming to create these by hand - Is there an easy
way to create these connections. I know you can create custom data sources -
the existing flat file data source is fine, but an easy method to enter the
specification would be useful.

Many Thanks,

David

David,

I'm not aware of any easy/automatic way to use your existing custom
format files through the SSIS Designer.

When creating a Flat File Connection Manager specify fixed width in
the Format dropdown on the General tab. Then on the Columns tab use
the GUI to define column widths after defining row width.

Andrew Watt [MVP]

On Wed, 19 Apr 2006 14:41:01 -0700, Wiseman82
<Wiseman82 (AT) discussions (DOT) microsoft.com> wrote:

Thanks Andrew. I was hoping I could somehow write a script to create the
connection programatically using the csv file to define the columns and
column widths...
I know MS has made some improvements to importing flat files in SSIS - the
interface is fine for smaller (narrower) flat files, but it's a bit clunky
for larger (wider) flat files (especially if you already have a format file
of some description).

If any MS employees working on the next version of SSIS are reading this
thread:

* An easy method to import from some sort of format file would be great -
I'm sure people can massage their own format files into a "Microsoft"
compatable format file.
* It would be useful to be able to specify start and end positions for your
columns instead of just the width. I think its easier to specify this way
and it saves you from creating "dummy" columns to fill in gaps of data stored
in the flat file that you are not interested in.
* The interface could be improved to allow you to move columns up/down.

If anyone has any other suggestions, please let me know,

Thanks,

David

"Andrew Watt [MVP]" wrote:

David,

Re your second bullet point. Would unchecking specific columns in the
Columns tab of the Flat File Source Editor not do what you want re
unwanted columns?

Andrew Watt [MVP]

On Thu, 20 Apr 2006 09:55:03 -0700, Wiseman82
<Wiseman82 (AT) discussions (DOT) microsoft.com> wrote:

Thanks again Andrew. You are quite right - this is how I've been doing it.
It would be easier to specify the start and end of each column than adding
dummy columns and removing the checkbox - It's not much of a problem really.
My main problem is creating half a dozen flat file connections with hundreds
of columns to specify for each of them - As a programmer I don't like to do
stuff by hand if it can be automated in some way. (I'm lazy and it also
saves on human input error)
At least it's easy to copy these connections between packages once they have
been created!

"Andrew Watt [MVP]" wrote: