SSIS Script Task Get File Names and Store to an SSIS Object Variable

Question

I am trying to build an SSIS package that will be used in a standardized file system archive process. Basically, I will be able to add information to a configuration table and then use this table to archive certain files in specified folders. My issue is that a lot of the files have dynamic naming so I need to get a list of all files and then query to decide which files I should be touching.

Not being a C#/VB programmer is causing me some problems when trying to script a portion of the package the grabs all files in a specified network directory and then feed these file names back into an SSIS object variable.

I have a string variable 'User::SourceNetworkFolderName' which will contain the UNC location of the folder I want to read all files from. I want to then pass all these files names (with extension) back to an SSIS object variable called 'User::SourceFilesInTheDirectory'. Once I have the list of file names into the object variable, I was going to foreach loop them into an SQL table.

Does anyone have any specific suggestions on how I go about getting a list of all files names from my variable directory to my SSIS object variable?

Thank you in advance!

EDIT: Here is my updated code:

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

namespace ST_f5e4ae71f14d40d8811af21fa2a9a622.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()
        {
        //Setup Connection String to SQL
            SqlConnection SQLConnection = new SqlConnection(
                                       //"user id=username;" +                  //UserName
                                       //"password=password;" +                 //Password
                                       "Trusted_Connection=true;" +             //Windows Auth
                                       "server=SERVERNAME;" +                   //SQL Server
                                       "database=DATABASENAME; " +              //SQL Database
                                       "connection timeout=30;" +               //connection timeout
                                       "Network Library=dbmssocn");             //TCP/IP Connection ("dbnmpntw" = Name Pipes)


        //Open the SQL Connection and remove the error code
            try
            {
                SQLConnection.Open();
            }
            catch (Exception OpenConnectionError)
            {
                Console.WriteLine(OpenConnectionError.ToString());
            }


        //Fetch a list of files from 'SourceNetworkFolderName' SSIS variable to an array called array1.
            string[] ArrayFileName = Directory.GetFiles(Dts.Variables["SourceNetworkFolderName"].Value.ToString());


        //Set up sql variable for table population
            SqlParameter SQLFileNameParam = new SqlParameter("@FileName", SqlDbType.VarChar, 100);


        //Loop through the array and insert into an SQL table
            foreach (string strFileName in ArrayFileName)
            {
            //Update sql variable with file names from array
                SQLFileNameParam.Value = strFileName;
            //Make the table insert
                SqlCommand SQLInsertToTable = new SqlCommand("INSERT INTO Archive_Extract_Network_Folder_File_List (FileName) VALUES (@FileName)", SQLConnection);
            //This snippit allows the use of the variable in the sql script.
                SQLInsertToTable.Parameters.Add(SQLFileNameParam);
            //Execute SqlCommand
                SQLInsertToTable.ExecuteNonQuery();
            //Clear the parameters and set the object to null    
                SQLInsertToTable.Parameters.Clear();
                SQLInsertToTable = null;
            }


        //Close the SQL Connection and remove the error code
            try
            {
                SQLConnection.Close();
            }
            catch (Exception CloseConnectionError)
            {
                Console.WriteLine(CloseConnectionError.ToString());
            }


        //Set array to null since it is no longer required.
            ArrayFileName = null;


        //Exit on success
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

Answer 1

Inside your script, just build an array of the file names and set that array to your variable (make sure the variable is set to writable in the scripting task). If the variable is of type object you can then iterate on it using a for loop in a subsequent task and do whatever you want to with the files. You should not need to work on any miracles solely in one script.

Put all files under your source dir in an Array:

string[] array1 = Directory.GetFiles(Dts.Variables("SourceNetworkFolderName").Value.ToString());

Put all files with an extension of "BIN" in an array:

string[] array2 = Directory.GetFiles(Dts.Variables("SourceNetworkFolderName").Value.ToString(), "*.BIN");

You may need to include System.IO at the top of your scripting code.

EDIT:

To convert array to a List for processing by the Loop task. After calling the code above, call this:

List<string> fileList = new List<string>(astrTest);
Dts.Variables["SourceFilesInTheDirectory"].Value = fileList;

You will need to include System.Collections.Generic at the top of your script file.