How to get last written date on txt file to SQL table after a BULK INSERT

[wz]

Part of My Code in SQL Server Mgmt Studio as listed below. On the last line I want to get the modified date on the txt file that is used in the bulk insert and place into a column that I just created "UpdateFileDate". Any help on the code I would use? I have end users who creating a txt file and then I run a report in SSRS off of the data but I would like to know when the txt file was last modified.

USE ExcelWorksheets;
DROP TABLE dbo.MaterialPlant1;

CREATE TABLE dbo.MaterialPlant1
(
Material INT NOT NULL,
MaterialDescription NVARCHAR(50),
StorLoc INT,
StgeType INT,
StorageBin NVARCHAR(50),
Batch NVARCHAR(50),
Restricted NVARCHAR(50),
StockCat NVARCHAR(50),

);

BULK INSERT ExcelWorksheets.dbo.MaterialPlant1
FROM 'C:\TEMP\WorksheetPlant1.txt'

WITH
(
ORDER (Material ASC)
);

ALTER TABLE dbo.MaterialPlant1 ADD UpdateFileDate DATETIME

 

[philhege]

Look at the CMDEXEC step type. I typically pipe a DIR command to a text file, then upload that to a temp table. There you can parse through the directory info and determine the modified date.

-----------
With business clients like mine, you'd be better off herding cats.