SSIS - 在加载到目标之前将 CSV 文件与控制文件匹配 [英] SSIS - Matching CSV file to control file before loading to destination

问题描述

我每天有一个 CSV 文件要加载到目标表.但是，在加载发生之前，必须执行检查.控制 csv 文件有一个日期列和一个记录计数列.检查基本上是让记录计数列与每日 CSV 文件上的列数和控制文件上的日期列匹配以匹配当前日期.

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.

匹配成功后，每日 CSV 文件将加载到目标表中.我被困在流程应该是什么样子上.到目前为止，我已经对 DailyCSV 文件进行了行计数，并使用以下表达式对控制文件进行了条件拆分.

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] 

但是，输出只有控制文件的 2 列，我需要继续该过程才能将 CSV 文件加载到目标表中.

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.

推荐答案

一种方法是使用脚本任务来处理控制文件验证.脚本任务从控制文件中读取值.然后将这些值与当前日期和每日源文件中的行数进行比较.下面是使用这种方法的控制流的屏幕截图.如果一切都检查出来它流向数据流任务，否则流向发送邮件任务.

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.

以下是我在脚本任务中用于完成所需验证的代码.它是用c#编写的.此代码考虑了控制文件和源文件中的头记录.我想感谢博客文章 此处 用于 ReadFile 函数.

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

这篇关于SSIS - 在加载到目标之前将 CSV 文件与控制文件匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！