Use DTS to export data to diferent named text files
-
How I can use DTS to export data to a diferent named text files. It always export to the same file but I need diferent files. Is it possible with DTS? If not, exist other way to do it?
-
Hai,
If you create a DTS package with a destination file then it always exports to that file only. Why don't you try with BCP. Have a variable to construct the file name(use datetime value) and try exporting. If you don't require any data transformation you may go for BCP. Also consider BULK INSERT which is the fastest.
declare @BackupFolder varchar(100)
declare @BCPOutCommand varchar(500)
declare @BackupFileName varchar(50)
/* Set BackupFolder name here */
set @BackupFolder = 'D:\Testing\BCP\'
select @BackupFileName = data+substring(getdate(),13,5)
+ '.txt'/* Combine backup folder name and file name */
select @BackupFolderFile = @BackupFolder + @BackupFileName
select @BCPOutCommand = 'bcp ' + '"DBA_DB.dbo.' + @TableName +
'" out "' + ltrim(rtrim(@BackupFolderFile)) +
'" -c -q -S' + @@Servername + ' -T'
print @BCPOutCommand
exec master..xp_cmdshell @BCPOutCommand
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me -
A script will do. Call the script in DTS.
Following sample rename aa.txt to a random generated name.
dim fso, sourcefilename, tempfile
set fso=createobject("Scripting.Filesystemobject")sourcefilename="c:\temp\aa.txt"
tempfile=fso.gettempname
fso.copyfile sourcefilename, tempfile
wscript.echo tempfile
-
Take a look at the Dynamic Properties task in DTS, which can set the file name based on a global variable, for example.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply