Get file properties with SSIS

Case
I recently did a post on how to get filedates in SSIS, but I got questions about how to get other file properties like size, owner and a check whether the file is locked by an other process.

Solution
You could use the open source File Properties Task, but you can also do it yourself with a Script Task. Let's elaborate that Script Task solution.

1) Create variables
I created a whole bunch of variables, one for each file property. Notice the various datatypes. Fill the variable FilePath with a value.

Right click in your Control Flow to activate Variable window

2) Script Task
Add a Script Task to your Control Flow. I didn't add the variables to the readonly and readwrite lists of the Script Task, but locked them in the script. See this article for the difference between those methods.

Script Task

2a) Optional
This step is optional. It depends on whether you choose script 3a or 3b. This step is required for script 3a.
Select all the new variables as ReadWriteVariables. Only the FilePath can be a ReadOnlyVariable.

ReadOnly- and ReadWriteVariables

3a) The script (if you used 2a)
Copy the following code (usings and method) to your Script Task and remove the properties you don't need.

// C# code
// Fill SSIS variables with file properties
using System;
using System.Data;
using System.IO;                        // Added to get file properties
using System.Security.Principal;        // Added to get file owner
using System.Security.AccessControl;    // Added to get file owner
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_9ef66c631df646e08e4184e34887da16.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        public void Main()
        {
            // Variable for file information
            FileInfo fileInfo;

            // Fill fileInfo variable with file information
            fileInfo = new FileInfo(Dts.Variables["User::FilePath"].Value.ToString());

            // Check if file exists
            Dts.Variables["User::FileExists"].Value = fileInfo.Exists;

            // Get the rest of the file properties if the file exists
            if (fileInfo.Exists)
            {
                // Get file creation date
                Dts.Variables["User::FileCreationDate"].Value = fileInfo.CreationTime;
                
                // Get last modified date
                Dts.Variables["User::FileLastModifiedDate"].Value = fileInfo.LastWriteTime;

                // Get last accessed date
                Dts.Variables["User::FileLastAccessedDate"].Value = fileInfo.LastAccessTime;

                // Get size of the file in bytes
                Dts.Variables["User::FileSize"].Value = fileInfo.Length;

                // Get file attributes
                Dts.Variables["User::FileAttributes"].Value = fileInfo.Attributes.ToString();
                Dts.Variables["User::FileIsReadOnly"].Value = fileInfo.IsReadOnly;
                
                //////////////////////////////////////////////////////
                // Check if the file isn't locked by an other process
                try
                {
                    // Try to open the file. If it succeeds, set variable to false and close stream
                    FileStream fs = new FileStream(Dts.Variables["User::FilePath"].Value.ToString(), FileMode.Open);
                    Dts.Variables["User::FileInUse"].Value = false;
                    fs.Close();
                }
                catch (Exception ex)
                {
                    // If opening fails, it's probably locked by an other process
                    Dts.Variables["User::FileInUse"].Value = true;

                    // Log actual error to SSIS to be sure 
                    Dts.Events.FireWarning(0, "Get File Properties", ex.Message, string.Empty, 0);
                }

                //////////////////////////////////////////////////////
                // Get the Windows domain user name of the file owner
                FileSecurity fileSecurity = fileInfo.GetAccessControl();
                IdentityReference identityReference = fileSecurity.GetOwner(typeof(NTAccount));
                Dts.Variables["User::FileOwner"].Value = identityReference.Value;
            }

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

or the VB.Net code

' VB.net Code
' Fill SSIS variables with file properties 
Imports System
Imports System.Data
Imports System.IO                        ' Added to get file properties
Imports System.Security.Principal        ' Added to get file owner
Imports System.Security.AccessControl    ' Added to get file owner 
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
 Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

 Enum ScriptResults
  Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
  Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
 End Enum

 Public Sub Main()
        ' Variable for file information 
        Dim fileInfo As FileInfo

        ' Fill fileInfo variable with file information 
        fileInfo = New FileInfo(Dts.Variables("User::FilePath").Value.ToString())

        ' Check if file exists 
        Dts.Variables("User::FileExists").Value = fileInfo.Exists

        ' Get the rest of the file properties if the file exists 
        If (fileInfo.Exists) Then
            ' Get file creation date 
            Dts.Variables("User::FileCreationDate").Value = fileInfo.CreationTime
            ' Get last modified date 
            Dts.Variables("User::FileLastModifiedDate").Value = fileInfo.LastWriteTime
            ' Get last accessed date 
            Dts.Variables("User::FileLastAccessedDate").Value = fileInfo.LastAccessTime
            ' Get size of the file in bytes 
            Dts.Variables("User::FileSize").Value = fileInfo.Length
            ' Get file attributes 
            Dts.Variables("User::FileAttributes").Value = fileInfo.Attributes.ToString()
            Dts.Variables("User::FileIsReadOnly").Value = fileInfo.IsReadOnly
            ''''''''''''''''''''''''''''''''''''''''''''''''''' 
            ' Check if the file isn't locked by an other process 
            Try
                ' Try to open the file. If it succeeds, set variable to false and close stream 
                Dim fs As FileStream = New FileStream(Dts.Variables("User::FilePath").Value.ToString(), FileMode.Open)
                Dts.Variables("User::FileInUse").Value = False
                fs.Close()
            Catch ex As Exception
                ' If opening fails, it's probably locked by an other process 
                Dts.Variables("User::FileInUse").Value = True

                ' Log actual error to SSIS to be sure 
                Dts.Events.FireWarning(0, "Get File Properties", ex.Message, String.Empty, 0)
            End Try

            ''''''''''''''''''''''''''''''''''''''''''''''''''' 
            ' Get the Windows domain user name of the file owner 
            Dim fileSecurity As FileSecurity = fileInfo.GetAccessControl()
            Dim identityReference As IdentityReference = fileSecurity.GetOwner(GetType(NTAccount))
            Dts.Variables("User::FileOwner").Value = identityReference.Value
        End If

        Dts.TaskResult = ScriptResults.Success
 End Sub

End Class

3b) The script (without 2a)
Copy the following code (usings and method) to your Script Task and remove the properties you don't need.

