Click here to Skip to main content
14,690,505 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello All,

I am trying to use a flat file source in SSIS to populate some database tables. I have used Cinchoo NACHA Reader to process an ACH file. The partial results of that file appear below.

-- ChoETL.NACHA.ChoNACHAFileHeaderRecord State --
RecordTypeCode: FileHeader
PriorityCode: 01
ImmediateDestination: 000000000
ImmediateOrigin: 000000000
FileCreationDate: 4/3/2017 12:00:00 AM
FileCreationTime: 8/10/2017 7:47:00 AM
FileIDModifier: L
RecordSize: 94
BlockingFactor: 10
FormatCode: 1
ImmediateDestinationName: Department Name
ImmediateOriginName: Bank Name
ReferenceCode:

I then use this file as a flat file source in SSIS and use a colon as delimiter. Which gives two columns. One with column names and one with values.

Column 0 Column 1
RecordTypeCode FileHeader
PriorityCode 1
ImmediateDestination 00000000
ImmediateOrigin 00000000
FileCreationDate 2/1/2017
FileCreationTime 8/11/2017
FileIDModifier L
RecordSize 94
BlockingFactor 10
FormatCode 1
ImmediateDestinationName Destination Name
ImmediateOriginName Origin Name
ReferenceCode

From here I add all of my desired outputs to the script component and apply the code below.

