SSIS – How to Parse Extensions from Path-Filename Strings

For something like this the optimal solution of course is to control your input. That said, the reality is you have to parse the supplied input.

For something as complex as your parsing, I'd skip the Derived Column transformation and go straight for a Script transformation. I select my source column, Input and create three output columns: number, trash and Interval. number and Interval will hold the parsed values while trash will only be populated when the script can't make heads or tails from the input.

I use two member variables, numbersRegex and periodDomain. periodDomain is just a list with the acceptable values. For string comparisons, I force everything to lowercase and hope for English. numbersRegex is a regular expression that is used to identify digits in a string.

For every row that comes in, the script will split the Input value based on whitespace. For each of those tokens, I test whether the token has a digit in it. If it does, we'll call the GetBiggestNumber method. Otherwise, we'll call the ValidatePeriodDomain Once all the tokens have been processed, then it's important to make certain both values have been set.

GetBiggestNumber attempts to look at all the groupings of number and find the largest set.

ValidatePeriodDomain attempts to compare the current value to a known list of acceptable values.

using System.Text.RegularExpressions;
using System.Collections.Generic;

/// <summary>
/// The amazing script transformation
/// </summary>
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    Regex numbersRegex;
    List<string> periodDomain;

    /// <summary>
    /// Initialize member variables
    /// </summary>
    public override void PreExecute()
    {
        base.PreExecute();
        // match consecutive digits
        this.numbersRegex = new Regex(@"\d+", RegexOptions.Compiled );
        this.periodDomain = new List<string>(){ "year", "month" };
    }

    /// <summary>
    /// Parse the incoming data
    /// </summary>
    /// <param name="Row">The row that is currently passing through the component</param>
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        string[] parts = Row.Input.Split();
        string period = string.Empty;
        int? foo = null;

        foreach (string token in parts)
        {
            // try to do something with it
            // If the token has a digit in it, then we'll extract the largest value
            // if no digits, then the first token matching our domain is preserved
            if (this.numbersRegex.IsMatch(token))
            {
                foo = GetBiggestNumber(token);
            }
            else
            {
                if (ValidatePeriodDomain(token))
                {
                    period = token;
                }
            }
        }

        // at this point, we've processed the input data
        // If the local variables are in their initial states, then we didn't find
        // anything of note and need to populate the Row.Junk column
        // Why local variables, because can't read from Row.column
        if (period == string.Empty || (foo == null))
        {
            Row.trash = Row.Input;
        }
                    else
                    {
            Row.number = foo.Value;
            Row.Interval = period;
                    }
    }

    private bool ValidatePeriodDomain(string token)
    {
        return (this.periodDomain.Contains(token.ToLower()));
    }

    private int? GetBiggestNumber(string token)
    {
        int? bigOne = null;
        int? current = null;
        // Get all the groups of numbers and compare them
        foreach (Match item in this.numbersRegex.Matches(token))
        {
            current = int.Parse(item.Value);
            if (!bigOne.HasValue)
            {
                bigOne = current;
            }

            if (current.Value > bigOne.Value)
            {
                bigOne = current;
            }
        }

        return bigOne;
    }
}

Using the above script, you can see how it slices and dices the Input data. I made a minor change between the code that generated the below screenshot and what's posted. I observed that the input value 9000 was assigned to Row.number but as that Row never had an Interval assigned, I deferred the actual Row population to the end of the script (it was in the

Mapped drives, like P:\ in your case are dependent on the user logged in. Just because you can see the drive letter when logged into the server machine does not mean SQL Server can "see" the drive letter. If you specify the UNC (Universal Naming Convention) name in the path instead of using the drive letter, and the account SQL Server is using has access to the share and path, then it will work.

Instead of P:\mypath\myfile.txt use \\machine\share\mypath\myfile.txt