// C# code
// Fill SSIS variables with file properties
using System;
using System.Data;
using System.IO;                        // Added to get file properties
using System.Security.Principal;        // Added to get file owner
using System.Security.AccessControl;    // Added to get file owner
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_cb8dd466d98149fcb2e3852ead6b6a09.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        public void Main()
        {
            // Lock SSIS variables
            Dts.VariableDispenser.LockForRead("User::FilePath");

            Dts.VariableDispenser.LockForWrite("User::FileAttributes");
            Dts.VariableDispenser.LockForWrite("User::FileCreationDate");
            Dts.VariableDispenser.LockForWrite("User::FileExists");
            Dts.VariableDispenser.LockForWrite("User::FileInUse");
            Dts.VariableDispenser.LockForWrite("User::FileIsReadOnly");
            Dts.VariableDispenser.LockForWrite("User::FileLastAccessedDate");
            Dts.VariableDispenser.LockForWrite("User::FileLastModifiedDate");
            Dts.VariableDispenser.LockForWrite("User::FileOwner");
            Dts.VariableDispenser.LockForWrite("User::FileSize");

            // Create a variables 'container' to store variables
            Variables vars = null;

            // Add variables from the VariableDispenser to the variables 'container'
            Dts.VariableDispenser.GetVariables(ref vars);

            // Variable for file information
            FileInfo fileInfo;

            // Fill fileInfo variable with file information
            fileInfo = new FileInfo(vars["User::FilePath"].Value.ToString());

            // Check if file exists
            vars["User::FileExists"].Value = fileInfo.Exists;

            // Get the rest of the file properties if the file exists
            if (fileInfo.Exists)
            {
                // Get file creation date
                vars["User::FileCreationDate"].Value = fileInfo.CreationTime;
                
                // Get last modified date
                vars["User::FileLastModifiedDate"].Value = fileInfo.LastWriteTime;

                // Get last accessed date
                vars["User::FileLastAccessedDate"].Value = fileInfo.LastAccessTime;

                // Get size of the file in bytes
                vars["User::FileSize"].Value = fileInfo.Length;

                // Get file attributes
                vars["User::FileAttributes"].Value = fileInfo.Attributes.ToString();
                vars["User::FileIsReadOnly"].Value = fileInfo.IsReadOnly;
                
                //////////////////////////////////////////////////////
                // Check if the file isn't locked by an other process
                try
                {
                    // Try to open the file. If it succeeds, set variable to false and close stream
                    FileStream fs = new FileStream(vars["User::FilePath"].Value.ToString(), FileMode.Open);
                    vars["User::FileInUse"].Value = false;
                    fs.Close();
                }
                catch (Exception ex)
                {
                    // If opening fails, it's probably locked by an other process
                    vars["User::FileInUse"].Value = true;

                    // Log actual error to SSIS to be sure 
                    Dts.Events.FireWarning(0, "Get File Properties", ex.Message, string.Empty, 0);
                }

                //////////////////////////////////////////////////////
                // Get the Windows domain user name of the file owner
                FileSecurity fileSecurity = fileInfo.GetAccessControl();
                IdentityReference identityReference = fileSecurity.GetOwner(typeof(NTAccount));
                vars["User::FileOwner"].Value = identityReference.Value;
            }

            // Release the locks
            vars.Unlock();
            
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

or the VB.Net code

' VB.net Code
' Fill SSIS variables with file properties 
Imports System
Imports System.Data
Imports System.IO                        ' Added to get file properties
Imports System.Security.Principal        ' Added to get file owner
Imports System.Security.AccessControl    ' Added to get file owner 
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
 Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

 Enum ScriptResults
  Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
  Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
 End Enum

    Public Sub Main()
        ' Lock SSIS variables 
        Dts.VariableDispenser.LockForRead("User::FilePath")

        Dts.VariableDispenser.LockForWrite("User::FileAttributes")
        Dts.VariableDispenser.LockForWrite("User::FileCreationDate")
        Dts.VariableDispenser.LockForWrite("User::FileExists")
        Dts.VariableDispenser.LockForWrite("User::FileInUse")
        Dts.VariableDispenser.LockForWrite("User::FileIsReadOnly")
        Dts.VariableDispenser.LockForWrite("User::FileLastAccessedDate")
        Dts.VariableDispenser.LockForWrite("User::FileLastModifiedDate")
        Dts.VariableDispenser.LockForWrite("User::FileOwner")
        Dts.VariableDispenser.LockForWrite("User::FileSize")

        ' Create a variables 'container' to store variables 
        Dim vars As Variables = Nothing

        ' Add variables from the VariableDispenser to the variables 'container' 
        Dts.VariableDispenser.GetVariables(vars)

        ' Variable for file information 
        Dim fileInfo As FileInfo

        ' Fill fileInfo variable with file information 
        fileInfo = New FileInfo(vars("User::FilePath").Value.ToString())

        ' Check if file exists 
        vars("User::FileExists").Value = fileInfo.Exists

        ' Get the rest of the file properties if the file exists 
        If (fileInfo.Exists) Then
            ' Get file creation date 
            vars("User::FileCreationDate").Value = fileInfo.CreationTime
            ' Get last modified date 
            vars("User::FileLastModifiedDate").Value = fileInfo.LastWriteTime
            ' Get last accessed date 
            vars("User::FileLastAccessedDate").Value = fileInfo.LastAccessTime
            ' Get size of the file in bytes 
            vars("User::FileSize").Value = fileInfo.Length
            ' Get file attributes 
            vars("User::FileAttributes").Value = fileInfo.Attributes.ToString()
            vars("User::FileIsReadOnly").Value = fileInfo.IsReadOnly
            ''''''''''''''''''''''''''''''''''''''''''''''''''' 
            ' Check if the file isn't locked by an other process 
            Try
                ' Try to open the file. If it succeeds, set variable to false and close stream 
                Dim fs As FileStream = New FileStream(vars("User::FilePath").Value.ToString(), FileMode.Open)
                vars("User::FileInUse").Value = False
                fs.Close()
            Catch ex As Exception
                ' If opening fails, it's probably locked by an other process 
                vars("User::FileInUse").Value = True

                ' Log actual error to SSIS to be sure 
                Dts.Events.FireWarning(0, "Get File Properties", ex.Message, String.Empty, 0)
            End Try

            ''''''''''''''''''''''''''''''''''''''''''''''''''' 
            ' Get the Windows domain user name of the file owner 
            Dim fileSecurity As FileSecurity = fileInfo.GetAccessControl()
            Dim identityReference As IdentityReference = fileSecurity.GetOwner(GetType(NTAccount))
            vars("User::FileOwner").Value = identityReference.Value
        End If

        ' Release the locks 
        vars.Unlock()

        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class

4) The result

For testing purposes I added an other Script Task with a messagebox that reads all the variables.

The result

5) Usage
You can check for example whether a file locked before you start the Data Flow. You do this with an expression on the Precedence Constraint.


Expression

Note: This script task example can be refined with error handling and logging.

Use filedates in SSIS

Case
A while ago someone asked me if it was possible to use the actual filedate of a file in SSIS.

Solution
Well, a Script task can retrieve the creation and modify date of a file. In this example I will import the latest file in a folder and delete all files older than 5 days. And as a bonus I will add an audit column with the filedate. A lot of steps (may be a bit too much for the experienced users, but not for the novice ones).

1) Create variables
Create three variables named FilePath (string), FileDate (datetime) and SourceFolder (string). Fill the SourceFolder variable with an existing path where the sourcefiles are stored.

Variables

2) Script task
Drag a Script task and a Data Flow task to you Control Flow and give them suitable names.

Script and Data Flow task

3) Map variables in Script task
Edit the Script task and select the FilePath and FileDate as ReadWrite variables and the SourceFolder as ReadOnly variable.

Map variables

4) The Script
Copy the code from the main method to your code and add the extra using.

// C# code 
// Script to get the last modified file
// Note: if there are two files with both
// the highest modified date, the script
// will pick a random one.
using System;
using System.Data;
using System.IO;    // Added
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_9f1bb3d696fe44b9b5e50ee8f5f648b5.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        public void Main()
        {
            // Get all files in the SourceFolder
            string[] sourceFiles = Directory.GetFiles(Dts.Variables["User::SourceFolder"].Value.ToString());

            // Store highest date en file
            DateTime highestDate = new DateTime();
            string lastFile = string.Empty;

            // Variable for file information
            FileInfo fileInfo;

            // Loop through sourceFiles
            foreach (string currentFile in sourceFiles)
            {
                // Fill fileInfo variable with file information
                fileInfo = new FileInfo(currentFile);

                // Choose between creation date and lastmodified date
                // Compare the file date to the variable
                if (fileInfo.LastWriteTime > highestDate)
                {
                    // Fill variables if the current file is the newest
                    lastFile = currentFile;
                    highestDate = fileInfo.CreationTime;
                }
            }

            // Return values to the SSIS variables
            Dts.Variables["User::FilePath"].Value = lastFile;
            Dts.Variables["User::FileDate"].Value = highestDate;

            // Finish script
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

5) Edit Data Flow
Create a Flat file source, Derived Column and a target of your choice. It should look something like this:

