DTSX

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)

SSIS: Config Connection info through configuration file/SQL

Categories: Database; Tagged with: DTSX • SQL Server • SSIS; @ 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