Sql-server – Best Practice for SQL Server data validation

I would replace your output Recordset Destinations with OLE DB Destinations and deliver the required data into database tables. As well as making the remaining work easier, this could also give you a far more useful Audit/History of previous runs.

From that point I would then write SSRS reports against those tables and use SSRS subscriptions to prepare well formatted emails and deliver them.

You can use a script transformation to create the actual column mapping. For your flat file source, make every output line become a single large column.

In your script transformation, create column definitions for each column you need mapped / already know about.

This assumes your first line contains headers:

Then, in your script transformation , split each row by the delimiter you need. You can create a boolean value indicating if the first line has been read yet. If not, parse the line and store the column mapping positions in a Dictionary<string,int> such as ColumnMappings and do not write to OutputBuffer. If your row reader sees that the column mappings have been read, then you can set your output to the correct column with something like:

 OutputBuffer.MyMappedColumn = SplitRow[ColumnMappings["MyMappedColumn"]];

An example of an entire transformation with a column "Day" is below

using System;
using System.Data;
using System.Text;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent {
    UTF8Encoding enc = new UTF8Encoding();
    char[] separator = { ',' };
    bool _isColumnMappingComplete = false;
    System.Collections.Generic.Dictionary<string, int> _columnMapping;
    bool isWritable = false;

    bool isColumnMappingComplete { //Set true after first row processed
        get{ return _isColumnMappingComplete; }
        set{ _isColumnMappingComplete = value;}   }

    System.Collections.Generic.Dictionary<string, int> columnMapping{ //Object to hold column mapping indexes
        get {
            if (_columnMapping == null) { _columnMapping = new System.Collections.Generic.Dictionary<string, int>(); }
            return _columnMapping; }
        set {
            _columnMapping = value;
            isColumnMappingComplete = true; } }

    public override void PreExecute() {
        base.PreExecute(); /* Add your code here for preprocessing or remove if not needed */ }

    public override void PostExecute()    {
        isWritable = true;
        base.PostExecute();
        /* Add your code here for postprocessing or remove if not needed
          You can set read/write variables here, for example: Variables.MyIntVar = 100 */  }

    public override void Input0_ProcessInputRow(Input0Buffer Row)    {
        bool isEmptyRow = false;
        //byte[] rawRowBytes = Row.Column0.GetBlobData(0,(int)Row.Column0.Length); //uncomment if using Text input datatype
        //string rawRow = enc.GetString(rawRowBytes); //uncomment if using Text input datatype
        string rawRow = Row.Column0; //comment out if using Text input datatype
        if (rawRow.Replace("\"", "").Replace(",", "").Replace(" ", "").Length == 0)
        { isEmptyRow = true; }
        if (!isEmptyRow) {
            string[] rowData = rawRow.Split(separator);

            if (!isColumnMappingComplete) {
                System.Collections.Generic.Dictionary<string, int> firstColMap = new System.Collections.Generic.Dictionary<string, int>();
                for (int i = 0; i < rowData.Length; i++)
                { firstColMap.Add(rowData[i], i); }
                columnMapping = firstColMap;    }
            else {
                Output0Buffer.AddRow();
                bool _rowError = false;
                //Day
                if (columnMapping.ContainsKey("Day"))
                { Output0Buffer.Day = rowData[columnMapping["Day"]]; }
                else
                { //Output0Buffer.Day = "-";
                    _rowError = true; }   }      }    }

    public override void CreateNewOutputRows()
    {   /* Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
         * For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput". */ }     }