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, 2011 at 04:26 AM in Default

avatar image

1.1k 56 60 66

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, 2011 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, 2011 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, 2011 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, 2011 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
         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();
             return recs;
         public static void WriteToFile(string filePath, string fileText)
             var sw = new StreamWriter(filePath, false);
             sw.Write(fileText, Encoding.ASCII);
more ▼

answered Jan 18, 2012 at 11:34 PM

avatar image

1.1k 56 60 66

(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, 2012 at 03:06 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

  • from here

Jan 19, 2012 at 05:07 AM Usman Butt

+1, why bother with custom code when an easier process is provided...

Jan 19, 2012 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, 2012 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, 2012 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()
     ' 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()
     ' 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")
 End Sub

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

     ' MessageBox.Show("TEST")
 End Sub

 End Class
more ▼

answered Dec 08, 2011 at 03:59 PM

avatar image

Daniel Ross
2.9k 11 15 18

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, 2011 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, 2011 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, 2011 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, 2011 at 02:27 PM

avatar image

Magnus Ahlkvist
21.1k 19 39 42

(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Dec 08, 2011 at 04:26 AM

Seen: 10149 times

Last Updated: Mar 29, 2014 at 03:23 PM

Copyright 2016 Redgate Software. Privacy Policy