Post reply

Script Component: Creating a multilayout flat .txt file with three input files.

  • July 29, 2016 at 12:35 am

    #400486

    I need to create a nonstandard .txt file - mainframe style with a header row, group record, then detail records. First column is the record id which should not have a text identifier(") but any other text should, comma separated values. Each record type has different number of columns. One record for each type except the detail records.

    Have to do this quickly, so thinking in sql - ssis with a script component, but I've never tried this before. Anyone have a good starting point for getting up to speed on creating and using this component for exporting a non standard text file with 3 record types.

    Thanks in advance!

  • August 18, 2016 at 2:03 am

    #1896084

    easiest way is to build the records on SQL and then just output to the flat file as a single column.

    One approach I've taken on this is to have a table with following layout (shortened for this example)

    sortorder1

    sortorder2

    rownumber

    datarecord

    sortorder 1 and 2 are used to sort the records on the output file - mandatory for the type of files you mentioned as header(s) need to be first, then data records, then footer

    reason for 2 sortorder records is to group data records - e.g. in many mainframe files you have multiple records that form a single logical record and must be processed together.

    population of the data record is just a concatenation of all required data with formatting as required, e.g. dates on particular format, numbers on desired format, with leading/trailing sign, with or without decimal separator, with or without quotes around fields and so on.

    a typical select to populate the table would be

    select row_number ... as sortorder1

    , ... as sortorder2

    , dbo.quotestring(dbo.formatstring(accountid,size)) -- where Y means quotes and N means noquotes

    + dbo.quotestring(dbo.formatdate(duedate,'format'))

    + dbo.quotestring(dbo.formatnumber(priceused,7,2,'+','L','N')) -- where 7 means number of integer positions, 2 means number of decimal positions, + means output positive sign, L means sign on left, N means no decimal separator

    benefit of this approach is that you can do the data records first, then process those records and calculate both header and trailer rows in one go and still be sure that they will be sorted on the output file correctly.

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

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