Re: SSIS - Flat Files

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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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,

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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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
.

Relevant Pages

  • Re: SSIS - Flat Files
    ... It would be easier to specify the start and end of each column than adding ... My main problem is creating half a dozen flat file connections with hundreds ... An easy method to import from some sort of format file would be great - ... Is there an easy method to create a flat file connection from these format ...
    (microsoft.public.sqlserver.dts)
  • field tranformation during import using format file?
    ... I am using BULK INSERT with a format file to import lots of records from a ... I tried mapping fields in the flat file to the table fields but it caused ... I did not know how to write the transformation statements. ... Is there a way to do the same thing during BULK INSERT or use the format ...
    (microsoft.public.sqlserver.programming)
  • Bulk insert DTS task
    ... I am wanting to to a bulk insert task in a DTS package. ... must transfer data from a flat file that has 27 columns delimited by the ~ ... Does anyone have an example of a format file for this process? ... original 27 column format file is below as well as my changed format file. ...
    (microsoft.public.sqlserver.programming)
  • Bulk Insert Task
    ... I am wanting to to a bulk insert task in a DTS package. ... This bulk insert must transfer data from a flat file that ... columns i want using a format file. ... format file is below as well as my changed format file. ...
    (microsoft.public.sqlserver.dts)
  • Re: Creating Flat File Specifications For Biztalk
    ... because I can specify multiple student records in this way. ... > I specified schema delimiter as. ... > and for records I specified / as delimiter and type is infix. ... > patter of flat file? ...
    (microsoft.public.biztalk.general)
Loading