SSIS Derived Column Expression to strip double quotes from date column for flat file in SSIS Package. Every file doesn't have double quotes

Question

SSIS Derived Column Expression to strip double quotes from date column for flat file in SSIS Package. Every file doesn't have double quotes. The datatype is datetime and not varchar. I want to keep it datetime. What's the best way to strip the quotes from this column? Some of the files come over with quotes and some without. So, if the quotes are there, I need them stripped, if not, then it comes through fine with no error.

Answer 1

Create an SSIS variable of type string named TextQualifier

Define your flat file connection manager for whichever case you think is likely more common - the double quote or unquoted data. I'll assume double quote is more common and initialize the variable with the value of " Otherwise, initialize it with an empty string.

Right click on the Connection Manager and choose Properties. In the Properties menu, find Expressions. Click the ellipses ... and in the left hand side, select TextQualifier and in the right, you'll assign the variable @[User::TextQualifier]

A data flow with a Flat File Source to a Derived Column or Conditional Split with a Data Viewer between the two will show that we do/do not see the double quotes based on whether @[User::TextDelimiter] is blank or ".

The following images show the same file with the value of a text delimiter of blank vs the double quote.

Now that we have that working, the only thing left to do is to programmatically test what type of source file we're working with. This calls for a Script Task Read n first Characters of a big Text File - C#

We'll have our variable @[User::TextQualifier] set as a read/write variable. You'll need some way to tell the script task what the path is to the current file so I'll further assume this is inside a Foreach File Enumerator and the value of the current file name is in a variable called @[User::CurrentFileName]

using System;
using System.Data;
using System.IO;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_e869c568d3114d42b679805e45f0cabb
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            string filename = Dts.Variables["User::CurrentFileName"].Value.ToString();
            using (var stream = File.OpenRead(filename))
            {
                using (var reader = new StreamReader(stream))
                {
                    // Priming read to skip the header row
                    reader.ReadLine();

                    char[] buffer = new char[1];
                    int n = reader.ReadBlock(buffer, 0, 1);

                    // Did we encounter the double quote as the first character of the file?
                    if (buffer[0] == '"')
                    {
                        Dts.Variables["User::TextQualifier"].Value = "\"";
                    }
                    else
                    {
                        Dts.Variables["User::TextQualifier"].Value = "";
                    }

                }
            }

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

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
    }
}