0
votes

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.

1
Your control file has only one row in it right? - Chris Albert
thats correct. It has one row with 2 columns. 'Current_Date' and 'Record_Count' - A.S.
what? so you are trying to load the csv as 1 row 2 columns then if it passes the 2 checks load the same csv as more columns and rows? Perhaps you can share your current package design such as dataflow etc. - Matt
Sorry about the confusion. A daily csv file has an 'x' amount of records, to ensure its validity it is compared to a control file which identifies the current date as well as the amount of records the daily csv file should hold. So if the count on the daily csv file does not match the number that shows on the control files' record count column, then we would identify it as an error and stop the package. Also if the date in the 'current_date' column doesnt match the system current date, then also it will be flagged as an error and stop the package - A.S.
I suggest you load both files into a staging table then use T-SQL to validate. If valid, move the data into the real table. Note that this way you get an audit log with the info from the control file plus any other information you can add such as when the file was imported, how big it was etc. Yes you could wire all this up in SSIS if you want to do it that way - Nick.McDermaid

1 Answers

0
votes

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.

enter image description here

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 "";
    }
}