Hi,

I have a query regarding customization of data
transformation using DTS. I have created a table in SQL
Server 2000, in which data have to be imported from an
excel file. I have a folder containing number of excel
files. The problem is that, i have to import only that
file which has recently been created/modified. DTS will
trigger the import every half an hour and take the latest
created/modified file and copy all the contents into the
same table each time.

Can you please suggest me how it can be done. Your
response will highly be appreciated.

Thanks & Regards,
Pratyush Kumar.

Loop through the files using the FSO (File System Object)
Create a variable to hold file names
Grab the .DateLastModified and compare against the current variable
replacing as needs be.


Another way to do this is to archive your files once you've finished with
them and you latest file will be the only one in the directory



--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Pratyush Kumar" <pratyushk (AT) ggn (DOT) hcltech.com> wrote

Hi Allan,

Thanks for the quick response. As you suggested, i am
trying to implement the following code -

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

Function Main()
Dim lobjFileObject
Dim lobjFolder
Dim lobjFiles
Dim ldtLastModiefied

set lobjFileObject = CreateObject
("Scripting.FileSystemObject")
Set lobjFolder = lobjFileObject.GetFolder("\\odc-
ashish\Code")
Set lobjFiles = lobjFolder.Files

For Each File In lobjFiles
ldtLastModiefied = FileDateTime("\\odc-ashish\Code\" &
File.Name)
If DateValue(ldtLastModiefied) >= DateValue(Now) Then
'create new connection to source Excel file
'Create new connection to table in SQL Server
'Dump data from source file into table
End If
Next

Main = DTSTaskExecResult_Success
End Function

Is this correct?

Regards
Pratyush

You loop through the files - good
You pick up the file.
First file Store it's name and DateLastModified properties in variables
Subsequent files - compare time variable to this file's
DateLastModified.
If this is a newer file replace the variables with this file's
properties.
Continue comparisons


--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


<anonymous (AT) discussions (DOT) microsoft.com> wrote