none
add header and footer ssis

    Question

  • hello I have a ssis package which exports data from a query into a flat file, which will be used to import into a data warehouse. One of the requirements is to add a header row with the current date, with some other bits and pieces, and a footer row with total row count. I would like to do this ideally in one script component or task using C# for tidiness in the package. I'm a noob when it comes to writing code so could do with some help! How can this be done? I've looked at some of the other examples but nothing quite matches what I want... I've pasted my current attempt below, but am getting invalid entry point for some reason even though the entry point is set to main?  Please help! Ideally i'd like to read in all my data from the flat file, then insert a header row at the top and footer at the bottom, i've been struggling with this for ages!

     

     

    /*
       Microsoft SQL Server Integration Services Script Task
       Write scripts using Microsoft Visual C# 2008.
       The ScriptMain is the entry point class of the script.
    */
    
    using System;
    using System.IO;
    using System.Data;
    using System.Text;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    
    namespace ST_de84dc4752634b8cb463af6bef2fa22c.csproj
    {
        [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
        public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
        {
    
            #region VSTA generated code
            enum ScriptResults
            {
                Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
                Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
            };
            #endregion
    
            /*
                The execution engine calls this method when the task executes.
                To access the object model, use the Dts property. Connections, variables, events,
                and logging features are available as members of the Dts property as shown in the following examples.
    
                To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
                To post a log entry, call Dts.Log("This is my log text", 999, null);
                To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
    
                To use the connections collection use something like the following:
                ConnectionManager cm = Dts.Connections.Add("OLEDB");
                cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
    
                Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
                
                To open Help, press F1.
          */
    
            public void Main(string[] args)
            {
                var paramArray = Environment.GetCommandLineArgs();
                //var dirPath = paramArray[1];
    
                const string dirPath = @"C:\SSIS\Dev";
    
                var headerRecord = ("0" + DateTime.Now.ToString() + Dts.Variables["LastSequenceNumber"].Value + Dts.Variables["FileName"].Value) ;
                var fileBody = AddHeaderAndFooter.GetFileText(dirPath + "blank.txt");
                var trailerRecord = AddHeaderAndFooter.CountRecords(dirPath + "blank.txt").ToString();
    
                var outPutData = headerRecord + "\r\n" + fileBody + "\r\n" + trailerRecord + "\r\n";
    
                AddHeaderAndFooter.WriteToFile(dirPath + "blank.txt", outPutData);
    
            }
    
        }
    
        public static class AddHeaderAndFooter
        {
            public static int CountRecords(string filePath)
            {
    
                return File.ReadAllLines(filePath).Length;
            }
    
            public static string GetFileText( string filePath)
            {
                var sr = new StreamReader(filePath, Encoding.Default);
                    return sr.ReadToEnd();
            }
    
            public static void WriteToFile( string filePath, string fileText)
            {
                var sw = new StreamWriter(filePath, false);
                sw.Write(fileText, Encoding.ASCII);
            }
       
        }
    }
    
    Monday, December 12, 2011 3:42 PM

Answers

All replies

  • Did you see this post: http://agilebi.com/jwelch/2008/02/08/adding-headers-and-footers-to-flat-files/?

    I find it is probably the best way to add a header and footer that way.


    Arthur My Blog
    Monday, December 12, 2011 3:46 PM
  • yea it's in VB though and I suck at VB, plus i'd rather just do it in one script component... i'm nearly there i just need some help with the code...
    • Edited by jhowe1 Monday, December 12, 2011 4:33 PM
    Monday, December 12, 2011 4:19 PM
  • Convert to C# using: http://www.developerfusion.com/tools/convert/vb-to-csharp/
    Arthur My Blog
    • Marked as answer by Eileen Zhao Monday, December 19, 2011 9:53 AM
    Monday, December 12, 2011 4:51 PM