error creating text file with dynamic properties in DTS pkg

  • 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.

     Anyone every have this problem before?

     

    Thanks

    David

  • 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

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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

  • 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.

  • 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.

  • Go to the site below:

    http://www.sqldts.com

    there are various articles and samples that will help.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply