Naming Text File Export file

• kemo

  SSC Veteran

  Points: 296

  More actions

  June 2, 2004 at 5:32 am

  #60076

  Hi,

  I am completely new to DTS and need some help.  I am trying to create a text file using a global variable and the Dynamic Properties Task to append the date and database name to the file name.   I have created a SQL Task with the query to append the date but I do not know how to get the database name.  Also how do I then use that file name in the DTS Transformation Task instead of outputting to the file data source I created. 

  Many Thanks in advance

  K

• LeeFAR

  SSCarpal Tunnel

  Points: 4644

  More actions

  June 2, 2004 at 2:24 pm

  #508725

  One of the easiest ways of doing this is to have global variables to hold the following:

  Database Name = gvDatabase

  File Base Path = gv FileBasePath

  Destination File = DestinFile

  Create an ActiveX Script task that basically does the following:

  Function Main()

   Dim strMonth

   Dim strDate

   Dim strFileDate

   Dim iLength

   Dim i

   

   DTSGlobalVariables("gvDestinFile").Value = ""

   strDate = Date

   strMonth = Date

   iLength = Len(strDate)

   strFolder = ""

   

   Do While Len(strDate) <> 0

       If InStr(1, strDate, "/") > 0 Then

           strFileDate = strFileDate & Mid(strDate, 1, InStr(1, strDate, "/") - 1)

           strDate = Mid(strDate, 4)

       Else

           strFileDate = strFileDate & strDate

           strDate = ""

       End If

   Loop

   DTSGlobalVariables("gvDestinFile").Value = DTSGlobalVariables("gvFileBasePath").Value & "\myfile" & strFileDate & DTSGlobalVariables("gvDestinFile").Value & ".txt"

   Main = DTSTaskExecResult_Success

  End Function

  This basically gets you a global variable that can then be used to set your Destination object's path using the Dynamic Properties task. 

   Lee