October 21, 2004 at 9:53 am
Hi
I need to import csv & xml files automatically into a sql server table, ideally I need it to recognise when a file is in a directory then run an import (DTS presumably) and then archive the imported file.
The major problems I am encountering is getting it to realise there is a file waiting and picking up the filename which changes with every file!
If anyone has any ideas I would appreciate it.
Thanks
Andrew
October 21, 2004 at 1:55 pm
this is a rather quick cut 'n paste from a process I use for a similar task.
It moves files from a download area to a workarea renaming the file with today's date and the process time (all the files I receive have the same name each day, to archive, I need to rename them).
It also writes the name of the file to an ImportExportLog table which can then be used for the process loop package (built using the loop example at sqldts.com) which reads the name of each file and processes it.
The archive task is a cut from this task.
Note I open the file for read and then write a new file - I have removed some stuff here where I do some data manipulation which you may not be doing. It may be an option to use the FSO to simply move the file w/o opening it.
'*************************************************************************
' File Move script.
' Used to move files from the downloads directory to
' the workarea directory.
' Server name and directory values are read from DTS global
' variables
'***********************************************************************
Option Explicit
Dim oFSO
Dim objCommand
Dim objConnection
Dim objRecordSet
Dim sServerName
Dim sTargetFolder
Dim sErrorFolder
Dim sRootFolder
Dim sSubFolders
Dim sFiles
Dim sExtensionString
Dim sTableName
Dim sSpecName
Dim sFormatId
Dim sSplit
Dim sSplitControl
Dim sErrorFile
Dim bRenameFile
Dim sDay
Dim sMonth
Dim sHour
Dim sMinute
Dim sSecond
Dim sDate
Dim sUserID
Dim sPassword
'*************************************************************************
' Default UserID and Password
'*************************************************************************
sUserId = ""
sPassword = ""
'*************************************************************************
'get today's date
sDate = Now()
' Get Rename parameter from global variable
bRenameFile = DTSGlobalVariables("RenameFile").Value
IF bRenameFile THEN
'Get date and time values to use in rename extension
sDay = Day(Date)
IF Len(sDay) = 1 THEN sDay = "0" & sDay
sMonth = Month(Date)
IF Len(sMonth) = 1 THEN sMonth = "0" & sMonth
sHour = Hour(Time)
IF Len(sHour) = 1 THEN sHour = "0" & sHour
sMinute = Minute(Time)
IF Len(sMinute) = 1 THEN sMinute = "0" & sMinute
sSecond = Second(Time)
IF Len(sSecond) = 1 THEN sSecond = "0" & sSecond
sExtensionString = sMonth & sDay & Year(Date) & sHour & sMinute & sSecond
End If
' current server name
sServerName = DTSGlobalVariables("ServerName").Value
' Create File System Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
' Get the from (download) directory
Set sRootFolder = oFso.GetFolder(DTSGlobalVariables("FromDirectory").Value)
' Then get the list of SubFolders beneath this root folder
Set sSubFolders = sRootFolder.SubFolders
' Get the move to (work) directory
sTargetFolder = DTSGlobalVariables("ToDirectory").Value
If Right(sTargetFolder, 1) <> "\" Then
sTargetFolder = sTargetFolder & "\"
End If
' error directory
sErrorFolder = DTSGlobalVariables("ErrorDirectory").Value
If Right(sErrorFolder, 1) <> "\" Then
sErrorFolder = sErrorFolder & "\"
End If
'connect to the database
Set objConnection = CreateObject("ADODB.Connection")
objConnection.ConnectionString = "Provider=sqloledb;Data Source=" & sServerName & ";Initial Catalog=YOUR DATABASE;User Id=" & sUserID & ";Pwd=" & sPassword
objConnection.Open
'build a command object to use for SQL Inserts into task history
Set objCommand = CreateObject("ADODB.Command")
Set objCommand.ActiveConnection = objConnection
Set objRecordSet = CreateObject("ADODB.RecordSet")
'*********************************************************************************************************************************
' Main Function for this script
' Loop through downloads directory, pass each file found in root or any subdirectory of
' the root to a split function which will move the file to the workarea directory and split it if it
' has multiple header rows
'*********************************************************************************************************************************
Function Main()
' ***************************************************************************
' Log process start
' ***************************************************************************
objCommand.CommandText = "Insert Into TaskHistory (PackageName, StepName, RunDate, OrigFileLocation)"
objCommand.CommandText = objCommand.CommandText & "Values ('File Split', 'Start', convert(datetime,'" & sDate & "',101), 'Begin Move/Split')"
objCommand.Execute
' Check for files in root folder -- not supposed to be any at this level, but
' account for error
Set sFiles = sRootFolder.Files
For Each sFiles in sFiles
move sFiles.Path, sFiles.Name
oFSO.DeleteFile(filePath)
Next
' Loop through each subfolder
For Each sSubFolders in sSubFolders
' Get the list of files in this subfolder
Set sFiles = sSubFolders.Files
move sFiles.Path, sFiles.Name
Next
' Clean Up
Set oFSO = Nothing
Main = DTSTaskExecResult_Success
End Function
'*********************************************************************************************************************************
' Move files without splitting
'*********************************************************************************************************************************
Function move ( filePath, fileName) ', extensionString )
Const ForReading = 1
Const ForWriting = 2
Const Appending = 8
Dim objTextStream
Dim strText
Dim bValidHeader
Dim outFileName
Dim tso
Dim iFileNumber
objCommand.CommandText = "Insert Into TaskHistory (PackageName, StepName, RunDate, OrigFileLocation)"
objCommand.CommandText = objCommand.CommandText & "Values ('File Split', 'Processing File', convert(datetime,'" & sDate & "',101), '" & filePath & "')"
objCommand.Execute
'***************************************************************************
' FileSystemObject.OpenTextFile(fname,mode,create,format)
'***************************************************************************
'Parameter Description
'fname Required. The name of the file to open
'mode Optional. How to open the file
'1=ForReading - Open a file for reading. You cannot write to this file.
'2=ForWriting - Open a file for writing.
'8=ForAppending - Open a file and write to the end of the file.
'create Optional. Sets whether a new file can be created if the filename does not exist.
'True indicates that a new file can be created, and False indicates that a new file will not be created. False is default
'format Optional. The format of the file
'0=TristateFalse - Open the file as ASCII. This is default.
'-1=TristateTrue - Open the file as Unicode.
'-2=TristateUseDefault - Open the file using the system default.
'***************************************************************************
' Open the passed in file for read
Set objTextStream = oFSO.OpenTextFile(filePath , ForReading)
If not objTextStream.AtEndOfStream Then
'read first line
strText = objTextStream.ReadLine
Else
objTextStream.Close
sErrorFile = sErrorFolder & fileName & "." & sExtensionString
objCommand.CommandText = "Insert Into TaskHistory (PackageName, StepName, RunDate, OrigFileLocation)"
objCommand.CommandText = objCommand.CommandText & "Values ('File Process', 'Error', convert(datetime,'" & sDate & "',101), '" & sErrorFile & " is an empty file.')"
objCommand.Execute
oFSO.MoveFile filePath, sErrorFile
Exit Function
End If
'set up the output file name
' iFileNumber = 1
outFileName = sTargetFolder & fileName & "." & sExtensionString ' & "." & iFileNumber
'and open it for write
Set tso = oFSO.OpenTextFile(outFileName, ForWriting, True)
'write the first line to the output file
tso.WriteLine(strText)
'read the next line
strText = objTextStream.ReadLine
'if not at end, go into loop and write records until EOF
If NOT objTextStream.AtEndOfStream Then
Do While NOT objTextStream.AtEndOfStream
tso.WriteLine(strText)
strText = objTextStream.ReadLine
If IsNull( strText ) or LTrim(Rtrim(strText)) = "" THEN
objCommand.CommandText = "Insert Into TaskHistory (PackageName, StepName, RunDate, OrigFileLocation)"
objCommand.CommandText = objCommand.CommandText & "Values ('File Process', 'Error', convert(datetime,'" & sDate & "',101), '" & FileName & " delivered with blank data lines. Bypassed..')"
objCommand.Execute
End If
Loop
tso.WriteLine(strText)
' write an entry into ImportExportLog
objCommand.CommandText = "Insert Into ImportExportLog ( OrigFileLocation)"
objCommand.CommandText = objCommand.CommandText & "Values ( '" & outFileName & "')"
objCommand.Execute
tso.Close
Else
objTextStream.Close
sErrorFile = sErrorFolder & fileName & "." & sExtensionString
oFSO.MoveFile filePath, sErrorFile
objCommand.CommandText = "Insert Into TaskHistory (PackageName, StepName, RunDate, OrigFileLocation)"
objCommand.CommandText = objCommand.CommandText & "Values ('File Split', 'Error', convert(datetime,'" & sDate & "',101), '" & sErrorFile & " has no data records.')"
objCommand.Execute
tso.Close
Exit Function
End If
objTextStream.Close
Set objTextStream = Nothing
Set tso = Nothing
oFSO.DeleteFile(filePath)
End Function
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply