TJB TJB - 3 months ago 21
C# Question

Move file depending on row count of csv file

I have a SSIS package which has a single .csv file as a flat file source. A conditional split inserts the data in various SQL Server staging tables based on an ID. Any rows that do not successfully insert are then redirected to an error .csv file, one per ID.

With these numerous error files, I want to perform an evaluation on the number of rows there are (apart from the header row). If there is at least one data error row, these will be sent via a send email task to the source developers, otherwise they will be archived via File System task.

I'm struggling on how to approach this. I have some idea that a Foreach loop container in the error file directory could be used to loop through and perform a row count via script task. However, I'm not sure how I can pass the individual file names to the script task. Furthermore, I'm not sure afterwards I can do the conditional email/file system task move.

In terms of script tasking, I've got something that looks like this (it errors) for the script task, failure being it's not more than 1 and gets archived, pass it gets emailed, just for some idea. I'm fairly new with complex C# scripting.

public void Main()
{
string folderPath = Dts.Variables["User::errorDirectory"].Value.ToString();
string archivePath = @"\\SERVER\placeholderfilepath\blah\blah";
var FileNames = new List<string>();
var FilePaths = new List<string>();

DirectoryInfo directoryInfo = new DirectoryInfo(folderPath);
FileInfo[] files = directoryInfo.GetFiles();

var lineCount = 0;


foreach (FileInfo fileInfo in files)
{

lineCount = File.ReadAllLines(fileInfo.FullName).Count();

//MessageBox.Show(lineCount.ToString());

if (lineCount == 1)
{
File.Move(folderPath, archivePath);
}

}




Dts.TaskResult = (int)ScriptResults.Success;

}


After a few hours of searching I can't find anything that covers this, so would be grateful for any pointers / articles.

Thanks in advance.

Answer

You might try adding Row Count component to the data flow that writes the data to .CSV. Row Count will put the number of rows to some variable and then you'll know if there were any rows in .CSV

I can see the problem in your code

string folderPath = Dts.Variables["User::errorDirectory"].Value.ToString();
        string archivePath = @"\\SERVER\placeholderfilepath\blah\blah";
        var FileNames = new List<string>();
        var FilePaths = new List<string>();

        DirectoryInfo directoryInfo = new DirectoryInfo(folderPath);
        FileInfo[] files = directoryInfo.GetFiles();

        var lineCount = 0;


        foreach (FileInfo fileInfo in files)
        {

            lineCount = File.ReadAllLines(fileInfo.FullName).Count();

            //MessageBox.Show(lineCount.ToString());

            if (lineCount == 1) 
            {
                File.Move(fileInfo.FullName, Path.Combine(archivePath, fileInfo.Name));
            }

        }

        Dts.TaskResult = (int)ScriptResults.Success;