Shango avatar image
Shango asked

Processing a flat file with multiple formats and different sets of records in SSIS

I am trying to process a Pipped separated flat file using SSIS. The file has two different types of records. each record set has has different it's own header row and a trailer row. They need to go to two different table.
The trailer row has the row count for each record set while the header row contains column names for the records. So in essence it's like two types of files but in one file.
I've tried several solutions including using a conditional split but I haven't been able to achieve this. I know you could do this using a script component and C# but I haven't been able to achieve this. I've attached an image to show file format.
This is what I have tried so far.
1. I edited the flat file connection to ragged right so that the output comes in one column.
2. I then created a script component as source. The idea was to read the file line by line using stream reader,
   then create 4 output buffers. 2 for column headers and 2 for the different detail rows then set the script to stop when it reaches the trailer row.
   My intention was to merge each header row the respective detail rows then save them to the relevant tables. 
3. I then used C# code which I got from my research. I picked this up from the Microsoft site.I used the Code Below:

public class ScriptMain : UserComponent
    private StreamReader textReader;
    private string RTWFile;

    public override void AcquireConnections(object Transaction)
        IDTSConnectionManager100 connMgr = this.Connections.RTWCon;
        RTWFile = (string)connMgr.AcquireConnection(null);

    public override void PreExecute()
        textReader = new StreamReader(RTWFile);

    public override void CreateNewOutputRows()
        string nextLine;
        string[] columns;

        char[] delimiters;
        delimiters = "|".ToCharArray();

        nextLine = textReader.ReadLine();
        while (nextLine != null)
            columns = nextLine.Split(delimiters);
                HeadersBuffer.EmployeeNumber = columns[0];
                HeadersBuffer.LegacyStaffID = columns[1];
                HeadersBuffer.FirstName = columns[2];
                HeadersBuffer.LastName = columns[3];
                HeadersBuffer.PassportIssuingCountry = columns[4];
                HeadersBuffer.PassportType = columns[5];
                HeadersBuffer.PassportNumber = columns[6];
                HeadersBuffer.PassportIssuingAuthority =columns[7];           
                HeadersBuffer.PassportIssueDate = columns[8];
                HeadersBuffer.PassportExpirationDate = columns[9];
            nextLine = textReader.ReadLine();

    public override void PostExecute()
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers


Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.