September 18, 2008 at 1:01 pm
I have a DTS package scheduled as a job which runs everyday. It includes a script that sends the result to a flat file. How do I move ahead if I want the file name to be appended with the date on it and then it be mailed..
thanks in advance
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
September 18, 2008 at 1:54 pm
Lots of VBScripting.
Transfers are always easier to one file name. You could use the Dynamic Properties task to alter the connection to the file to a different name based on the date. Check out the VBSCript references or even look on this site of sqldts.com for samples.
For mailing, you need to have the filename, or look it up. You could do the same thing with an ExecuteSQL task if you have SQLMail enabled. OR the send mail task. Use the Dynamic Properties task to calculate the name of the file and then alter that specific task.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 18, 2008 at 2:04 pm
Basically I am using a FTP task which puts this file after it is created on the customers site. Now the issue is that they have a utility that runs the process by picking up the file which has the date in it. The step I use to FTP is a VB script..how do I make sure that if date is given date then pick this file...also will I be able to delete the old files..
thanks in advance..
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
September 18, 2008 at 2:31 pm
I have always been a fan of the YYYYMMDD append format. If you are dealing with more than one file a day, or care about when the files was created, I would add a timestamp as well in the form of HHMMSS. Thus, you can have "OUTPUTFILENAME_20080918_151312.EXT" as your flat output file, and unless your script can run more than one time per second, it will be be a valid unique date/time stamp.
For Command Prompt / Batch files, I have created the following code to create a environment variable named TimeStamp:
SET TimeStamp=%date:~10,4%%date:~4,2%%date:~7,2%_%time:~0,2%%time:~3,2%%time:~6,2%
IF "%TIME:~0,1%"==" " SET TimeStamp=%date:~10,4%%date:~4,2%%date:~7,2%_0%time:~1,1%%time:~3,2%%time:~6,2%
For VB(Script), I use the following snippet:
tmpHour = Hour(Now())
tmpMinute = Minute(Now())
tmpSecond = Second(Now())
IF len(tmpHour) < 2 THEN tmpHour = "0" & tmpHour
IF len(tmpMinute) < 2 THEN tmpMinute = "0" & tmpMinute
IF len(tmpSecond) < 2 THEN tmpSecond = "0" & tmpSecond
tmpMonth = Month(now())
tmpDay = Day(Now())
IF len(tmpMonth) < 2 THEN tmpMonth = "0" & tmpMonth
IF len(tmpDay) < 2 THEN tmpDay = "0" & tmpDay
TimeStamp = Year(now()) & tmpMonth & tmpDay & "_" & tmpHour & tmpMinute & tmpSecond
For SQL Server, I use:
DECLARE @TimeStamp varchar(17)
SET @TimeStamp = CONVERT(varchar(8), CURRENT_TIMESTAMP, 112) + '_' + REPLACE(CONVERT(varchar(8), CURRENT_TIMESTAMP, 14), ':', '')
Using whichever of these best serves your purpose, combine your filename and the appropriate variable and you have a datestamped file name which you can now pass along your code.
This should also make it easy to do file operations by date just by using the file name, since the date is now part of it.
Hope that helps,
-John
September 18, 2008 at 2:59 pm
Try this:
In your DTS package select the Dynamic Properties Task. Open the
properties for that task and click on Add. Next expand the connections
and select the output file to which you want to append the date. On the right hand side select the DataSource default value. Next select query in the dropdown box. For the connection select your SQL server
connection, then add a query such as
select 'myfile_' + substring(cast(datepart(month, getdate())+100 as char
(3)), 2, 2)+ '_'+ substring(cast(datepart(day, getdate())+100 as char(3)), 2, 2)+ '_'+ cast(datepart(year, getdate()) as char(4))
+ '.txt' as DataSource
The output filename will be myfile_12_18_2006.txt but you can modify
the date format as needed. Please read about using Dynamic Properties
Tasks.
VB Script(Adding to what John told):-
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
On Error Resume Next
Dim FileSys
set FileSys = CreateObject("Scripting.FileSystemObject")
dim tmpHour
dim tmpMinute
dim tmpSecond
tmpHour = Hour(Now())
tmpMinute = Minute(Now())
tmpSecond = Second(Now())
IF len(tmpHour) < 2 THEN tmpHour = "0" & tmpHour
IF len(tmpMinute) < 2 THEN tmpMinute = "0" & tmpMinute
IF len(tmpSecond) < 2 THEN tmpSecond = "0" & tmpSecond
tmpMonth = Month(now())
tmpDay = Day(Now())
IF len(tmpMonth) < 2 THEN tmpMonth = "0" & tmpMonth
IF len(tmpDay) < 2 THEN tmpDay = "0" & tmpDay
TimeStamp = Year(now()) & tmpMonth & tmpDay & "_" & tmpHour & tmpMinute & tmpSecond
DocFile = "C:\test"+TimeStamp+".txt"
If FileSys.FileExists(DocFile) Then FileSys.DeleteFile(DocFile)
FileSys.CreateTextFile (DocFile)
DocFile.close
Set DocFile = Nothing
Main = DTSTaskExecResult_Success
End Function
HTH
MJ
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply