Dynamic File Name and compression in DTS
-
Hi
I am pumping data from table into a text file using DTS but I want a file name along with date. For example myData_06252005. I know it can be done using ActiveX Script but I don't know how? I also want to zip the file . Any help will be appreciated.
Thanks
-
Khalid,
-Add a global variable to your package called OutputFilePath with type as string
-Add a Dynamic Properties task to your package and set the "Data Source" property of your destination data connection (your text file) to the global variable
-Add an ActiveX script task before your import and add a bit of code to set the destination file path e.g. something like
Function Main()
DTSGlobalVariables("OutputFilePath").Value = "C:\MyOutputFile_" & Replace(FormatDateTime(Now() , 2 ), "\", "") & ".txt"
Main = DTSTaskExecResult_Success
End Function
-This will export the data to the file name specified in the code
-You have a couple of options if you then want to zip the resulting file. You can either get the command line executable for Winzip or write your own dotnet component to use the free library csharpziplib and then use either an Execute Process task or an ActiveX script task to kick off either program with the relevant command line parameters read from your global variable.
Hope this helps
-
noggin is making a very good answer. I would definitly do as he suggest.
With one small modification, to use a free ZIP compression algorithm you do not have to bake your own code, it's enough to use one of the free command line ZIP applications out there (which you can use in the same way as the pay-to-use program WINZIP).
-
Hi,
I have a DTS package that works, and now I want to add the date to the output file name.
Where, exactly, does this ActiveX script go?
I tried adding it to the Workflow between the Source and Destination connections:
-inside Workflow Properties, I selected Use ActiveX script, then,
-in that Properties box, inserted the code to what was already there.
The package still executed, but the date was not added to the name.
Here's the code:
***********************************************
' Visual Basic ActiveX Script
'***********************************************
Function Main()
Main = DTSStepScriptResult_ExecuteTask
DTSGlobalVariables("OutputFilePath").Value = "C:\Output\ATB_" & Replace(FormatDateTime(Now() , 2 ), "\", "") & ".txt"
Main = DTSTaskExecResult_Success
End Function[/font]
----------------------------------------------------------------
I would really appreciate some help with this .
Thanks,
Irene
-
I am a newbie to this just trying solve a similar issue
Iwould appear to me that the "\" character would be misinterpreted
as part of a pathway - illegal char for a filename
try
"C:\Neil\MyOutputFile_" & year(date) & month(date) & _
day(date) & hour(now) & minute(now) & second(now) & ".txt"
Regards
Neil Douglas
-
I'm trying to do this exact same thing. I've tried adding the active x into the workflow properties between the connections; have also tried putting the dynamic properties and active x script after the output file creation. I can't get it to rename the file, although it keeps telling me all steps completed successfully.
-
Were you able to get this to work? or find an alternative solution?
¤ §unshine ¤
-
No 🙁 I still can't get it to work
-
I haven't figured out global variables yet, but here is a solution that works for me.
The code is in an ActiveX Script task that is the FINAL step in the package.
It changes the file name from BD.txt to something like BD_20080724_061523.txt (you can skip the seconds if you don't need to be that precise).
-Irene
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Set Fso = CreateObject("Scripting.FileSystemObject")
Dim aYear, aMonth, aDay, aDate, aHour, aMin, aTime, aSec
' Format date portion of string......
aYear = Year(Now())
aMonth = Month(Now())
if len(aMonth) = 1 then
aMonth = "0" & aMonth
end if
aDay = Day(Now())
if len(aDay) = 1 then
aDay = "0" & aDay
end if
aDate = aYear & aMonth & aDay
' Format time portion of string......
aHour = Hour(Now())
if len(aHour) = 1 then
aHour = "0" & aHour
end if
aMin = Minute(Now())
if len(aMin) = 1 then
aMin = "0" & aMin
end if
aSec = Second(Now())
if len(aSec) = 1 then
aSec = "0" & aSec
end if
aTime = aHour & aMin & aSec
CopyToSpec = "c:\Output\BD_" & aDate & "_" & aTime & ".txt"
CopyFromSpec = "c:\Output\BD.txt"
Fso.CopyFile CopyFromSpec, CopyToSpec
Fso.DeleteFile CopyFromSpec
Main = DTSTaskExecResult_Success
End Function
-
Great! Thanks!
¤ §unshine ¤
-
Chaps
Happened across this post whilst looking for some stuff and thought an example of mine from below might help.
From looking at the code in the first solution it looks like a type in that the poster has used the "\" character rather than the "/" character.
I'm sure you've moved on from this now but just in case below is a screen shot that might help you:
Cheers
Bobby
-
Bobby,
Thanks for those screen shots! I'm getting a little closer to understanding the use of Global Variables, and hoping you can help me with this:
I need to produce an Excel file with 3 worksheets, each holding the results of a different query. Would you have any screen shots for this?
Thanks,
Irene
-
HI Ten centuries,
Your method worked nicely to me.
Thanks man.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply