SSIS - Matching CSV file to control file before lo

Question

I have a daily CSV file to load to the destination table. However, before the loading occurs, a check has to be performed. The control csv file has a date column as well as a record count column. The check is basically to have the record count column match the number of columns on the daily CSV file and the date column on the control file to match the current date.

After the match is successful, the daily CSV file will be loaded on to the destination table. I am stuck at how the flow should look like. So far I have done a row count for the DailyCSV file and a conditional split for the control file with the expression below.

([Current Date] == (DT_WSTR,12)GETDATE()) && ([Record Count] == (DT_WSTR,4)@[User::DailyCSVRowCount] 

However the output only has the 2 columns of the control file and i need to continue the process to have the CSV file loaded on to the destination table.

Answer 1

One approach to this is using a script task to handle the control file validation. The script task reads in the values from the control file. Then it compares the values to the current date and number of rows present in the daily source file. Below is a screenshot of a control flow that uses this approach. If everything checks out it flows to the data flow task, otherwise is flows to the send mail task.

Below is the code I used in the script task to accomplish the required validation. It is written in c#. This code takes into account a header record in both the control and source files. I would like to give credit to the blog post here for the ReadFile function.

public void Main()
{
    string errInfo = "";
    string controlFilePath = "Z:\\StackOverFlow\\Control.csv";
    string sourceFilePath = "Z:\\StackOverFlow\\Source.csv";
    string fileContent = ReadFile(controlFilePath, errInfo);
    string[] parsedContent = fileContent.Split(new string[] { "\n", "\r\n" }, StringSplitOptions.RemoveEmptyEntries);
    int controlRows = Int32.Parse(parsedContent[1].Split(',')[0]);
    string controlDate = parsedContent[1].Split(',')[1];
    int sourceRows = -1;

    using (var reader = File.OpenText(sourceFilePath))
    {
        while (reader.ReadLine() != null)
        {
            sourceRows++;
        }
    }

    if (DateTime.Parse(controlDate).Date.Equals(DateTime.Now.Date) && controlRows == sourceRows)
    {
        Dts.TaskResult = (int)ScriptResults.Success;
    }
    else
    {
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
}

public String ReadFile(String FilePath, String ErrInfo)
{
    String strContents;
    StreamReader sReader;
    try
    {
        sReader = File.OpenText(FilePath);
        strContents = sReader.ReadToEnd();
        sReader.Close();
        return strContents;
    }
    catch (Exception e)
    {
        MessageBox.Show(ErrInfo);
        ErrInfo = e.Message;
        return "";
    }
}