SSIS Export Flat File as Fixed Width

Question

SSIS Export Flat File as Fixed Width

Oscar_Boots

Old Hand

Points: 362
More actions
May 25, 2020 at 11:24 am

#3757199

Hi Forum,
I have a table that uses the script below to create fields with Spaces at the start of the field value.
The way this works is if,for example, a field length is set at 12 characters and the value for the field is 8 characters long, it should have 4 spaces at the start - 8 + 4 = 12.
This is a requirement of the database I'm loading into.
So now I've got all my field lengths sorted out, I need to export this table as a Flat File and maintain the spaces & characters as I've defined them.
To do this, I've selected in the Flat File Destination settings to export as a 'Fixed Width' file.
I found out after getting an error that any Field Heading that is longer than the Length set for that field.
That meant that if a field had a set length of 1, the field heading could only be 1 character long.
Obviously this has caused some problems which I'm trying to avoid.
My question is - Would I still retain my spaces & field name formatting if I didn't select the 'Fixed Width' option when I set the options for exporting as a flat file?
Has anyone come across this before & if so, how did you work around it?
Thanks Peter

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

I did not follow everything you did...it seems that your real problem is how to use SSIS to export to a fixed width file.

First, a fixed width file generally does not contain column headers - those are supplied in a document defining the start/stop/length and data type of each field in the file.

Second - manipulating the column data in SQL should not be necessary. In a fixed width file - string data is left-aligned and numeric data is right-aligned (with leading zeroes out to the full length of the field).

From what I get from your post...you have data that must be set with leading spaces? That is a most unusual requirement...if that is truly the case then I am not sure how the system receiving this data could accurately process the fields. The would have to use RTRIM/LTRIM type functions on every field...

In SSIS - when exporting to a file for fixed width, you define the start and end (or length) and that field will be written to the file from those positions. For example, if the first field is 10 characters long and a string and the field is defined as 15 characters long - the second field is a number with 8 digits - what is written is: ABCDEFGHIJ 00001000.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

This is unworkable. Surely it is clear that a fixed-width file cannot have headings which are wider than the length of their column?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

I agree with Phil. Headings longer than the column width of a fixed field format file just aren't going to work. For such files, folks usually don't include a header especially since the receiver of the file supposedly knows what the structure of the file actually is either through documentation or previous agreement.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

frederico_fonseca SSCoach Points: 16154 More actions · Answer 4

well.... kind of no headers as such.. but for many such files you do have a "header" record with a particular fixed format, then "data" records with their own format(s), then a "trailer" record with yet another fixed format.

but assuming that we are talking about a file with a single fixed size format then NO headers is the standard.

Should a header be required, it can be done - but then the file on SSIS is defined as a single column, size 5000 (as an example) ragged right.

And the SQL builds the record according to the fixed size definition.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

Just as a side bar, I generally don't mix meta data (headers/footers/line counts, whatever) in fixed field format files simply because most people on the receiving end have problems dealing with it even if they have the right tools to deal with it. Instead, I'll also send a "manifest" file named the same as the data file but with a file extension of ".manifest" or whatever the receiver wants.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

frederico_fonseca wrote:

well.... kind of no headers as such.. but for many such files you do have a "header" record with a particular fixed format, then "data" records with their own format(s), then a "trailer" record with yet another fixed format.
but assuming that we are talking about a file with a single fixed size format then NO headers is the standard.
Should a header be required, it can be done - but then the file on SSIS is defined as a single column, size 5000 (as an example) ragged right.
And the SQL builds the record according to the fixed size definition.

Or, you build 3 separate file connections and the final step combines them into a single file...

Or, you have 3 separate file connections pointing to the same filename - with the first output building the header record, the second file connection writing out the data rows and the final output building the trailer record.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

Jeff Moden wrote:

Just as a side bar, I generally don't mix meta data (headers/footers/line counts, whatever) in fixed field format files simply because most people on the receiving end have problems dealing with it even if they have the right tools to deal with it. Instead, I'll also send a "manifest" file named the same as the data file but with a file extension of ".manifest" or whatever the receiver wants.

That is how I normally receive these types of files - and it is very easy to setup the load process once you have that information (regardless of tool used).

A further problem with the OP's request is that they want strings with leading spaces. Not sure why that would be a requirement.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

frederico_fonseca SSCoach Points: 16154 More actions · Answer 8

Jeffrey Williams wrote:

A further problem with the OP's request is that they want strings with leading spaces. Not sure why that would be a requirement.

I would almost bet that someone "derived" a record definition from another file - and it happens that those 4 positions are empty and someone decided that they belonged to the Next field, and not to the previous field (or to an "filler" field)

Too many years working with COBOL (mainframe and PC based) to have people supplying these formats and having to live with them (or even supplying them - I remember Basel II and files to supply were fixed format with lots of record types on same file)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

Might have even been a "Line#" column so you could verify that no lines were lost during transmission and to also guarantee a sort order if that was important to the file. If they did so in text, the might have had to remove it because the files continued to grow past the "9999" mark. Either that or maybe it was a 4 byte binary for such a thing and that blew people away because they didn't know how to import it as an integer.

I do actually like fixed field format because it's nasty fast to parse and import.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Oscar_Boots Old Hand Points: 362 More actions · Answer 10

Thanks Jeffrey,

That's it! I knew there was a logical explanation, so I'll leave the headings off for this process. Thanks again.