Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Generate a CVS file with timestamp as filename 2

Status
Not open for further replies.

Jeet2004

MIS
Jun 29, 2005
96
US
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
 
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
 
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)
[noevil]
 
mrdenny could you please elaborate on the above.
Thanks.
 
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)
[noevil]
 
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?
 
Like this perhaps:

Code:
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]
 
Never thought of that, I could just add that to a sp.

Thanks man!
 
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.

Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top