This is written for sql server v7
DTS has hard coded source and destination
strings which means that when moving between systems these values need to
be changed.
This can be solved by the use of a startup activex script, global
variables and some means of loading the package and setting the variables
before it runs.

This is for a simple file import

Declare Global Variables for the package
ServerName
DatabaseName
FileName

The dts package has an activex startup task on success of which the import
task runs.
A data transformation from a text source 'SourceFile' to a sql server
table.
And some logging Execute SQL tasks with descriptions LogFailureImport,
LogSuccessImport which call an SP with the FileName.

The package can now use these variables to change the source and
destination of steps.

Public objPackage

sub main()

' First of all get the Package handle
Set objPackage = DTSGlobalVariables.parent

' now set all the server connections - see below for SetServer
Call SetServer(DTSGlobalVariables("ServerName").Value,DTSGlobalVariables("DatabaseName").Value)

' Set the Source File
objPackage.Connections("SourceFile").DataSource = DTSGlobalVariables("FileName").Value

' Set the Logging tasks sql statements
dim sSQL
dim objSQLTask
	
	Set objSQLTask = objPackage.Tasks(GetTaskNumber("LogFailureImport")).CustomTask
	sSQL = "exec spLog 999, '" & DTSGlobalVariables("FileName").Value & "'"
	objSQLTask.SQLStatement = sSQL
	
	Set objSQLTask = objPackage.Tasks(GetTaskNumber("LogSuccessImport")).CustomTask
	sSQL = "exec spLog -1, '" & DTSGlobalVariables("FileName").Value & "'"
	objSQLTask.SQLStatement = sSQL
	
	Set objSQLTask	= Nothing

	Main = DTSTaskExecResult_Success
End Function


' functions used - define before main.
Private Sub SetServer(sServer, sDatabase)
    Dim i
    For i = 1 To objPackage.Connections.Count
	If objPackage.Connections(i).ProviderID = "SQLOLEDB.1" Then
		objPackage.Connections(i).DataSource = sServer
		objPackage.Connections(i).Catalog = sDatabase
	End If
    Next 
End Sub

'Tasks are referenced by the task name or number which aren't very visible
so use the description
Private Function GetTaskNumber(sDescription)
    Dim i
    For i = 1 To objPackage.Tasks.Count
        If objPackage.Tasks(i).Description = sDescription Then
            GetTaskNumber = i
        End If
    Next
End Function