Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to add a timestamp to a file generated by a DTS package? Expand / Collapse
Author
Message
Posted Friday, January 15, 2010 5:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 11, 2010 12:47 AM
Points: 21, Visits: 177
I have a DTS package generates an XML file from a SQL query. The package consists of one ActiveX script task shown below.

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()

Dim oCmd, sSQL, oDom

''' If MSXML 4.0 is not installed this will not work!
Set oDom = CreateObject("Microsoft.XMLDOM")

Set oCmd = CreateObject("ADODB.Command")
oCmd.ActiveConnection ="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<databasename>;Data Source=(local)"

sSQL = "<?xml version=""1.0"" ?>"
sSQL = sSQL & "<NorthwindOrders xmlns=""http://www.northwind.com/schemas/orders"">"
sSQL = sSQL & "<sql:query xmlns:sql=""urn:schemas-microsoft-com:xml-sql"">"
sSQL = sSQL & "select * from vewDailyReportXML for xml auto,elements"
sSQL = sSQL & "</sql:query>"
sSQL = sSQL & "</NorthwindOrders>"

oCmd.CommandText = sSQL
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"

oCmd.Properties("Output Encoding") = "utf-8"
oCmd.Properties("Output Stream") = oDom
oCmd.Execute , , 1024

oDom.Save "C:\PC\XML\testxml.xml"

Set oDom=Nothing
Set oCmd=Nothing

Main = DTSTaskExecResult_Success

End Function

I want to timestamp the filename so it's something like testxmlyyyymmdd.xml or similar. How do I go about it? I've had several failed attempts so far.



Post #848179
Posted Thursday, February 11, 2010 2:54 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 7, 2014 1:39 PM
Points: 1,275, Visits: 220
Hey Artoo,

Modify the script to set the time/date to the file name . . .
http://msdn.microsoft.com/en-us/library/t0aew7h6(VS.85).aspx
(Language Reference)
http://msdn.microsoft.com/en-us/library/d1wf56tt(VS.85).aspx
DatePart
http://msdn.microsoft.com/en-us/library/4kt42529(VS.85).aspx

i.e.
oDom.Save "C:\PC\XML\testxml-" & DatePart("yyyy",NOW) & "-" & DatePart("m",NOW) & "-" & DatePart("d",NOW) & ".xml"

I briefly tested this with WSH & msgbox . . . seems to work . . .
(I didn't test it with the XMLDOM object though)

hope this helps,

M


Free Expert Advice . . .
http://xkcd.com/627/

Mark
Post #864347
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse