Handling Inconsistent Delimiters in Flat File Source on ForeachLoop Container

Question

I'm trying to handle inconsistent delimiters in 'n Flat File Source contained in a Data Flow Task running in a Foreach Loop container in SSIS.

I have several files in a folder with varying names but with one consistent identifier e.g. File23998723.txt File39872397.txt File29387234.txt etc., etc.

These files, as a standard should be tab delimited, but every so often a user missed cleaning up a file and it will be delimited with a , or a ; etc., which causes the package import to fail.

Is there an easy approach for me to follow to dynamically change the delimiter or to test for the delimiter beforehand?

Answer 1

I managed to handle it with a script task, thanks!

Basically added a script task to the Foreach Loop Container that executes before my DataFlow task.

I send the file name through as a variable:

I added the following namespaces to the script:

using System.IO;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;

And my script looks like this:

public void Main()
    {
        if (!string.IsNullOrEmpty(Dts.Variables["sFileName"].Value.ToString()))
        {
            StreamReader file = new StreamReader(Dts.Variables["sFileName"].Value.ToString());

            if (file != null)
            {
                string HeadRowDelimiter = "";
                string ColDelimiter = "";
                string data = "";

                while (file.Peek() >= -1)
                {
                    char[] c = new char[500];
                    file.Read(c, 0, c.Length);

                    data = string.Join("", c);

                    if (!string.IsNullOrEmpty(data))
                    {
                        //set row delimiters
                        if (data.Contains("\r\n"))
                        {
                            HeadRowDelimiter = "\r\n";
                        }
                        else if (data.Contains("\r"))
                        {
                            HeadRowDelimiter = "\r";
                        }
                        else if (data.Contains("\n"))
                        {
                            HeadRowDelimiter = "\n";
                        }
                        else if (data.Contains("\0"))
                        {
                            HeadRowDelimiter = "\0";
                        }

                        //set column delimiters 
                        if (data.Contains("\t"))
                        {
                            ColDelimiter = "\t";
                        }
                        else if (data.Contains(";"))
                        {
                            ColDelimiter = ";";
                        }
                        else if (data.Contains(","))
                        {
                            ColDelimiter = ",";
                        }
                        else if (data.Contains(":"))
                        {
                            ColDelimiter = ":";
                        }
                        else if (data.Contains("|"))
                        {
                            ColDelimiter = "|";
                        }
                        else if (data.Contains("\0"))
                        {
                            ColDelimiter = "\0";
                        }
                    }

                    break;
                }
                file.Close();

                RuntimeWrapper.IDTSConnectionManagerFlatFile100 flatFileConnection = Dts.Connections["FlatFileConnection"].InnerObject as RuntimeWrapper.IDTSConnectionManagerFlatFile100;

                if (flatFileConnection != null)
                {

                    flatFileConnection.HeaderRowDelimiter = HeadRowDelimiter;
                    flatFileConnection.RowDelimiter = HeadRowDelimiter;
                    flatFileConnection.HeaderRowsToSkip = 0;
                    flatFileConnection.Columns[0].ColumnDelimiter = ColDelimiter;
                }

                Dts.TaskResult = (int)ScriptResults.Success;
            }
        }
    }