Script Component in SSIS

  • 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)

    vars.Unlock()

    'Output one row with record count

    Output0Buffer.AddRow()

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

    Output0Buffer.SetEndOfRowset()

    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

    Dts.VariableDispenser.LockForRead("RecordCount")

    Dts.VariableDispenser.LockForRead("FinalFile")

    Dts.VariableDispenser.GetVariables(vars)

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

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

    vars.Unlock()

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

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

    fileContents.Append(File.ReadAllText(Dts.Connections("Destination").ConnectionString))

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

    Dts.TaskResult = ScriptResults.Success

    End Sub

    End Class

    Here is the link to which I followed : http://agilebi.com/cs/blogs/jwelch/archive/2008/02/08/adding-headers-and-footers-to-flat-files.aspx

    Thanks in Advance Guys

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

    Thanks!

    Emilly

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply