Sql-server – SSIS append trailer with Script

csql serverssis

I am trying to add a footer to a flat file output. I have 2 data flows in my control flow. One creates the file from the DB. It goes from OLE DB Source to Flat File Destination. The second control flow uses a second connection manager pointed at the same file path as the first data flow and uses. In the second data flow i am using a script component pointed at the flat file to append the trailer.

The issue is the script is not generating any output. I am a C# novice and have modified code found on the net and would appreciate any help.
Here is the scipt code in C#2012:

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent

 {

     public void Main()
     {
         const string dirPath = @"Q:\General\Operations\Prod_Support\XOOM OPS Procedures\Wells_Testing\";

         var fileBody = AddHeaderAndFooter.GetFileText(dirPath + "WellsPOS.txt");

         var trailerRecord = "9" + DateTime.Today.ToString("ddMMyyyy") + AddHeaderAndFooter.CountRecords(dirPath + "WellsPOS.txt").ToString();

         var outPutData = fileBody + trailerRecord + "\r\n";

         AddHeaderAndFooter.WriteToFile(dirPath + "WellsPOS.txt", outPutData);

     }
 }

 public static class AddHeaderAndFooter
 {
     public static int CountRecords(string filePath)
     {

         return (System.IO.File.ReadAllLines(filePath).Length + 2);  

     }

     public static string GetFileText(string filePath)
     {
         var sr = new System.IO.StreamReader(filePath, System.Text.Encoding.Default);

         var recs = sr.ReadToEnd();

         sr.Close();

         return recs;
     }

     public static void WriteToFile(string filePath, string fileText)
     {

         var sw = new System.IO.StreamWriter(filePath, false);

         sw.Write(fileText, System.Text.Encoding.ASCII);

         sw.Close();

     }
 }

Best Answer

I had to use a Script Task at the Control Flow level instead of a Script Component at the Data Flow level. When I switched that around it worked like a charm. I also changed the sw.Write(fileText, System.Text.Encoding.ASCII); line to sw.WriteLine(fileText, System.Text.Encoding.ASCII); so it adds the text to the next line.

Related Question