2

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();

     }
 }
3
  • Can you use PowerShell with an Execute Process Task? It is much easier to append to a file?
    – user507
    Jan 27 '15 at 14:43
  • The reason this script won't work in the Data Flow itself is that the file isn't done being written to. Even if the rows have arrived at the destination, the lock may not be released on the file and so your script would generate access violations. You might be able to get away with it, in the Data Flow task if you push your code into an override for the OnPostExecute event but that still seems like it'd have the chance for concurrent activity.
    – billinkc
    Jan 27 '15 at 17:57
  • You'll also want to take note that while this approach works, it will be memory bound. For large files/servers with small memory sets, you'll want to read a row and then write it. If you search for string splitting by adam machanic, look at how he handles it.
    – billinkc
    Jan 27 '15 at 18:00
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.