Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Script Component in SSIS Expand / Collapse
Posted Wednesday, November 11, 2009 2:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 10:53 PM
Points: 27, Visits: 202
Hi Guys,

I am very much new SQL and VB, I am trying to add header, details and footer to flat files using VB. Below is the sample script I got it from one of the forum. But I need to replace all the variables with my requirement. Basically, this can be accomplished by creating the header or footer in a separate task, and then prefixing or appending it to the data. . The final output file, which includes the header, data, and footer rows.

Header Field Names: RecordTypeCode, RecordFormatVersionNo,DataDateFileIdentificationCode
Details from source table: LoanID, SourceID, Borrower, SetttlementDate, Repay
Footer: RecordTypeCode, RecordFormatVersionNo,DataDateFileIdentificationCode,DetailRecordCount (This has to be count of records in detail table), controlTotal1

SSIS package : DataFlow 1 (Row Count From source to Flat File) --> DataFlow 2 ( Append Footer through VB Script and place same flat file ) --> Script Component ( Add heater).

I have declared a variable exactly like this in Dataflow

First Data Flow eg. ETLLoanTableàRowCount (I have declared variable here as RecordCount) à FlatFileDestination).

Second Data Flow :
I am trying to append footer using below script. Below highlighted code need to change according my requirements, please can anyone help me with this.

' Microsoft SQL Server Integration Services user script component

' This is your new script component in Microsoft Visual Basic .NET

' ScriptMain is the entrypoint class for script components

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

Public Overrides Sub CreateNewOutputRows()

Dim recordCount As Integer

Dim vars As IDTSVariables100

'Get the record count

Me.VariableDispenser.LockOneForRead("RecordCount", vars)

recordCount = CType(vars("RecordCount").Value, Integer)


'Output one row with record count


Output0Buffer.FooterRow = String.Format("Footer Row Count: {0}", recordCount)


End Sub

End Class

Third One is just the custome script to Add Header

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.IO

Imports System.Text

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _

<System.CLSCompliantAttribute(False)> _

Partial Public Class ScriptMain

Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

Enum ScriptResults

Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success

Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

End Enum

Public Sub Main()

Dim fileContents As New StringBuilder()

Dim vars As Variables

Dim recordCount As Integer

Dim finalFile As String

'Get the record count




recordCount = CType(vars("RecordCount").Value, Integer)

finalFile = CType(vars("FinalFile").Value, String)


'Write header, then append file contents and write back out.

fileContents.AppendLine(String.Format("Header Row Count 1: {0}", recordCount))


File.WriteAllText(finalFile, fileContents.ToString())

Dts.TaskResult = ScriptResults.Success

End Sub

End Class

Here is the link to which I followed :

Thanks in Advance Guys

Post #817047
Posted Tuesday, April 05, 2011 10:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 05, 2011 10:18 AM
Points: 1, Visits: 3
Where do you define your File in the script?

I'm very new to vb and just need to add a static header to my text file, but I don't see where your file is defined.

For instance, I need to add the header 'EXTSYS1|MXOPERLOCInterface|AddChange|EN' to my 'locations.txt' file that I created in my SSIS package.

Post #1088756
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse