VB

SSIS: Append Header/Footer to flat file

Categories: Database; Tagged with: CMD • DTSX • ETL • SSIS • VB; @ November 25th, 2012 17:52

Requirement:

Append additional header/footer to the destination flat file:

Solution:

Header/Footer can be added by several ways in SSIS, here is 3 ways I’m using:

Usinig Union in SQL :


-- Header  
Select 'START' + convert(varchar(8),GETDATE(), 112)  
-- Body  
Union All  
Select FULLNAME  
from OVERSEA_ADDRESS  
WHERE POSTAL_CODE = ''  
-- Footer  
Union All  
Select 'END'+ Convert(varchar(8), Count(*))  
from TWO_FA_OVERSEA_ADDRESS  
WHERE POSTAL_CODE = ''

But sometimes the requirement is more complex, then we need to use Script task.

Script task using VB/C#

We can read/write file using VB or C#, and we can get the variables/paremeters, so basically, we can handle most of the requirement by Script task.

Reading file using VB:

Dim objReader As New System.IO.StreamReader(FILE_NAME)

' Read each line, and append the text into textLines Dim currentLine As String Do While objReader.Peek() <> –1 currentLine = objReader.ReadLine() textLines.Add(currentLine) Loop

Writing to file

' For loop the textLines Dim index As Integer For index = 0 To textLines.Count – 1 Dim text As String = textLines.Item(index).ToString outStreamWriter.Write(text & vbNewLine) NextoutStreamWriter.Close()

Using copy CMD

Generate the header, body, foot file and use CMD to combine the files:


copy header.txt+body.txt+footer.txt output.txt /y

SSIS: Get Variables/Properties In Script task

Categories: Database; Tagged with: DTSX • ETL • SQL Server • SSIS • VB; @ November 25th, 2012 15:52

Requirement:
Get variables/properties in Script task.

Note: Do remember to set the variables in the component editor.

Solution(in VB):

1. Get properties:
MsgBox(Dts.Connections(“fileDest”).ConnectionString)

2. Get variables:
MsgBox(Dts.Variables(“User::Counter”).Value.ToString)