-1

I'm using SSIS in Visual Studio 2019 to read fixed width text files. Depending on the first character of each row in the fixed width file, I'm sending the line to one of two specified outputs. Both outputs are configured with a length of 700, matching the text file fixed width.

The Script Component is programmed using C# and evaluates the first character of each line using a pair of if/else statements.

My question: the script provided below builds and the SSIS package runs successfully but writes 0 rows to the tables in the database. I think the root cause is calling line.Split(delimiters) and need guidance as to how to remedy the issue.

public class ScriptMain : UserComponent
{
    private StreamReader textReader;
    private string rxFile;

    private string stagingPath = "mypath";
    private char[] delimiters;

    public override void AcquireConnections(object Transaction)
    {
        IDTSConnectionManager100 connMgr = this.Connections.TransactionFile;
        rxFile = (string)connMgr.AcquireConnection(null);
    }

    public override void PreExecute()
    {
        base.PreExecute();
        textReader = new StreamReader(rxFile);
    }

    public override void CreateNewOutputRows()
    {
        DirectoryInfo di = new DirectoryInfo(stagingPath);
        FileInfo[] files = di.GetFiles("*.txt");

        foreach (FileInfo file in files)
        {
            using (FileStream fileStream = file.OpenRead())
            using (textReader)
            {
                while (!textReader.EndOfStream)
                {
                    string line = textReader.ReadLine();
                    delimiters = Environment.NewLine.ToCharArray();
                    string[] items = line.Split(delimiters);

                    if (items[0] == "2")
                    {
                        PharmacyBuffer.AddRow();
                        PharmacyBuffer.Column0 = items[0];
                    }
                    else if (items[0] == "4")
                    {
                        TransactionsBuffer.AddRow();
                        TransactionsBuffer.Column0 = items[0];
                    }
                }
            }
        }

        textReader.Close();
        textReader.Dispose();
    }

    public override void PostExecute()
    {
        base.PostExecute();
    }
}
5
  • This question could benefit from more debugging details. Specifically, does string[] items contain the values you expect? Are the conditional statements associated with the content of items flowing as you expect? And finally, when you add a result viewer or test variable to inspect the output of this script task, does it contain the results you expect? Please edit your question and provide more debugging details like this. Commented Jul 16, 2024 at 16:54
  • I am looking at your code and it is a bit confusing. If you need to just check the first character in the line, why not take the string line, and then parse out the first character from that? Also a few other things look weird/incorrect (but may work). When I have issues with SSIS C# script tasks, I create them in a console app and test/debug there and it is MUCH step through to see what is going on and what each line is doing and if you are actually getting to a specific spot in your code. Commented Jul 16, 2024 at 20:03
  • Have you looked more closely at your usages of fileStream (no usages) and textReader (only the transaction file)? Commented Jul 16, 2024 at 21:43
  • SSIS script components are next to impossible to debug, it's almost always easier to have well-tested functions in GAC-based assemblies and just orchestrate them from SSIS script components. Commented Jul 16, 2024 at 21:45
  • @AlwaysLearning - This has not been my experience? I drop a break point and step through each line of code in my script tasks, just like how I debug non-SSIS c# code in Visual Studio. There are some cases where the break point won't hit, but it should be possible to do. Commented Jul 16, 2024 at 23:12

1 Answer 1

0

I appreciate the comments you've provided. I've simplified logic as follows:

public override void CreateNewOutputRows()
{
    DirectoryInfo di = new DirectoryInfo(stagingPath);
    FileInfo[] files = di.GetFiles("*.txt");

    foreach (FileInfo file in files)
    {
        using (FileStream fileStream = file.OpenRead())
        using (textReader)
        {

            while (!textReader.EndOfStream)
            {
                string line = textReader.ReadLine();
                if(line.Substring(0,1) == "2")
                {
                    PharmacyBuffer.AddRow();
                    PharmacyBuffer.Column0 = line;
                }
                if(line.Substring(0,1) == "4")
                {
                    TransactionsBuffer.AddRow();
                    TransactionsBuffer.Column0 = line;
                }
            }
        }
    }
    textReader.Close();
    textReader.Dispose();
}

I ran the data flow task and it processed the data as intended.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.