Friday, February 26, 2010

SSIS 2005: Reading a value from a Flat file

This is a small explanation to read a value from a flat file into a variable in SSIS 2005, if you know a better process please comment on this post.

1) Create an package object type variable to store it. This variable will be like an temporary table for your data. (User::Value_FileCount) and the variables for the values read from the file.

2) Using a DataFlow task
2.1) create a Flat File Source (with the connection to the file you want to read)

and map the columns...

2.2) create a Recordset Destination and choose the 'Value_FileCount' into the 'variable name' property.

and again map the values read to the object variable 'rows' (this is when you read from the file into 'Value_FileCount', building the memory data)

3) Going back to the Control Flow and to read from the object variable, add a Foreach Loop
3.1)Set it as a 'Foreach ADO Enumerator', reading from the 'Value_FileCount'

3.1) Map the columns from the 'Value_FileCount' into the variables accordingly to the columns you are reading (in this case just one - 'FileValue')

4) Inside the Foreach Loop container insert a script task
4.1) Pass the variables that you want to set values to. Remember that if you want to change the variables values you must pass them in the 'ReadWriteVariables' section, her I'm just reading the value.

4.2) Add the script to read the variables values


Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
Public Sub Main()
'Retrieves the value from the variable
MsgBox("Read from file as FileValue = " & Dts.Variables("FileValue").Value.ToString)
Dts.TaskResult = Dts.Results.Success
End Sub

End Class


That is it.

The end result is something like this:

With the data flow looking like this:

Hope it helps,