SSIS - mapping CSV file to manage file before uploading to destination
I have a daily CSV file to upload to the destination table. However, a validation must be performed before downloading. The control csv file has a date column as well as a record count column. The check is to ensure that the record count column matches the number of columns in the daily CSV file and the date column in the control file matches the current date.
After a successful match, the daily CSV file will be loaded into the destination table. I am stuck on how the stream should look like. So far I have been doing the line count for the DailyCSV file and the conditional split for the control file with the expression below.
([Current Date] == (DT_WSTR,12)GETDATE()) && ([Record Count] == (DT_WSTR,4)@[User::DailyCSVRowCount]
However, there are only 2 control file columns in the output and I need to continue the process for the CSV file to be loaded into the destination table.
source to share
One approach to this is using a script task to handle the control file check. The script task is read in values from the control file. Then it compares the values with the current date and the number of lines present in the daily source file. Below is a screen shot of the control flow using this approach. If everything verifies that it flows into a data flow task, otherwise flows into a send mail task.
Below is the code I used in the script task to do the required validation. It is written in C #. This code respects the header entry in both 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 "";
}
}
source to share