Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Using DTS to Detect and Process a File

By Bruce Szabo,

Using DTS to Detect and Process a File

Introduction

There is nothing better when a new technology lends itself to an old problem. One of the classic old problems in computing has been to process files that were uploaded to a server. As this problem arose in a recent situation I was ablt to use DTS to help upload a file into SQL server.

In order to process the file the first step was to be able to detect the arrival of the file via FTP. Once this had been accomplished the file needed to be loaded into SQL server. The loading of a flat file to SQL server can be handled by a seperate DTS package but the more difficult task was detecting, unzipping and starting the processing of the file.

Getting Started

The following is a diagram of what the final DTS package looks like in the DTS Design window. The package is broken down into four parts. Three of the parts are ActiveX Script Tasks while the fourth launches another DTS Process.

Knowing and experimenting with the various objects one can use in the DTS process quickly expands the tasks that can be accomplished. This was never as evident as working on this project. DTS had been used previouly to perform transformations between different database formats and used to combine fields but the ability to run ActiveX scripts in the middle of the DTS process allowed for some nice logging and file manipulation.

File Arrival Check

The beginning of the DTS process checks for a file that is created during the DTS process. In creating a process that can run continuously it is important to check to see if the process had failed on a previous attempt. In this case if the file that was to be imported into the SQL server still exists the DTS package failed to properly process on a previous run and the new processing should not start.

The script to check for the import.txt file follows.

toImport.txt Check Script

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()
'
' Create needed FileSystemObject and setup the directories for the file locations.
'
	Set objFSO  = CreateObject("Scripting.FileSystemObject")
	strLogFile = "D:\Log\objPartsLog.txt"
	strFTPDir = "C:\inetpub\ftproot"
	strLandingDir = "D:\Landing"
	strPublishDir = "D:\Publish"
	strZipArchiveDir = "D:\Archive"

'
' Check to see if a log file exists if it does open it.  If not create it.
'

	If not objFSO.FileExists(strLogFile) then
		Set  master = objfso.CreateTextFile(strLogFile)
		master.WriteLine "File Created @ " & now
		master.WriteLine "          In toImport Check Routine @ " & now
	Else
		Set  master = objFSO.OpenTextFile(strLogFile,8)
		master.WriteLine
		master.WriteLine "File Opened @ " & now
		master.WriteLine "          In toImport Check Routine @ " & now
	End if

	Set fso      = CreateObject("Scripting.FileSystemObject")

'
	If fso.FileExists(strPublishDir &  "\toImport.txt") then
		master.WriteLine "          toImport.txt EXISTS it should not exist at this point  @ " & now		
		fso.DeleteFile strPublishDir & "\toImport.txt"
		Main = DTSTaskExecResult_Failure
	Else
		Main = DTSTaskExecResult_Success
	End if
	master.WriteLine	 "FILE CLOSED @" & now
	master.close

	set fso = nothing
	set objfso = nothing
	set master = nothing
	set objfsoFolder = nothing

End Function

Marker File

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()
'
' Create needed FileSystemObject and setup the directories for the file locations.
'
	Set objFSO  = CreateObject("Scripting.FileSystemObject")
	strLogFile = "D:\Log\objPartsLog.txt"
	strFTPDir = "C:\inetpub\ftproot"
	strLandingDir = "D:\Landing"
	strPublishDir = "D:\Publish"
	strZipArchiveDir = "D:\Archive"

