SSIS: Append Header/Footer to flat file

Categories: Database; Tagged with: ; @ November 25th, 2012 17:52

Requirement:

Append additional header/footer to the destination flat file:

image

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)
Next
outStreamWriter.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: ; @ 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)

SSIS: Config Connection info through configuration file/SQL

Categories: Database; Tagged with: ; @ September 27th, 2012 14:41

It’s not a good idea to hardcode the connection info in the pacakge, a better solution is store connection info in SSIS package configurations.
SSIS support different configuration type: XML/Environment variable/Registry entry/ Parent package variable/SQL server.

How to config it?
SSIS > Package configurations, New… chose the paramters you want to config.
then modify it in the configuration file or database.

Test: open your pacakge using Execute package utility, If you are going to use configuration file, you need to add the config file before executing, open the ‘Configurations’ tab, add your config file. if you are using SQL Server to store your configurations, no need to do this.
then, run your pacakge.

But SSIS can not encrypt data, there are 2 solutions:
i. using configuration file: NTFS encryption and/or ACLs to control access to config files and contents, or use third party tools.
ii. using SQL server: will store config info in database,  use SQL security to control access.

1. SSIS Package Config File Encryption
2. encrypting ssis package configuration file

 

 

Newer Posts



// Proudly powered by Apache, PHP, MySQL, WordPress, Bootstrap, etc,.