DTS Export to Flat File with DateTime in Filename

[RLTE]

I have a DTS that connects to a SQL db and then executes a stored procedure and saves the output into a flat file. This DTS runs daily and thus overwrites the file each day. My goal is to have the datetime be a part of the filename going forward. I have seen all kinds of TK's out there about creating an ActiveX Script Task or a Dynamic Properties Task. Before I started adding in all of this the DTS is fine.

I now have both the Active X and the Dynamic Properties Task created and running prior to the Exec of the sp or the Transform Data Task.

Here is the script on the Active X

Option Explicit

Function Main()
Dim oConn, sFilename

' Filename format - List_t00024458mai_20080519.txt
sFilename = "List_t00024458mai_" & Month(Now) & Day(Now) & Year(Now) & Hour(Time) & Minute(Time) & Second(Time) & ".txt"
DTSGlobalVariables("fileName").Value = "E:\GPShare\Integrations\Concur\" & sFilename

Set oConn = DTSGlobalVariables.Parent.Connections("Text File [Destination]")
oConn.DataSource = DTSGlobalVariables("fileName")

Set oConn = Nothing

Main = DTSTaskExecResult_Success
End Function



The scritp bombs out on the "Text File [Destination]" saying that the DTS Connection was not found. This is when I execut the DTS or just the Active X.

The Dynamic Properties Task is:

Destination Property: Value
Source Type: Global Variable
Source Value: fileName

The default value of the fileName is: E:\GPShare\Integrations\Concur\List_t00024458mai_5192008221725.txt


I am at a loss and only have experience DTS once, not repeatedly with different file names. Have never had to use activex or anything. Please help! I am on a deadline and am freaking out.

 

[BlackburnKL]

RLTE,

I noticed that nobody had responded yet, and while my solution is not exactly what you requested, it is close enough that I thought it might be helpful in meeting your deadline.

I use a DTS with an ActiveX script to rename an import text file to include the date when it was imported:

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

Function Main()
Dim oFSO, sFileName
Dim FileDate
Dim DateString
Dim File

sFileName = "H:\SHARED\BCMDSD\bcm_sales.txt"
Set oFSO = CreateObject("Scripting.FileSystemObject")
If oFSO.FileExists(sFileName) Then
FileDate = Date
DateString = DatePart("yyyy", FileDate) & Right("0" & DatePart("m", FileDate), 2) & Right("0" & DatePart("d", FileDate), 2)
Set File = oFSO.GetFile(sFileName)
File.Name = "bcm_sales_" & DateString & ".txt"
Main = DTSTaskExecResult_Success
Else
Main = DTSTTaskExecResult_Failure
End IF
Set oFSO = Nothing
End Function

In my example, the file H:\SHARED\BCMDSD\bcm_sales.txt would be renamed H:\SHARED\BCMDSD\bcm_sales_20080520.txt if it were imported today.

My ActiveX script renames an existing file (which isn't what you were after), but it does allow you to add the date to an existing file. Maybe you can leverage that into what you need.

BlackburnKL

 

[RLTE]

Thansks. This was out on another website and worked wonderful. Hopes it helps someone.

Create package, one connection -- the source db. Create an execute SQL task with the query against the data source. After typing in the query, click Parameters, Output Parameters. Check rowset value. Click "Create global variable" Create an output parameter (I've called mine rs in the following example) give it a type of "other." It will become a type "Dispatch" after first run. Click "OK" until you've closed the SQL Task.

Create an ActiveX Script task. Here's the code for it (generates a file named ccyymmdd.csv):

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

Function Main()
Dim dt
Dim thisFile

Dim Pkg
Dim rs
Dim fso
Dim tst

dt = fixDate(Date())
thisFile = "\\pathToFile\" & dt & ".csv"

Set Pkg = DTSGlobalVariables.Parent
Set rs = DTSGlobalVariables("rs").value

Set fso = CreateObject("Scripting.FileSystemObject")

Set tst = fso.openTextFile(thisFile, 2, true)
tst.write rs.GetString(2, , ",", vbCrLf, "")
tst.close

Set tst = Nothing
Set fso = nothing

Set rs = Nothing

Main = DTSTaskExecResult_Success
End Function


Function fixDate(dt)
Dim yy, mm, dd
dim jDate
dim arDate

jDate = cstr(datevalue(dt))
arDate = split(jDate,"/")

mm = arDate(0)
dd = arDate(1)
yy = arDate(2)

if len(yy) = 2 then
yy = "20" + yy
end if

if len(mm) = 1 then
mm = "0" & mm
end if
if len(dd) = 1 then
dd = "0" & dd
end if

fixDate = yy & mm & dd
End Function


Use workflow properties to ensure the Execute SQL Task completes successfully b4 starting the ActiveX task