SSIS Export Flat File as Fixed Width

  • Oscar_Boots

    Old Hand

    Points: 313

    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

     

  • Jeffrey Williams

    SSC Guru

    Points: 88558

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • Phil Parkin

    SSC Guru

    Points: 244599

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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Jeff Moden

    SSC Guru

    Points: 996676

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • frederico_fonseca

    SSChampion

    Points: 14687

    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: 996676

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Jeffrey Williams

    SSC Guru

    Points: 88558

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • Jeffrey Williams

    SSC Guru

    Points: 88558

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • frederico_fonseca

    SSChampion

    Points: 14687

    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: 996676

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Oscar_Boots

    Old Hand

    Points: 313

    Thanks Jeffrey,

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

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

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