DTS Package Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS Package Question

Author

Topic

kwilliams

194 Posts

Posted - 2004-11-16 : 17:41:06

I have a DTS Package in SQL Server 2000 that does the following:
Step 1) ActiveX Script Task: To check for a file in Directory 1

On failure of Step 1:
Step 2) Send Email Task: Sends basic notification email that job's failed

On success of Step 1:
Step 2) Delete from TABLENAME: Delete job for entire table
Step 3) Move text file from Directory 1 to Directory 2
Step 4) Copy data from comma-delimited text file in Directory 2 into DB table
***NOTE: The reason for Steps 2 and 3 is because whenever the user tried to upload a new version of that file into Directory 1, the job received an error because it already existed. I tried to add an "overwrite" option to it, but it didn't work for some reason (probably Permissions). Since I don't setup or maintain Permissions, I was forced to develop the workaround in Steps 2 and 3.

This package works great at what I need it to do. But I don't want the job to say that it's failed if there was no file in Directory 1. So how would I add a job that would stop the rest of the package upon Step 1 failing? I know that it's probably a simple answer, but I'm trying:) Thanks for any help.

kwilliams

194 Posts

Posted - 2004-11-16 : 17:50:45

...by the way, here's the code for the ActiveX Script:

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
Function Main()
	Main = DTSTaskExecResult_Success
	Dim fso, f, i, j, k, t, row, datetime_array, StartingKeyValue, StartingOtherValue, NewStartingKey, NewStartingOther, swap_pos, DimensionToSort, OtherDimension
	Dim NewFile, OldFile2, log, incoming, incoming_files, dir_count, willholddate
	Set fso = CreateObject("Scripting.FileSystemObject")
	Set incoming = fso.GetFolder("\\SERVERNAME\DIRECTORY\DIRECTORY1\") 
	Set incoming_files = incoming.Files
	dir_count = incoming_files.Count
	ReDim datetime_array(dir_count, 1)
	' *** This loop will load the array with the DateLastModified file attribute ***
	For Each k in incoming_files
		datetime_array(i, 0) = k.Name
		datetime_array(i, 1) = k.DateLastModified
		i = i + 1
	Next 
	' *** This loop will perform the DateTime sort ***
	Const column = 1
	DimensionToSort = 1
	OtherDimension = 0
	For row = 0 To dir_count - 1
		StartingKeyValue = datetime_array ( row, DimensionToSort )
		StartingOtherValue = datetime_array ( row, OtherDimension )
		NewStartingKey = datetime_array ( row, DimensionToSort )
		NewStartingOther = datetime_array ( row, OtherDimension )
		swap_pos = row
	    	For j = row + 1 to dir_count - 1
			If datetime_array ( j, DimensionToSort ) < NewStartingKey Then
				swap_pos = j
				NewStartingKey = datetime_array ( j, DimensionToSort )
				NewStartingOther = datetime_array ( j, OtherDimension )
			End If
		Next
		If swap_pos <> row Then
			datetime_array ( swap_pos, DimensionToSort ) = StartingKeyValue
			datetime_array ( swap_pos, OtherDimension ) = StartingOtherValue
			datetime_array ( row, DimensionToSort ) = NewStartingKey
			datetime_array ( row, OtherDimension ) = NewStartingOther
		End If	
	Next
	' *** This is where the file gets set ***
	NewFile = "\\SERVERNAME\DIRECTORY\DIRECTORY1\" & datetime_array(dir_count - 1, 0)
	RenamedFile = "\\SERVERNAME\DIRECTORY\DIRECTORY1\import.txt"
	OldFile = "\\SERVERNAME\DIRECTORY\DIRECTORY2\" & datetime_array(dir_count - 1, 0)
	Import = "\\SERVERNAME\DIRECTORY\DIRECTORY2\import.txt"
	' *** Rename the file to the name that the Data Source expects ***
	If fso.FileExists(NewFile) Then
	fso.MoveFile NewFile, RenamedFile
	fso.CopyFile RenamedFile, Import
	fso.DeleteFile RenamedFile
	End If
End Function

Katherine Williams
-------------------
It's the end of the world as we know it...and I feel fine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources