How to add a timestamp to a file generated by a DTS package?

• artoo

  SSC-Addicted

  Points: 445

  More actions

  January 15, 2010 at 5:11 am

  #215632

  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.

• mark.pleasance

  SSCrazy

  Points: 2070

  More actions

  February 11, 2010 at 2:54 pm

  #1117795

  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