question

jhowe avatar image
jhowe asked

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...
sql-server-2008ssissql-server-2008-r2
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
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.
0 Likes 0 ·
jhowe avatar image jhowe commented ·
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.
0 Likes 0 ·
jhowe avatar image jhowe commented ·
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...
0 Likes 0 ·
KenJ avatar image KenJ commented ·
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
0 Likes 0 ·
jhowe avatar image
jhowe answered
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(); } } }
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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...)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Daniel Ross avatar image
Daniel Ross answered
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 _ _ 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
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

aRookieBIdev avatar image aRookieBIdev commented ·
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
1 Like 1 ·
jhowe avatar image jhowe commented ·
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?
0 Likes 0 ·
Daniel Ross avatar image Daniel Ross commented ·
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.
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
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.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image Usman Butt commented ·
+1 from here
0 Likes 0 ·
Shawn_Melton avatar image Shawn_Melton commented ·
+1, why bother with custom code when an easier process is provided...
0 Likes 0 ·
jhowe avatar image jhowe commented ·
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...
0 Likes 0 ·
Pavel Pawlowski avatar image Pavel Pawlowski commented ·
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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.