Two DTS questions 1

[melbert2002]

Hello all,
I have two servers here, one is the SQL Server and the other is an FTP server. The task that I have is to create a DTS package and export a bunch of data to a CSV. Here is the trick. I need to export the data to a directory on the FTP server, everytime I try and create a job, the initial job will run correctly, dumping the csv to the ftp server on a mapped drive (N, but when the job is scheduled it will not run. I get an error that says it can not find the drive.

The second question I have is what can I do to get a Date Time Stamp to be placed on the end of the file name to look something like this: DTS_031005.csv This way I can keep a listing of every daily result.

 

[mrdenny]

First question.
Don't have it write to a mapped drive. The drive may be mapped for you, but it isn't for the login that the SQL Agent logs in as. Use the full UNC path (\\server\share\path\file.txt) to get to the file.

Second question.
You will want to use a dynamic properties task.
Add a dynamic properties task to your dts package.
open it.
Click Add.
Open Connections, Open the connection to your text file.
Open OLE DB Properties
Select Data Source
Click Value on the right, then Set at the bottom.
Change the Source to Query
Change the Connection to a SQL Server.
In the query area put in something like this.

select '\\server\share\path\to\file_' + convert(varchar(4), year(getdate())) + 
	case when len(month(getdate())) = 1 then '0' end + 
	convert(varchar(2), month(getdate())) +
	case when len(day(getdate())) = 1 then '0' end + 
	convert(varchar(2), day(getdate())) + 
 .ext'

Click OK
Click OK
Make the Dynamic Properties Task the First thing to happen in the package.

The next time the package runs the file will be saved with the date in the name.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[dbomrrsm]

Good answer MrD

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]