Data Flow

6) Source File
Add a connection manager to your Flat File Source. Select one of the files in your source folder for this connection manager. It doesn't matter which one, because the connection string will be overwritten by an expression. Add the colums to finish it. Now go to the properties of your newly created connection manager and add an expression.

Right Click if you don't see the properties

Add an expression

7) Expression
Add an expression for the ConnectionString Property so the ConnectionString will be overwritten by the value of the FilePath variable.

Create an expression

8) Derived Column
Edit the Derived Column and add a new column that is filled with the variable FileDate.

New column with FileDate

9) Target
Finish the dataflow by adding a target and test the first part of this mapping.

The test result


















Now the second part which deletes files older than 5 days. We could do this in one simple Script task, but I will try to limit the .Net code because this is an SSIS blog and not a .Net blog.


10) Delete old files
Add a Foreach Loop to the Control Flow with a Script Task and a File System Task in it. Your Control Flow should look something like this.

Continuing Control Flow

11) Configure Foreach Loop
Edit the Foreach Loop and select the Foreach File Enumerator. After that enter the path of you sourcefolder.

Foreach File

12) Expression
This step is not really necessary for this example, but it's just a bit nicer. Goto the Expressions on the Collection tab of the Foreach Loop and add an expression for the Directory which will overwrite the Folder property (don't let the different names confuse you) with the variable SourceFolder.

Add an expression for the Directory/Folder

13) Variable Mapping
Map the FilePath variable to Index 0 in the Foreach Loop.

Variable Mappings

14) Script task
Copy the code from the main method to your code and add the extra using. Don't fill in the variables this time (it's done by script).

// C# code 
// Fill SSIS variable with the last modified date
// of the current source file
using System;
using System.Data;
using System.IO;    // Added
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_cb8dd466d98149fcb2e3852ead6b6a09.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        public void Main()
        {
            // Lock SSIS variables
            Dts.VariableDispenser.LockForRead("User::FilePath");
            Dts.VariableDispenser.LockForWrite("User::FileDate");

            // Create a variables 'container' to store variables
            Variables vars = null;

            // Add variables from the VariableDispenser to the variables 'container'
            Dts.VariableDispenser.GetVariables(ref vars);

            // Variable for file information
            FileInfo fileInfo;

            // Fill fileInfo variable with file information
            fileInfo = new FileInfo(vars["User::FilePath"].Value.ToString());

            // Choose between creation date and lastmodified date
            // Fill SSIS variable with last modified date
            vars["User::FileDate"].Value = fileInfo.LastWriteTime;

            // Release the locks
            vars.Unlock();
            
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

15)  Precedence Constraint

Enter the following Contraint to the line between the Script Task and the File System Task:

DATEDIFF("dd", @[User::FileDate], @[System::StartTime] ) > 5

Date check in the Expression

16) File System Task

Delete the file which is stored in the variabe FilePath. You need to enter some dummy default value in the variable. Otherwise you will get an error message: Variable "FilePath" is used as an source or destination and is empty.

Delete the old file

17) The result

Now test the package with a couple of files.

Last file wasn't old.

That's that! Let me know if you have some other interesting SSIS ideas with file properties.

Update: a whole bunch of file properties.