November 8, 2004 at 2:09 pm
I need to create a cvs text file were the name of the file is based on the date. I am trying to accomplish this with a DTS package that has the dynamic properties task. I pass the new file name/path as a parameter for the text file connection in the "test file destination" task. I get an error saying that the file can not be found. This is the file I am trying to create.
I can run the package with the default values in the variables with out error. I have double checked the path and it is a valid path.
November 8, 2004 at 4:04 pm
I've had this and did not find a straightforward solution, so in the end I worked round it. But, as an alternative to having a dynamic filename, you might think about output to a fixed filename and then chaining to another task (VB script?) that does a file rename to whatever you want.
Phil
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 9, 2004 at 2:15 am
I have used an Activex script task to handle the dynamic filename using the script below
Function Main()
Dim oConn, sFilename
' Filename format
sFilename = "TEXTFILE"
If Day(Now())<10 Then sFilename = sFilename & _
"0" & Day(Now()) Else sFilename = sFilename & Day(Now())
If Month(Now()) < 10 Then sFilename = sFilename & "0" & _
Month(Now()) Else sFilename = sFilename & Month(Now())
sFilename= sFilename & Year(Now())
sFilename = DTSGlobalVariables("TextFilePath").Value & _
sFilename & ".csv"
Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Source)")
oConn.DataSource = sFilename
Set oConn = Nothing
on error goto 0
Main = DTSTaskExecResult_Success
End Function
This supplies a filename to the Source.
Regards
Bolo
November 9, 2004 at 7:18 am
When you write the code producing the name of the file, the file actually has to exist, or you get the file not found error.
You could probably get around this by working in a disconnected edit mode, as I think this is the kind of thing it's designed for, but I don't know that from first-hand experience.
November 9, 2004 at 10:57 am
From what I am seeing the ActiveX script would be a good one if the DTS package was an ActiveX package. But, this is a SQL server package.
I looked at the disconnected edit mode. But I do not see a way to use this with the dynamic properties. Does anyone know of a way to use these together?
For now I will build the file and then rename it from the command line using xp_cmdshell.
November 10, 2004 at 2:54 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply