CODEWITHSUNDEEP
c#ssisscript-task
Hannah McDade
Hannah McDade

Reputation: 87

SSIS Script Task for moving files based on their file extension

I have the following code contained within a Script Task in SSIS 2012. 

public void Main()
    {
        string inputDir = (string) Dts.Variables["User::InputDirectory"].Value;
        string CSVFolder = (string) Dts.Variables["User::CSVFolder"].Value;
        string XMLFolder = (string) Dts.Variables["User::XMLFolder"].Value;
        string XLSXFolder = (string) Dts.Variables["User::XLSXFolder"].Value;
        bool isXMLFolderEmpty = (bool) Dts.Variables["User::isXMLFolderEmpty"].Value;
        bool isCSVFolderEmpty = (bool)Dts.Variables["User::isCSVFolderEmpty"].Value;
        bool isXLSXFolderEmpty = (bool)Dts.Variables["User::isXLSXFolderEmpty"].Value;

        string[] fileNames = Directory.GetFiles(@inputDir);
        if (fileNames.Length > 0)
        {
            foreach (string inputFile in fileNames)
            {
                string FileExtension = Path.GetExtension(inputFile);

                if (FileExtension == ".csv")
                {
                    File.Move(inputDir + "\\" + inputFile, CSVFolder + "\\" + inputFile);
                    isCSVFolderEmpty = false;
                }
                else if (FileExtension == ".xlsx")
                {
                    File.Move(inputDir + "\\" + inputFile, XLSXFolder + "\\" + inputFile);
                    isXLSXFolderEmpty = false;
                }
                else if (FileExtension == ".xml")
                {
                    File.Move(inputDir + "\\" + inputFile, XMLFolder + "\\" + inputFile);
                    isXMLFolderEmpty = false;
                }
            }
        }

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

However when I execute the Script task I am getting the following error: DTS Script Task has encountered an exception in User code: Exception has been thrown by the target of invocation.

Can anybody point out what is going wrong? All of variable names are correct.

Upvotes: 1

Views: 5671

Answers (1)

billinkc
billinkc

Reputation: 61221

File.Move is incorrect

You might also be interested in the Path.Combine as it will more gracefully handle building paths than your blind string concatenation route.

Directory.GetFile indicates

Returns the names of files (including their paths) in the specified directory

so inputFile is already going to be in the form of C:\ssisdata\so_35605920\Foo.csv so your initial parameter to FileMove is simply inputFile

So really the challenge becomes changing the folder path out of the inputFile variable to the targeted one (CSV, XML, or XLSX). Lazy hack would be to call the string.replace method specifying the inputDir and using the new directory. I'd probably go with something a bit more elegant in case there's weirdness with UNC or relative paths.

Path.GetFileName will give me the file and extension. So using that + Path.Combine would yield the correct final path for our Move operation

Path.Combine(CSVFolder, Path.GetFileName(inputFile));

Thus

File.Move(inputFile, Path.Combine(CSVFolder, Path.GetFileName(inputFile)));

Visual studio is rather unhappy at the moment so pardon the lack of precision in the suggested code but if there are typos, you have the references to books online and the logic behind it.

Also, try/catch blocks are exceptionally helpful in defensive coding as will be testing to ensure the folders exist, there's no process with a lock on the file, etc.

Upvotes: 1

Related Questions