Recognation of file name in dts

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)

Recognation of file name in dts

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-03-21 : 06:03:56

Hi,

Is there any way that dts can pickup the last created or last modified file from a specific location either over a lan or in a ftp as a source file for the transformation. I might have a source file in the format of .xls or .txt in a specific location the file name will be different based on the date of creation.

A likely scenario is like this I run my dts today and the next scheduled run is on next monday, when it runs next monday the dts has to pick the file which has the file name like 27Mar2005.txt and the next monday the file will be 03Apr2005.txt

Any ideas?

One way I thought about this is like finding the last created file in that location, but how can i do this?

Karunakaran

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-03-22 : 06:37:14

Hi
You can find the file last created within a particular directory using the FileSystemObject within an ActiveX task something like this:

Function Main()

Dim objFSO
Dim objFolder
Dim objFiles
Dim strFolder
Dim datLastCreated
Dim strLastCreated

'Get folder from global variable
strFolder=DTSGlobalVariables("Folder").Value

'Create FSO objects
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFso.GetFolder(strFolder)
Set objFiles = objFolder.Files

'Loop through files collection
For Each objFile In objFiles
'Test creation date of file against current max date
If objFile.DateCreated > datLastCreated Then
datLastCreated = objFile.DateCreated
strLastCreated = objFile.Path
End If
Next

'Set global variable with full path of file
DTSGlobalVariables("FileName") = strLastCreated

'Return success
Main = DTSTaskExecResult_Success
End Function

NB. Must create the global variables, 'Folder' and 'FileName' and populate 'Folder' with the directory being searched.

Alternatively, you could construct a filename using a date string based on the date that the package was running and use the FileExists method of the FSO object to test for existence of the file.

Either way, you could then use the global variable within a Dynamic Properties task to set the DataSource property of your text connection. You could alternatively do away with the Dynamic Properties task and the output global variable and just set the property of the text connection directly within the same ActiveX script.

Mark

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-03-22 : 06:56:27

Thanks Mark.

I'll work out on this and keep posted.

Karunakaran

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources