SSIS File Validation

Question

I am new to SSIS. I have more than one file(flat files) in the source location. When going through each file SSIS package needs to determine whether the files are .txt or .csv formats. If any of the files are not .txt or .csv format the files should be moved to an Error directory and send an email mentioning that the file is in an invalid format for each invalid file.The email message should contain the file name.

Could you please help me finding a solution for this?

Thanks in advance.

Before I answer are you familiar with C# and or are you permitted to use script tasks within your process. I ask because I know some places are very adamant about using only the built in tools when working within ssis. — SFrejofsky
Yes, I am familiar with C# and I am allowed to use it. Thanks! — Dhruba

SFrejofsky SFrejofsky · Accepted Answer · 2015-03-05T17:04:19

You could do something like this.

      //set string builder to create html for email. This will be 
      //set to a string variable in your ssis package
        System.Text.StringBuilder objMsg = new System.Text.StringBuilder();

        objMsg.Append("<html><head><style type='text/css'>table td {border: solid 1px black; padding-left: 10px; padding-right: 10px;}</style></head><body><div style='font-family: tahoma, geneva, verdana; font-size: 10pt;'>");
        objMsg.Append("<p>The following table contains files that do not match the required format.</p>");
        objMsg.Append("</div></body>");

      string[] files = Directory.GetFiles("directory");

        foreach (string file in files)
        {
            // if extension is invalid then append table for html email payload
            if (Path.GetExtension(file).ToLower() == ".txt" || Path.GetExtension(file).ToLower() == ".csv") 
            {
                objMsg.Append("<p style='margin-left: 30px;'>Details:</p><table style='margin-left: 30px; font-family: monospace; font-size: 9pt; text-align: right; width: 5in;'><tr style='background-color: silver; color: white; font-weight: bold;'><td style='width: 100%'>File Name</td></tr>");

                    objMsg.Append("<tr>");
                    objMsg.Append("<td>" + Path.GetFileName(file) + "</td>");
                    objMsg.Append("</tr>");
             File.Move(file, "errordirectory" + Path.GetFileName(file));
            }
        }
Dts.Variables["your user variable here"].value = objMsg.ToString();

My apologies for the rough formatting I was trying to do this quickly while at work.

What this will do is loop thru the file in your source directory and add an entry to a string builder object that will ultimately build a table in html with a leading message that reads "The following table contains files that do not match the required format."

Within the If statement it will also move the files identified to a new directory.

once you have run this step then you can assign the string variable you added the html to as the body of your email step.

SSIS File Validation

1 Answers