SSIS – Fix Missing File Extension in Script Task

cssis

Hi I have problem with script task C# I create I have to prefix log file with date but my problem is not adding file extension
log file is log_yyyy_MM_dd without extension
I want ti extension
log file is log_yyyy_MM_dd.txt or CSV

    private string ReadVariable(string varName)
    {
        object rtnValue = null;
        Variables var = default(Variables);
        try
        {
            Dts.VariableDispenser.LockOneForRead(varName, ref var);
            rtnValue = var[varName].Value.ToString();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            var.Unlock();
        }
        return rtnValue.ToString();
    }


    private void WriteVariable(string varName, object value)
    {
        Variables var = default(Variables);
        try
        {
            Dts.VariableDispenser.LockOneForWrite(varName, ref var);
            var[varName].Value = value;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            var.Unlock();
        }
    }

    public void Main()
    {
        //Read Variables
        String sFlatFileConnection = ReadVariable("FlatFileConnection");



        //Get Run Date
        DateTime dtRunDate = DateTime.Now;

        sFlatFileConnection = sFlatFileConnection + dtRunDate.ToString("yyyy_MM_dd");
        WriteVariable("FlatFileConnection", sFlatFileConnection);

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

Best Answer

The more SSISic way of doing this would be through Expressions. Despite seeing multiple variables here, don't worry about them. I have found that when people try to do too much with an Expression, diagnosing and debugging bad logic will leave you reaching for the nearest blunt object.

Variables

  • FlatFileConnection, string. Value = "C:\ssisdata\so\input\log.txt"
  • ArchiveName, string. Evaluate As Expression = True.
  • FormatDate, string. Evaluate As Expression = True.
  • FileExtensionPosition, int32, Value = 0. Evaluate As Expression = True;

The FileExtensionPosition is going to determine the last occurrence of . in a string.

The lazy trick I use for finding the last X in a string is to reverse it. It's then the first element in the string and I can pass 1 as the final parameter to FindString.

FileExtensionPosition

This could be overkill but I see this question as helpful to anyone trying to insert a value before the file extension and I further assume, it may not be a 3 character extension. 

LEN(@[User::FlatFileConnection]) - FINDSTRING(REVERSE(@[User::FlatFileConnection]), ".", 1)

FormatDate

This uses the package's start time as a reference point for determining year, month and date. You could use GETDATE but if your package starts at 11:59 PM and takes a minute to run, knowing that every time you use GetDate() it's re-evaulated... I'd rather not find out what happens. This expression generates a text string representing a date in the format of YYYY-MM-DD

(DT_WSTR, 4)  YEAR(@[System::StartTime]) 
+ "_" 
+ RIGHT("0" + (DT_WSTR, 2)  MONTH(@[System::StartTime]), 2) 
+ "_" 
+ RIGHT("0" + (DT_WSTR, 2) DAY(@[System::StartTime]), 2)

I present it with the line breaks in the expression so you can easily see it and future readers, you can modify it.

ArchiveName

Now it's time to put everything together.

The first line of the expression will get everything up to the first . in our connection string.

The second line is obvious, we concatenate an underscore

The third line adds in our date string

The fourth line grabs the remaining characters from our period to the end of the string.

SUBSTRING(@[User::FlatFileConnection], 1, @[User::FileExtensionPosition]) 
+ "_" 
+ @[User::FormatDate] 
+ RIGHT(@[User::FlatFileConnection], LEN(@[User::FlatFileConnection]) - @[User::FileExtensionPosition])

Biml

Biml, is the business intelligence markup language, which allows me to describe any business intelligence object in terms of some XML. Yes, I'm using XML to define more XML.

By virtue of grabbing the free BIDS Helper you can play along. Install it for your visual studio instance, right click on your solution to add a new biml file, paste the following, right click on the saved biml file and voila, you have a working sample.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="dba_81064" ConstraintMode="Linear">
            <Variables>
                <Variable Name="FlatFileConnection" DataType="String">C:\ssisdata\so\input\log.txt</Variable>
                <Variable Name="FormatDate" DataType="String" EvaluateAsExpression="true">(DT_WSTR, 4)  YEAR(@[System::StartTime]) 
    + "-" 
    + RIGHT("0" + (DT_WSTR, 2)  MONTH(@[System::StartTime]), 2) 
    + "-" 
    + RIGHT("0" + (DT_WSTR, 2) DAY(@[System::StartTime]), 2)</Variable>
                <Variable Name="FileExtensionPosition" DataType="Int32" EvaluateAsExpression="true">LEN(@[User::FlatFileConnection]) - FINDSTRING(REVERSE(@[User::FlatFileConnection]), ".", 1)</Variable>
                <Variable Name="ArchiveName" DataType="String" EvaluateAsExpression="true">SUBSTRING(@[User::FlatFileConnection], 1, @[User::FileExtensionPosition]) + "_" + @[User::FormatDate] + RIGHT(@[User::FlatFileConnection], LEN(@[User::FlatFileConnection]) - @[User::FileExtensionPosition])</Variable>
            </Variables>
        </Package>
    </Packages>
</Biml>

Emitted package should have a Variables window like

enter image description here

Debugging your script

If I put message box calls in before and after the assignment of sFlatFileConnection, with a starting value of "C:\ssisdata\so\input\log.txt", your code will assign a value of "C:\ssisdata\so\log.txt2014_10_28 My assumption is you want that date string between the log and the .txt.

If I'm using .NET, I'm not going to do the junior league string work I do above with Expressions. Instead, I'm going to make use of the .NET libraries as they've done all the heavy lifting for me. Specifically, I'm going to leverage System.IO.Path.

  • ChangeExtension allows me to change the extension
  • GetExtension grabs the current one

Thus, I change the extension to the current extension which I prepend _YYYY_MM_DD to.

System.IO.Path.ChangeExtension(sFlatFileConnection, string.Format("_{0}{1}", dtRunDate.ToString("yyyy_MM_dd"), System.IO.Path.GetExtension(sFlatFileConnection));
Related Question