June 28, 2005 at 11:57 pm
Hi,
Currently we are using DTS to transfer data from a text file to a DB table.
After moving the data, we need to copy the text file content to another backup file with extension say yyyymmddhhmmss.txt (For backup purpose).
That is, after importing the data from text file, need to backup the file with different name and differnt location and remove the source text file.
This we need to do it in a stored proc
Can any one please help
Thanks
-Kumar
June 29, 2005 at 2:28 am
Why you don't use the DTS Task?
June 29, 2005 at 2:49 am
Hi Deni,
Since we are having a Stored Procedure which will do all the Import process.
That is basically we are copying all the content to the Staging table using a DTS.
After that based on the business logic we are transferring the data to differernt tables to the DB.
Once this processs is done, we need to move the source file to a backup folder with a backup (say yyyymmddhhmmss) name and clear out the Source Text file.
We are doing all the above process in a stroed proc.
I hope this will help you to understand the problem.
Thanks for your reply
-Kumar
June 30, 2005 at 2:31 am
Hi,
You can use the xp_cmdshell stored procedure to achieve this. For example:
Declare
@sExecSQL NVARCHAR(4000),
@sFileLocation_Source NVARCHAR(255),
@sFileLocation_Target NVARCHAR(255)
SET @sFileLocation_Source = 'C:\Download\XXXXX.txt'
SET @sFileLocation_Target = 'C:\Archive\YYYYY.txt'
--Copy File
SET @sExecSQL = 'Copy ' + LTRIM(RTRIM(@sFileLocation_Source)) + ' ' + LTRIM(RTRIM(@sFileLocation_Target))
EXEC Master..xp_cmdshell @sExecSQL
--Delete Source File
SET @sExecSQL = 'DEL ' + @sFileLocation_Source
EXEC Master..xp_cmdshell @sExecSQL
Hope this helps
June 30, 2005 at 9:08 am
You could also use the FileSystemObject from within the DTS
July 6, 2005 at 9:07 pm
Hi sameer,
It works fine. Thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply