Hi all,

I am trying to use DTS Package to export some table result to an EXCEL
output file. After that, I would like to copy that file from the original
location to another directory with a different file name by adding the
current date to it. (Filename YYYY-MM-DD.xls).

Here is what I have done.
1) Select SQL 2000 | Import and Export Data menu item.
2) Click Next on the DTS Import/Export Wizard.
3) Select Microsoft OLE DB Provider for SQL Server as the data source with
the proper server and database.
4) Select Microsoft Excel 97-2000 as the destination and put
c:\temp\InFile.xls at the filename field.
5) Use a query to specify the data to transfer. I specify simple select
statement.
6) Click Next on the Tables and Views screen. I left it as it’s.
7) Schedule DTS for later execution and save the DTS Package to SQL server.
8) Give the DTS Package a name and it finished with no error after executing
the package.

Now how can I copy the file from c:\temp\InFile.xls to c:\MyDir\OutFile
2006-5-18.xls?

In addition, if I don’t delete InFile.xls from c:\temp, multiple runs of DTS
Package don’t overwrite the content of the file. Instead, it appends new
result to the end of the original file. Is this by design?

Lastly, if I run the DTS Package manually from Enterprise Manager | Data
Transformation Services | Local Packages, the Create Table Results Step
always fail. The error message is “Table Results already exists”. How could
I fix this error? It seems able to export the data however.

I am very new to DTS Package. I did some search on MSDN and it talks about
how to generate DTS Package from the Data Transformation Services. But I
can't find a good KB document to address this simple task.

Could you please help?

Thanks.

Abel Chan

Abel,

You could use a batch file in an Execute Process Task to copy the file and
rename it or use the FileSytem object in aVBScript in an ActiveX Script Task
to do the same.

HTH

Jerry
"Abel Chan" <awong (AT) newsgroup (DOT) nospam> wrote

Put this at the end of your DTS to copy the file to an OutFile with the
date, and then delete the InFile

DECLARE @InFile varchar(50)
DECLARE @OutFile varchar(50)
DECLARE @SHELL varchar(2000)

SET @InFile = 'c:\temp\InFile.xls'

SET @OutFile = 'c:\MyDir\OutFile_' + CONVERT (VARCHAR,
CONVERT(DATETIME, GETDATE(), 112) ,112) + '.xls'

SET @SHELL = 'master.dbo.xp_cmdshell ''copy "' + @InFile + '" "' +
@OutFile + ''''
PRINT @SHELL
EXEC(@SHELL)

SET @SHELL = 'master.dbo.xp_cmdshell ''del ' + @InFile + ''''
PRINT @SHELL
EXEC(@SHELL)

Hello Abel,


Have a look at the last step (6) in this package

Looping, Importing and Archiving
(http://www.sqldts.com/default.aspx?246)


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com