Hi! I need to import a text file that has the date in the first row, and some
data columns in other rows. That date I need to insert in the same table for
every imported record. What is the best way to do that?

In an Active Script task open up the file and read the first line.
Assign whatever you find to a global variable. Remove the first row and
save back the text file (or write out somewhere else.)

Now in your transformations (Active X) you can assign a destination
column the value of a Global Variable

DTSDestination("Column Name") = DTSGlobalVariables("My Date").Value





"Yuliaan" <Yuliaan (AT) discussions (DOT) microsoft.com> wrote

Thank you! I've thought about this, but how can I read only the first line?

"Allan Mitchell" wrote:

This ActiveX script will take a file, read the first line, assign it to a gv
then copy the rest of the file to a temp output. It then deletes the original
file and renames the temp output with the name of the original file.

Seems a bit long winded, but VBScript has no built in functions for deleting
data, only files.

Option Explicit

Function Main()

Dim FilePathIn
Dim FilePathOut

':: FilePathIn is the file you want to read and import.
':: FilePathOut is a temp file that is eventually renamed to FilePathIn
FilePathIn = "C:\ImportFile.txt"
FilePathOut = "C:\TempImportFile.txt"

Dim FSO
Dim TextFileIn
Dim TextFileOut

Dim Read
Dim ReadLine
Dim ReadAll

':: Define our FSO constants
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8

Set FSO = CreateObject("Scripting.FileSystemObject")

':: Check that the file exists
If FSO.FileExists(FilePathIn) Then

':: Open the file for reading
Set TextFileIn = FSO.OpenTextFile(FilePathIn, ForReading)

On Error Resume Next

':: Read the first line
ReadLine = TextFileIn.ReadLine

':: The file is empty
If Err.Number = 62 Then

':: NB
':: Include your own routine for dealing with this situation

':: Clean up
Call ObjDispose(TextFileIn,True,False)

Else

':: Assign the value to your global variable
':: This needs to be done now as the variable ReadLine contains
':: only the first line of the file at this point in time.
DTSGlobalVariables("My Date").Value = ReadLine

':: Initialise TempImportFile
Set TextFileout = FSO.CreateTextFile(FilePathOut, ForWriting)

':: Loop through the rest our the file, exluding the first line, and
':: write to TempImportFile
Do While Not TextFileIn.AtEndOfStream

ReadLine = TextFilein.ReadLine
TextFileOut.WriteLine(ReadLine)

Loop

':: Clean up
Call ObjDispose(ReadLine,False,True)
Call ObjDispose(TextFileIn,True,False)
Call ObjDispose(TextFileOut,True,False)

':: Delete ImportFile
Set FilePathIn = FSO.GetFile("C:\ImportFile.txt")
FilePathIn.Delete()

':: Copy the contents of TempImportFile file into a new file called
ImportFile.
Set FilePathOut = FSO.GetFile("C:\TempImportFile.txt")
FilePathOut.Copy("C:\ImportFile.txt")
':: Delete TempImportFile.
FilePathOut.Delete()

End If

Else

':: The file did not exist
':: NB
':: Include your own routine for dealing with this situation

End If

':: Clean up
Call ObjDispose(FSO,False,True)

Main = DTSTaskExecResult_Success

End Function

Sub ObjDispose( _
ByRef ObjToDispose, _
ByVal SetClose, _
ByVal SetNothing _
)

If SetClose Then
ObjToDispose.Close
End If

If SetNothing Then
Set ObjToDispose = Nothing
End If

End Sub

I've highlighted parts of the script that will require some attention RE
error handling.

Joe

I wouldn't bother with reading the entire file out to a new file. Read the
first line and store it in a GV, then just use a DataPump to load that same
file, setting skip rows to 1 so you ignore that date row. Reading files in
VBScript is pretty slow, and I cannot see the need to read any more than the
top line in the script.

--
Darren Green
http://www.sqldts.com
http://www.sqlis.com

"Joe" <Joe (AT) discussions (DOT) microsoft.com> wrote