public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        string a = "-- ChoETL.NACHA.ChoNACHAFileHeaderRecord State --";
        string b = "-- ChoETL.NACHA.ChoNACHABatchHeaderRecord State --";
        string c = "-- ChoETL.NACHA.ChoNACHAEntryDetailRecord State -- ";
        string d = "-- ChoETL.NACHA.ChoNACHAAddendaRecord State --";
        string e = "-- ChoETL.NACHA.ChoNACHABatchControlRecord State --";
        string f = "-- ChoETL.NACHA.ChoNACHAFileControlRecord State --";
        
        
        if (Row.Column0 == a)
        {
            FileHeaderBuffer.AddRow();
        

        switch (Row.Column0.ToLower().Trim())
        {
            case "recordtypecode":
                FileHeaderBuffer.recordtypecode = Row.Column1.Trim();
                break;
            case "prioritycode":
                FileHeaderBuffer.prioritycode = Row.Column1.Trim();
                break;
            case "immediatedestination":
                FileHeaderBuffer.immediatedestination = Row.Column1.Trim();
                break;
            case "immediateorigin":
                FileHeaderBuffer.immediateorigin = Row.Column1.Trim();
                break;
            case "filecreationdate":
                FileHeaderBuffer.filecreationdate = Row.Column1.Trim();
                break;
            case "filecreationtime":
                FileHeaderBuffer.filecreationtime = Row.Column1.Trim();
                break;
            case "fileidmodifier":
                FileHeaderBuffer.fileidmodifier = Row.Column1.Trim();
                break;
            case "recordsize":
                FileHeaderBuffer.recordsize = Row.Column1.Trim();
                break;
            case "blockingfactor":
                FileHeaderBuffer.blockingfactor = Row.Column1.Trim();
                break;
            case "formatcode":
                FileHeaderBuffer.formatcode = Row.Column1.Trim();
                break;
            case "immediatedestinationname":
                FileHeaderBuffer.immediatedestinationname = Row.Column1.Trim();
                break;
            case "immediateoriginname":
                FileHeaderBuffer.immediateoriginname = Row.Column1.Trim();
                break;
            case "referencecode":
                FileHeaderBuffer.referencecode = Row.Column1.Trim();
                break;
        }
        
        {
            if (Row.Column0 == b)
            {
                BatchHeaderBuffer.AddRow();
            }

            switch (Row.Column0.ToLower().Trim())
            {
                case "recordtypecode":
                    BatchHeaderBuffer.recordtypecode = Row.Column1.Trim();
                    break;
                case "serviceclasscode":
                    BatchHeaderBuffer.serviceclasscode = Row.Column1.Trim();
                    break;
                case "companyname":
                    BatchHeaderBuffer.companyname = Row.Column1.Trim();
                    break;
                case "companydiscretionarydata":
                    BatchHeaderBuffer.companydiscretionarydata = Row.Column1.Trim();
                    break;
                case "companyid":
                    BatchHeaderBuffer.companyid = Row.Column1.Trim();
                    break;
                case "standardentryclasscode":
                    BatchHeaderBuffer.standardentryclasscode = Row.Column1.Trim();
                    break;
                case "companyentrydescription":
                    BatchHeaderBuffer.companyentrydescription = Row.Column1.Trim();
                    break;
                case "companydescriptivedate":
                    BatchHeaderBuffer.companydescriptivedate = Row.Column1.Trim();
                    break;
                case "effectiveentrydate":
                    BatchHeaderBuffer.effectiveentrydate = Row.Column1.Trim();
                    break;
                case "juliansettlementdate":
                    BatchHeaderBuffer.juliansettlementdate = Row.Column1.Trim();
                    break;
                case "originatorstatuscode":
                    BatchHeaderBuffer.originatorstatuscode = Row.Column1.Trim();
                    break;
                case "originatingdfiid":
                    BatchHeaderBuffer.originatingdfiid = Row.Column1.Trim();
                    break;
                case "batchnumber":
                    BatchHeaderBuffer.batchnumber = Row.Column1.Trim();
                    break;
            }
        
            {
                if (Row.Column0 == c)
                {
                    EntryDetailBuffer.AddRow();
                }

                switch (Row.Column0.ToLower().Trim())
                {
                    case "recordtypecode":
                        EntryDetailBuffer.recordtypecode = Row.Column1.Trim();
                        break;
                    case "transactioncode":
                        EntryDetailBuffer.transactioncode = Row.Column1.Trim();
                        break;
                    case "receivingdfiid":
                        EntryDetailBuffer.receivingdfiid = Row.Column1.Trim();
                        break;
                    case "checkdigit":
                        EntryDetailBuffer.checkdigit = Row.Column1.Trim();
                        break;
                    case "dfiaccountnumber":
                        EntryDetailBuffer.dfiaccountnumber = Row.Column1.Trim();
                        break;
                    case "amount":
                        EntryDetailBuffer.amount = Row.Column1.Trim();
                        break;
                    case "individualidnumber":
                        EntryDetailBuffer.individualidnumber = Row.Column1.Trim();
                        break;
                    case "individualname":
                        EntryDetailBuffer.individualname = Row.Column1.Trim();
                        break;
                    case "discretionarydata":
                        EntryDetailBuffer.discretionarydata = Row.Column1.Trim();
                        break;
                    case "addendarecordindicator":
                        EntryDetailBuffer.addendarecordindicator = Row.Column1.Trim();
                        break;
                    case "tracenumber":
                        EntryDetailBuffer.tracenumber = Row.Column1.Trim();
                        break;
            }
            
            {
                    if (Row.Column0 == d)
                    {
                        AddendaRecordBuffer.AddRow();
                    }

                    switch (Row.Column0.ToLower().Trim())
                    {
                        case "recordtypecode":
                            AddendaRecordBuffer.recordtypecode = Row.Column1.Trim();
                            break;
                        case "addendatypecode":
                            AddendaRecordBuffer.addendatypecode = Row.Column1.Trim();
                            break;
                        case "paymentrelatedinformation":
                            AddendaRecordBuffer.paymentrelatedinformation = Row.Column1.Trim();
                            break;
                        case "addendasequencenumber":
                            AddendaRecordBuffer.addendasequencenumber = Row.Column1.Trim();
                            break;
                        case "entrydetailsequencenumber":
                            AddendaRecordBuffer.entrydetailsequencenumber = Row.Column1.Trim();
                            break;
                    }
                    
                    {
                        if (Row.Column0 == e)
                        {
                            BatchControlBuffer.AddRow();
                        }

                        switch (Row.Column0.ToLower().Trim())
                        {
                            case "recordtypecode":
                                BatchControlBuffer.recordtypecode = Row.Column1.Trim();
                                break;
                            case "serviceclasscode":
                                BatchControlBuffer.serviceclasscode = Row.Column1.Trim();
                                break;
                            case "entryaddendacount":
                                BatchControlBuffer.entryaddendacount = Row.Column1.Trim();
                                break;
                            case "entryhash":
                                BatchControlBuffer.entryhash = Row.Column1.Trim();
                                break;
                            case "totaldebitentrydollaramount":
                                BatchControlBuffer.totaldebitentrydollaramount = Row.Column1.Trim();
                                break;
                            case "totalcreditentrydollaramount":
                                BatchControlBuffer.totalcreditentrydollaramount = Row.Column1.Trim();
                                break;
                            case "companyid":
                                BatchControlBuffer.companyid = Row.Column1.Trim();
                                break;
                            case "messageauthenticationcode":
                                BatchControlBuffer.messageauthenticationcode = Row.Column1.Trim();
                                break;
                            case "reserved":
                                BatchControlBuffer.reserved = Row.Column1.Trim();
                                break;
                            case "originatingdfiid":
                                BatchControlBuffer.originatingdfiid = Row.Column1.Trim();
                                break;
                            case "batchnumber":
                                BatchControlBuffer.batchnumber = Row.Column1.Trim();
                                break;
                        }
                        
                        {
                            if (Row.Column0 == f)
                            {
                                FileControlBuffer.AddRow();
                            }

                            switch (Row.Column0.ToLower().Trim())
                            {
                                case "recordtypecode":
                                    FileControlBuffer.recordtypecode = Row.Column1.ToUpper().Trim();
                                    break;
                                case "batchcount":
                                    FileControlBuffer.batchcount = Row.Column1.Trim();
                                    break;
                                case "blockcount":
                                    FileControlBuffer.blockcount = Row.Column1.Trim();
                                    break;
                                case "entryaddendacount":
                                    FileControlBuffer.entryaddendacount = Row.Column1.Trim();
                                    break;
                                case "entryhash":
                                    FileControlBuffer.entryhash = Row.Column1.Trim();
                                    break;
                                case "totaldebitentrydollaramount":
                                    FileControlBuffer.totaldebitentrydollaramount = Row.Column1.Trim();
                                    break;
                                case "totalcreditentrydollaramount":
                                    FileControlBuffer.totalcreditentrydollaramount = Row.Column1.Trim();
                                    break;
                                case "reserved":
                                    FileControlBuffer.reserved = Row.Column1.Trim();
                                    break;
                            }
                        }
                    }
                }
            }
        }
    }
    public override void Input0_ProcessInput(Input0Buffer Buffer)
    {
        while (Buffer.NextRow())
        {
            Input0_ProcessInputRow(Buffer);
        }

        if (Buffer.EndOfRowset())
        {
            FileHeaderBuffer.SetEndOfRowset();
            BatchHeaderBuffer.SetEndOfRowset();
            EntryDetailBuffer.SetEndOfRowset();
            BatchControlBuffer.SetEndOfRowset();
            FileControlBuffer.SetEndOfRowset();
        }
    }

    public override void CreateNewOutputRows()
    {
        BatchHeaderBuffer.AddRow();
        EntryDetailBuffer.AddRow();
        AddendaRecordBuffer.AddRow();
        BatchControlBuffer.AddRow();
        FileControlBuffer.AddRow();
    }

}


