Generate a CVS file with timestamp as filename 2

[Jeet2004]

It might be really stupid question but as i dont know how to do it its really difficult for me.
Here's what i need to do
I need to create a dts package to get data from tables using query and write it to a file (.csv file).

I am able to do till here and now struggling with

1. how can i make sure that everytime the file is written to the folder its named "Test" + datetimestamp + ".CSV" eg

Test12012005054530.csv

Dont know how can i do this

 

[Jeet2004]

Also i need to post this file on a ftp site which is password protected How can i do this in a DTS package that its gets posted out there
Thanks for you help in advance

 

[mrdenny]

You can use the Dyanmic Properties Task to change the file name of the destination object to the current date each time the package runs.

You can script ftp commands from the command line. Do a google search for examples.

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com

 

[Jeet2004]

mrdenny could you please elaborate on the above.
Thanks.

 

[mrdenny]

By adding a Dyanamic Properties Task at the beginning of the package, you can dynamicly reset the file name that you will be exporting the data to.

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com

 

[rstum2005]

How would you dynamicly reset the file name? I have looked for a tutorial or website to explain how to do this and have not found anything as of yet?

 

[EBGreen]

Like this perhaps:

DECLARE @FileName varchar(50)

SET @FileName = REPLACE('c:\authors_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')

PRINT @FileName

--Results--

c:\authors_06-30-01.txt

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[rstum2005]

Never thought of that, I could just add that to a sp.

Thanks man!

 

[Eyespi20]

To set a dynamic properties, click on the dynamic properties icon, expand the tree under the connection to your csv file, go to DATA SOURCE and change to query, then type in your query similar to this (be sure to include the FULL path to the ftp server as mapped drives don't work.

select '\\ttc-appserver1\ftproot\FSRs\DataEntryReviewReport\DEReview_' + cast(datepart(mm, getdate()) AS varchar(2)) 
+ cast(datepart(dd, getdate()) as Varchar(2)) + 
cast(datepart(yyyy, getdate()) as Varchar(4))  + '.csv'

Margaret

 

[rstum2005]

I actually used this tutorial and got what I needed done.

http://www.databasejournal.com/features/mssql/article.php/2225481