September 15, 2005 at 11:05 am
I have a need to extract datafiles using DTS to a directory on my network where, for each day, a new directory and a new file would be created ie (dir 09152005 file 09152005.txt)automatically to store the files for archiving purposes. Is there a way to do this? Even better, is there a way to pass the newly created directory and file to an ftp function to automatically have this sent?
September 15, 2005 at 12:16 pm
If the directory is on your network, do you need FTP? Unless I misunderstand, you could just use the NT-shell 'COPY' command and reference the target folder as a share.
Who is going to create those new folders and files for you? If this gets too fancy, my recommendation would be to do some of these tasks outside of T-SQL, in something like C# or VBScript or even NT Shell. If you must drive the operations from T-SQL, then you might simply consider using 'xp_cmdshell' with NT Shell commands to create them. Anytime you need feedback from an NT shell command within a T-SQL batch or procedure, you can use the output of 'xp_cmdshell' to populate a temp table, e.g.
CREATE TABLE #response
(seq INT IDENTITY NOT NULL
, detail VARCHAR (8000) NULL)
DECLARE @cmd VARCHAR (8000)
SELECT @cmd = 'DIR /AD /B {root folder}'
INSERT INTO #response (detail)
EXEC master..xp_cmdshell @cmd
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply