Using ActiveX to check the age of a file

Sometimes you need to check the age of a file before proceeding. You really don't want to work with old data, so this is how you can do this in the middle of an ActiveX task.

Option Explicit

Function Main()
Dim oFSO, oConn, sFileName, oFile

' Get the filename from my Text File connection called "Text File (Source)"
' There are other ways to do this, of course.
Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Source)")
sFilename = oConn.DataSource
Set oConn = Nothing

Set oFSO = CreateObject("Scripting.FileSystemObject")

' Check File Exists first
If Not oFSO.FileExists(sFilename) Then
' Return Error
Main = DTSTaskExecResult_Failure
Else
' Get file object
Set oFile = oFSO.GetFile(sFilename)

' Check age of file is less than 24 hours
If DateDiff("h", oFile.DateLastModified, Now) >= 24 Then
' Return Error
Main = DTSTaskExecResult_Failure
Else
' Return Success
Main = DTSTaskExecResult_Success
End If
End If

Set oFile = Nothing
Set oFSO = Nothing

End Function

Using ActiveX to Dynamically set a filename

From time to time, you will need to dynamically set a filename in the middle of your DTS packages. The following code shows you how easy it is to build a filename dynamically:

'*************************
' Sets the file name to the format: MyFile_YYYYMMDD.csv
'*************************
Function Main()

Dim oPkg, oBulkExport

'Set the Path where the file is located
strPath = "c:\MyPath\"

'Next build the different parts of the file name,
'including the prefix and file extension using
'the Now function, which returns the current date and
'the Month(),Day(), and Year() function which
'return the respective datepart.
strPrefix = "MyFile_"
strMonth = Month(Now)
strDay = Day(Now)
strYear = Year(Now)
strFileExt = ".csv"

'If you only require 2 digit years, trim the first 2 digits
'strYear = Right(strYear, 2)

'If the day or month is a single digit, the
'Now function will only return that single digit
'We will append a leading zero to the Month
'and/or Date string if needed
If Len(strMonth) = 1 Then strMonth = "0" & strMonth
If Len(strDay) = 1 Then strDay = "0" & strDay

'Concatenate all parts of the file name with
'the file path in front.
outString = strPath & strPrefix & strYear & strMonth & strDay & strFileExt

'Once the path and file name are determined,
'set the global variable.
'You will still need to set the file name
using a Dynamic Properties Task.
DTSGlobalVariables("gv_FileName").Value = outString

Main = DTSTaskExecResult_Success
End Function

As stated in the code, you will still need a Dynamic Properties Task to actually set the file name, as you are only setting a global variable in the code.

ANSI warnings, NOCOUNT and Temp Tables

So, I'm always needing these code bits. And yet, I forget the exact syntax and I have to look all over the place for the exact code. Well, here it is. First, NOCOUNT. If you don't want SQL Server to return the Row Count after you execute a query, here is the code:

SET NOCOUNT ON

Next, do you get annoying ANSI errors when inserting data? Begone!

SET ANSI_WARNINGS OFF

Lastly, when working with true temp tables, the ones starting with ## or #, the old standard will not work. This is because Temp tables live in the database TempDB, not your current DB. SO, sysobjects won't have the table info you're looking for.

-- Won't Work!
IF EXISTS (SELECT * FROM sysobjects WHERE NAME='##tmpTable')
BEGIN
Truncate table ##tmpTable
DROP TABLE ##tmpTable
END

You need this:

-- THis will delete your temp table
IF OBJECT_ID('tempdb..##tmpTable') IS NOT NULL
BEGIN
Truncate table ##tmpTable
DROP TABLE ##tmpTable
END