Need to get file properties into table via SSIS

Question

I have an SSIS package that needs to get file size, last modified date, and file name with extension and dump that information into a sql server 2014 table.

I'm developing using SQL Server Data Tools for Visual Studio 2013. So far, I've created a script task which reads from a "source_directory" string variable and writes to an object variable. My plan is to create a table within the script, loop through the source directory and pull the desired file properties using fileinfo. Then search that table for only .txt files and sort by filename and place into a separate, sorted table. Then place those values into my object variable. Then loop through the object using a foreach loop container

However, I can't get this to work. Whenever I execute just the script task I get "Exception has been thrown by the target of an invocation."

Please see below C# code.

public void Main()
{
        // Create a dataset. I named it unsorted, because it's not yet sorted
        DataSet dsUnsorted = new DataSet();

        // Create a new table in the dataset
        DataTable filelistTable = dsUnsorted.Tables.Add();
        filelistTable.Columns.Add("Source_Full_Filepath", typeof(string));  // Filepath needed for connectionstring.
        filelistTable.Columns.Add("Source_Filename", typeof(string));  // Filename used for sorting [optional]. 
        filelistTable.Columns.Add("Source_Datetime", typeof(DateTime));// Filedate used for sorting [optional].
        filelistTable.Columns.Add("Source_Size", typeof(int));// Filesize.

        // Get all files within the folder       
        string[] allFiles = Directory.GetFiles(Dts.Variables["Source_Directory"].Value.ToString());

        // Variable for storing file properties
        FileInfo fileInfo;

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

            // Choose which the file properties you will use
            // Columns:            FilePath           FileName       FileDate
            filelistTable.Rows.Add(fileInfo.FullName, fileInfo.Name, fileInfo.LastWriteTime, fileInfo.Length);

        }

        // Filtering on *.txt extension. Note: like uses * instead of %
        // Sorting the files on filename (or filedate: FileName DESC)
        DataRow[] rows = dsUnsorted.Tables[0].Select("Source_Full_Filepath like '*.txt'", "Source_FileName ASC");



        // Create a new sorted dataset that the SSIS foreach loop uses.
        DataSet dsSorted = new DataSet();
        DataTable filelistTableSorted = dsSorted.Tables.Add();

        // Only interested in the filepath which is needed for the connectionstring 
        filelistTableSorted.Columns.Add("Source_Full_Filepath", typeof(string));  // Filepath needed for connectionstring.
        filelistTableSorted.Columns.Add("Source_Filename", typeof(string));
        filelistTableSorted.Columns.Add("Source_Datetime", typeof(DateTime));// Filedate used for sorting [optional].
        filelistTableSorted.Columns.Add("Source_Size", typeof(int));// Filedate used for sorting [optional].
        // Fill the new dataset with the sorted rows.

        foreach (DataRow row in rows)
        {
            filelistTableSorted.Rows.Add(row["Source_Full_Filepath"].ToString());
            filelistTableSorted.Rows.Add(row["Source_Filename"].ToString());
            filelistTableSorted.Rows.Add(row["Source_Datetime"].ToString());
            filelistTableSorted.Rows.Add(row["Source_Size"].ToString());
        }

        // Store the dataset in the SSIS variable       
        Dts.Variables["FileDataset"].Value = dsSorted;




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

Answer 1

A few thoughts here: Directory.GetFiles should be overloaded to allow you to only list files with a .txt extension. That should clear up some of your code. If your goal is to ultimately push this information into a table, you will need parts of this script to collect the FileInfo values. However, you're overcomplicating matters.

Add a Data Flow Task to your SSIS package. Within the Data Flow Task, add a Script Component. When it hits the canvas, it will ask whether this is a Source, Destination or Transformation. Choose Source

On the Inputs and Outputs tab, on the Output 0 output, click Add Column 4 times. Rename to and assign correct data types (I went with DT_STR 256, DT_STR 128, DB_DATETIME, DT_I8) for your problem domain

Back on the Script tab, now indicate that your SSIS Variable Source_Directory is a ReadOnly and click Edit Scripts.

using System;
using System.Data;
using System.IO;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    public override void CreateNewOutputRows()
    {
        string sourceDirectory = @"C:\ssisdata";
        string fileMask = "*.txt";

        // Variable for storing file properties
        FileInfo fileInfo;

        foreach (var currentFile in Directory.GetFiles(sourceDirectory, fileMask, SearchOption.AllDirectories))
        {
            fileInfo = new FileInfo(currentFile);
            Output0Buffer.AddRow();
            Output0Buffer.FullName = fileInfo.FullName;
            Output0Buffer.Name = fileInfo.Name;
            Output0Buffer.LastWriteTime = fileInfo.LastWriteTime;
            // fileInfo.Length is type Long
            // Output0Buffer.Length is type Int64
            // Too lazy to look, but I think Long could overflow Int64
            Output0Buffer.Length = fileInfo.Length;
        }
    }
}

From there, add an OLE DB Destination (unless you need to use a different provider) and send your data there. For purposes of this answer, I added a Derived Column and put a data viewer in there to show this works.