I have enabled a datareader from the flat file source to the script component and all rows are present. When the rows pass through the script component outputs to the tables, either the tables are populated with a single row from the bottom of the file or the correct number of rows are entered with NULL values. I am new to C# so I know I am doing something wrong, I just don’t know what it is. I believe it may have something to do with the syntax of my “if” statements or the duplicate column names that exist in the file. Any advice would be greatly appreciated.

What I have tried:

I used the articles below as a reference for my script component. I have tried both synchronous and asynchronous script components. I have also been researching conditional splits and derived columns, but I cannot figure out how to apply it to my data format.

How to use Script Component as Asynchronous Transformation
http://www.rad.pasfu.com/index.php?/archives/19-How-to-use-Script-Component-as-Asynchronous-Transformation.html

Parsing Non-Standard Text File Formats with the Script Component
https://technet.microsoft.com/en-us/library/ms345160(v=sql.110).aspx
Posted
Updated 7-Dec-17 4:02am

1 solution

Take great care over how you indent your code and errors will sometimes leap out at you.

For example look at the segment...
}
        
        {
            if (Row.Column0 == b)
            {
                BatchHeaderBuffer.AddRow();
            }

- That random open brace { on a line by itself is not necessary, but importantly Row.Column0 can never equal b because to get to this part of the code Row.Column0 == a. Check it out by debugging your code - this article will get you started Mastering Debugging in Visual Studio 2010 - A Beginner's Guide[^]

You are inconsistent in your structure - if the column == a you add a row then populate it in the same statement. But if it == b you only add a row and attempt to populate it outside the statement block ... which would just overwrite the previous values if the column != b. And thereby hangs your problem I believe - try putting the closing brace in the right place...
if (Row.Column0 == a)
{
    FileHeaderBuffer.AddRow();
}

It's also a lot easier to follow the flow of your code if you break stuff out into subroutines ... for example all of those switch statements can be extracted out to subroutines e.g.
public override void UpdateFileHeader(string testString, string valueString)
{
	switch (testString)
	{
		case "recordtypecode":
			FileHeaderBuffer.recordtypecode = valueString;
			break;
...etc.
Your Input0_ProcessInputRow then starts to look like...
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    string a = "-- ChoETL.NACHA.ChoNACHAFileHeaderRecord State --".ToLower().Trim();
    string b = "-- ChoETL.NACHA.ChoNACHABatchHeaderRecord State --".ToLower().Trim();
    string c = "-- ChoETL.NACHA.ChoNACHAEntryDetailRecord State -- ".ToLower().Trim();
    string d = "-- ChoETL.NACHA.ChoNACHAAddendaRecord State --".ToLower().Trim();
    string e = "-- ChoETL.NACHA.ChoNACHABatchControlRecord State --".ToLower().Trim();
    string f = "-- ChoETL.NACHA.ChoNACHAFileControlRecord State --".ToLower().Trim();
    
    if (Row.Column0.ToLower().Trim() == a)
    {
        FileHeaderBuffer.AddRow();
    }

    UpdateFileHeader(Row.Column0.ToLower().Trim(), Row.Column1.Trim());
	
    if (Row.Column0 == b)
    {
	BatchHeaderBuffer.AddRow();
    }

    UpdateBatchHeader(Row.Column0.ToLower().Trim(), Row.Column1.Trim());
	
    if (Row.Column0 == c)
    {
        EntryDetailBuffer.AddRow();
    }

    UpdateEntryDetail(Row.Column0.ToLower().Trim(), Row.Column1.Trim());
    
    if (testString == d)
    {
        AddendaRecordBuffer.AddRow();
    }

    UpdateAddendaRecord(Row.Column0.ToLower().Trim(), Row.Column1.Trim());
        
...etc
Now it becomes quite clear that you are attempting to update each buffer for every row in your file. You need to keep track of which "section" of the file you are processing and only call the appropriate subroutine. For example (NB untested)
const string a = "-- ChoETL.NACHA.ChoNACHAFileHeaderRecord State --";
const string b = "-- ChoETL.NACHA.ChoNACHABatchHeaderRecord State --";
const string c = "-- ChoETL.NACHA.ChoNACHAEntryDetailRecord State -- ";
const string d = "-- ChoETL.NACHA.ChoNACHAAddendaRecord State --";
const string e = "-- ChoETL.NACHA.ChoNACHABatchControlRecord State --";
const string f = "-- ChoETL.NACHA.ChoNACHAFileControlRecord State --";

public override void Input0_ProcessInputRow(Input0Buffer Row, out section)
{
switch(Row.Column0.ToLower().Trim())
{
    case a:
    FileHeaderBuffer.AddRow();
    section = 1;
    return;
    case b:
    BatchHeaderBuffer.AddRow();
    section = 2;
    return;
    case c:
    EntryDetailBuffer.AddRow();
    section = 3;
    return;
    case d:
    AddendaRecordBuffer.AddRow();
    section = 4;
    return;
    case e:
    BatchControlBuffer.AddRow();
    section = 5;
    return;
    case f:
    FileControlBuffer.AddRow();
    section = 6;
    return;
    default:
            string col0 = Row.Column0.ToLower().Trim();
            string col1 = Row.Column0.Trim();
    switch case (section)
    {
        case 1:
            UpdateFileHeader(col0,col1);
        break;
        case 2:
            UpdateBatchHeader(col0, col1);
        break;
        case 3:
        UpdateEntryDetail(col0,col1);
        break;
        case 4:
        UpdateAddendaRecord(col0,col1);
        break;
        case 5:
        UpdateBatchControl(col0,col1);
        break;
        case 6:
        UpdateFileControl(col0,col1);
        break;
    }
}
}
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900