'
' Check to see if a log file exists if it does open it.  If not create it.
'

	If not objFSO.FileExists(strLogFile) then
		Set  master = objfso.CreateTextFile(strLogFile)
		master.WriteLine "File Created @ " & now
		master.WriteLine "          In LAST File Exists Routine @ " & now
	Else
		Set  master = objFSO.OpenTextFile(strLogFile,8)
		master.WriteLine
		master.WriteLine "File Opened @ " & now
		master.WriteLine "          In LAST File Exists Routine @ " & now
	End if

	Set objFSOFolder = objfso.getfolder(strFTPDir)
	for each objFSOFile in objfsofolder.files
		if ucase(right(objFSOFile.name,4)) = "MARK" then
			strFileNameBase = trim(left(objFSOFile.Name, len(objFSOFile.name)-5))
			master.WriteLine	objFSOFile.name & " detected @ " & now
			master.WriteLine	strFileNameBase & " BASENAME "
		end if
	next
	Set fso      = CreateObject("Scripting.FileSystemObject")

	If fso.FileExists(strFTPDir & "\" & strFileNameBase & ".zip") then
		master.WriteLine	strFileNameBase & "          .zip exists @" & now		
		master.WriteLine	strFileNameBase & "           *********** LAST FILE EXISTS ***** SUCCESS******"
'
' Move File to the Archive Directory
'
		fso.copyfile strFTPDir & "\" & strFileNameBase & ".zip", strZipArchiveDir & "\" & strFileNameBase & ".zip",true
		If fso.FileExists(strZipArchiveDir & "\" & strFileNameBase & ".zip") then
			master.WriteLine	strFileNameBase & "          .zip was moved to the Archive Folder @" & now		
		else
			master.WriteLine	strFileNameBase & "          .zip move to the Archive Folder FAILED @ " & now		
		end if
'
' Unzip the file to the Publish Directory
' 
		Set WshShell = CreateObject("WScript.Shell")
		strUnzip = "D:\Ford\landing\pkzipc.exe -extract -over=all  " & strFTPDir & "\" & strFileNameBase & ".zip " & strPublishDir
		WshShell.Run strUnZip,,true
		master.WriteLine	strFileNameBase & "          .txt was created in the Publish Folder @" & now
		Set WshShell = nothing
'
' Copy the file to a file named toImport.txt
'
		fso.copyfile strPublishDir & "\" & strFileNameBase & ".txt", strPublishDir & "\toImport.txt",true
		master.WriteLine	"           " & strFileNameBase & ".txt was copied to toImport.txt  in the Publish Folder @" & now		
'
'Clean Up Old Files and signal success for process
'
		fso.DeleteFile strFTPDir & "\" & trim(strFileNameBase) & ".MARK"
		master.WriteLine	"           " & strFileNameBase & ".last was Deleted @" & now		
		fso.DeleteFile strFTPDir & "\" & trim(strFileNameBase) & ".zip"
		master.WriteLine	"           " & strFileNameBase & ".zip was Deleted @" & now		
		fso.DeleteFile strPublishDir & "\" & trim(strFileNameBase) & ".txt"
		master.WriteLine	"           " & strFileNameBase & ".txt was Deleted @" & now		
		Main = DTSTaskExecResult_Success
	Else
		master.WriteLine	strFileNameBase & "           *********** LAST FILE EXISTS *****FAILURE******"
		Main = DTSTaskExecResult_Failure
	End if
	master.WriteLine	 "FILE CLOSED @" & now
	master.close

	set fso = nothing
	set objfso = nothing
	set master = nothing
	set objfsoFolder = nothing

End Function

Run another DTS Process

Remove File

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()
'
' Create needed FileSystemObject and setup the directories for the file locations.
'
	Set objFSO  = CreateObject("Scripting.FileSystemObject")
	strLogFile = "D:\Log\objPartsLog.txt"
	strFTPDir = "C:\inetpub\ftproot"
	strLandingDir = "D:\Landing"
	strPublishDir = "D:\Publish"
	strZipArchiveDir = "D:\Archive"

'
' Check to see if a log file exists if it does open it.  If not create it.
'

	If not objFSO.FileExists(strLogFile) then
		Set  master = objfso.CreateTextFile(strLogFile)
		master.WriteLine "File Created @ " & now
		master.WriteLine "          In toImport Delete Routine @ " & now
	Else
		Set  master = objFSO.OpenTextFile(strLogFile,8)
		master.WriteLine
		master.WriteLine "File Opened @ " & now
		master.WriteLine "          In toImport Delete Routine @ " & now
	End if

	Set fso      = CreateObject("Scripting.FileSystemObject")

'
	If fso.FileExists(strPublishDir &  "\toImport.txt") then
		master.WriteLine "          toImport.TXT deleted  @ " & now		
		fso.DeleteFile strPublishDir & "\toImport.txt"
		Main = DTSTaskExecResult_Success
	Else
		Main = DTSTaskExecResult_Failure
	End if
	master.WriteLine	 "FILE CLOSED @" & now
	master.close

	set fso = nothing
	set objfso = nothing
	set master = nothing
	set objfsoFolder = nothing

End Function

Conclusions

A DTS package is explored and the scripts used to detect a file arrival are explained. This process is used to load a file into SQL server but the power of SQL server allows this process to be used for more than just loading data into SQL server.

Total article views: 11188 | Views in the last 30 days: 5
 
Related Articles
FORUM

creating indexes in existing tables

creating indexes in existing tables

FORUM

Need a script to create a new user based on existing user's permission

create a nre user as copy from existing user

FORUM

unzip files using DTS

Hi, I need to create a DTS package which will first unzip the files and put into specific directo...

FORUM

script to create indexes existing in a database

script to create indexes existing in a database

FORUM

Process for populating an existing xsd

Populating an xml document using an existing file xsd

Tags
dts    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones