x

header and footer ssis

Hi, I've had a requirement from one of our partners to add a Header row which will add my current filename (which i have another process that adds a datetime stamp to the filename) and a footer row that includes a row count of sent rows. I'm not sure how to do this and after scouring the net can't find something that is very easy to follow. I'm fairly new to ssis so would appreciate some best practice advice and some step by step instructions? Thanks for your help! Note: I think I will need to do this after the file is created, so as a script task ideally c# in the control flow...
more ▼

asked Dec 08 '11 at 04:26 AM in Default

jhowe gravatar image

jhowe
1.1k 47 56 60

What do you mean with Header row and Footer row? Where do you want to place them? In a table? As far as tables are concerned, there's no such thing as headers and footers, there are just rows, and there are indexes that can be used to sort them. As far as SSIS is concerned, it's a tool to create workflows, so no headers and footers there either. So I just don't understand your question.
Dec 08 '11 at 04:37 AM Magnus Ahlkvist
sorry let me be more clear, my ssis process exports a flat file from a sql table. This file will be used by a partner in an automated process to import into a data warehouse.
Dec 08 '11 at 08:00 AM jhowe
ideally i'd just like to use a script component after the data is transferred to the file to insert a row above the data and insert a row at the bottom of the data...
Dec 08 '11 at 09:36 AM jhowe
a quick and dirty hack i've seen used is to UNION the header and footer rows with the result set being exported. Didn't want to make a real answer out of that, but it might be useful
Dec 08 '11 at 01:00 PM KenJ
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

This is the cleanest way I've found of doing this task and it's not in horrible VB! Basically just builds up header and trailer rows and then appends to the dataset. You will need a little bit of C# knowledge, but it is well worth it!

   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.Text;
using System.IO;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_db04adc927b941d19b3817996ff885c2.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()
        {
            const string dirPath = @"C:\SSIS\Dev\";

            DateTime minusoneweek = DateTime.Today.AddDays(-7);

            DateTime minusoneday = DateTime.Today.AddDays(-1);

            var headerRecord = ("0|" + DateTime.Today.ToString("ddMMyyyy") + "|" + Dts.Variables["LastSequenceNumber"].Value + "|" 
                + Dts.Variables["FileName"].Value) + "|" + minusoneweek.ToString("ddMMyyyy") + "|" + minusoneday.ToString("ddMMyyyy");

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

            var trailerRecord = "9|" + AddHeaderAndFooter.CountRecords(dirPath + "blank.txt").ToString();

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

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

        }
    }

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

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

        }

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

            var recs = sr.ReadToEnd();

            sr.Close();

            return recs;
        }

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

            var sw = new StreamWriter(filePath, false);

            sw.Write(fileText, Encoding.ASCII);

            sw.Close();

        }
    }
}
more ▼

answered Jan 18 '12 at 11:34 PM

jhowe gravatar image

jhowe
1.1k 47 56 60

(comments are locked)
10|1200 characters needed characters left

It is not necessary to use a Script Component as Destination for this purposes. With Script Component you will have to handle custom processing of the passed rows etc.

The easiest way it simply use a Flat File Destination and easily define the output structure. To include a custom header row, simply set the Header property of the Flat File Destination. It can be even set by an expression on the Data Flow level to contain whatever you want.

In this case the first row will contain your custom header and will be followed by all the data.

To append custom footer add a Script Task which will write a custom footer by a single line of code:
System.IO.File.AppendAllText(@"D:\\tmp\\test2.txt", "My Custom Footer");

Of Course you can construct whatever header, you want. If you want to append row counts, simply in the data flow include the Row Count transformation which will write the row count into a variable and then pass that variable to the Script Task to write the number as footer.
more ▼

answered Jan 19 '12 at 03:06 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.2k 8 11 21

+1 from here
Jan 19 '12 at 05:07 AM Usman Butt
+1, why bother with custom code when an easier process is provided...
Jan 19 '12 at 08:43 AM Shawn_Melton
i've not investigated this method, i'm not sure if this will satisfy my requirements as i'm doing row counts, inserting dates etc... can you confirm whether this is all possible via this method...
Jan 25 '12 at 02:22 AM jhowe

Yes, you can do all of this usin gthis method. As I have mentioned, you can set the header as an Expression. You can do whatever calculations in the header and in case Expressions are not enought any more complex calculations to construct the header can be done usin eg. Script task and store thi in a variable and then put this variable as an expression to the header (this is only case when th ebuildin expressions are not enought).

For the footer There is used script task, so you can write whatever you wrote.

Only limitation related to the header is that maximum length of the string returned by expression is 4000 characters.
Jan 25 '12 at 06:06 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

You can do this using a script component as a destination in the data flow.

Basically, you will declare the streamwriter file in the class script so it is accessible from all of the subs. Then in the PreExecute Sub you write the header, and in the PostExecuteSub you write the footer. Then the guts of the file are written by the ProcessInputRow? Here is a sample script that does it just fine.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _
<CLSCompliant(False)> _
Public Class ScriptMain 
Inherits UserComponent

Dim ioFile As System.IO.StreamWriter

Public Overrides Sub PreExecute()
    MyBase.PreExecute()
    '
    ' Add your code here for preprocessing or remove if not needed
    '
    ioFile = New System.IO.StreamWriter("D:/class/ssis.csv")
    ioFile.WriteLine("This is a header")
End Sub

Public Overrides Sub PostExecute()
    MyBase.PostExecute()
    '
    ' Add your code here for postprocessing or remove if not needed
    ' You can set read/write variables here, for example:
    ' Me.Variables.MyIntVar = 100
    '
    ioFile.WriteLine("this is a footer")
    ioFile.Close()
End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    '
    ' Add your code here
    '

    ioFile.WriteLine(Row.team.ToString)
    ' MessageBox.Show("TEST")
End Sub
End Class
more ▼

answered Dec 08 '11 at 03:59 PM

Daniel Ross gravatar image

Daniel Ross
2.9k 10 12 13

this is exactly the sort of thing i'm looking for, can I use the script task? I'm not sure what the difference between script component and script task is... i also have the cozyroc ssis components so can use the script task plus or script component plus? I don't suppose I could get this in c#? ;) Would I ideally want to use this in the control flow after the data flow task that extracts the data?
Dec 09 '11 at 06:07 AM jhowe
You should use this in your script component.A script component is used in data flow task while transforming the rows where as script task is an individual task in SSIS
Dec 09 '11 at 06:33 AM aRookieBIdev
there is basically only 5 lines of code, everything else is automatically generated for you when you create the script task. If you google vb to c# you will get a plethora of websites that can convert it. The script component is used in the dataflow, while the spcript task is used in the control flow. you don't need any custom script task components (not sure if you would ever need them?), just use the standard one.
Dec 12 '11 at 06:45 PM Daniel Ross
(comments are locked)
10|1200 characters needed characters left

EDIT. I should really learn to read/listen before I speak...

Go with the suggestions from @KenJ or @Daniel Ross and forget about mine...

Time to get some sleep (apparently...)
more ▼

answered Dec 08 '11 at 02:27 PM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
15.9k 15 19 32

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816
x900
x562

asked: Dec 08 '11 at 04:26 AM

Seen: 3976 times

Last Updated: Mar 29 at 03:23 PM