I recently saw a post on MSDN forums about loading a date value from a flat file into an SSIS variable. I thought I'd document one way to do it - I don't claim this is the best way, but it is one way that works.
First, I created a file named C:\FileDate.txt 
and put the date 1/12/2007 in it.
Next, I created a new SSIS package and renamed it VarFromFile.dtsx . I dragged a Data Flow task onto the Control Flow.
Double-click the Data Flow task to edit it. Drag a Flat File Source onto the Data Flow:
Double-click the Flat File Source to edit it. Click the New button to create a new Flat File Connection Manager:
I gave the Flat File Connection Manager the name FileDate, added a Description, and set the File name to C:\FileDate.txt:
Next, click the Columns item on the left to view the Row and Column delimiters and contents of the file:
Click OK to close the Connection Manager editor. The FileDate Flat File Connection Manager we just created is visible in the Flat File Connection Manager combobox:
Click OK to close the Flat File Source editor. Click the Control Flow tab. Right-click in the white space of the Control Flow tab and click Variables from the menu:
Create a new variable by clicking the New Variable icon (upper left). Name it vdtFileDate and configure the properties as shown:
Note: You cannot change the scope of the variable in the Variables window. Scope is controlled by the object that had focus when you created the variable - in this case, it was the Package.
Next, return to the Data Flow by either double-clicking the Data Flow Task or clicking the Data Flow tab. Drag a Script Component onto the Data Flow. You will be prompted to select the function of this script component (Source, Destination, or Tranformation). Choose Transformation:
Connect the data path (green arrow) from the Flat File Source to the Script Component:
Double-click the Script Component to open the editor. On the Input Columns page, select Column 0:
On the Script page, enter vdtFileDate into the ReadWriteVariables property:
Click the Design Script button to open Microsoft Visual Studio for Applications and perform the following edits:
- Beneath the Inherits UserComponent line add Dim myDate As Date
- In the Input0_ProcessInputRow subroutine add
With Row
myDate = CDate(.Column0.ToString)
End With
- After the End Sub for the Input_ProcessInputRow subroutine, add a new subroutine:
Public Overrides Sub PostExecute()
Me.Variables.vdtFileDate = myDate
MsgBox(Me.Variables.vdtFileDate.ToString)
End Sub
IMPORTANT: Do not leave the MsgBox command in deployed code. It will halt execution at this point. The MsgBox is here only to display the variable value has, in fact, been read from the file!
Execute by pressing the F5 key or clicking the green "Play" arrow on the SSIS IDE toolbar. You should see the results in a message box:
I'm certain there are better ways to get a variable value from a flat file. Please comment if you know one!
:{> Andy
Technorati Tags: SSIS Variable File