SQL Server Performance

DTS and Dynamic Filename

Discussion in 'SQL Server DTS-Related Questions' started by Jon M, Feb 7, 2006.

  1. Jon M Member

    Hi Everyone,
    I have a DTS that transforms data and this is going to be a daily thing.

    How can i dynamically create the destination file name? For example, i want to name it as '20060207.txt' (YYYYMMDD.txt format).

    Jon M
  2. mmarovic Active Member

    I am not aware of solution using just dts designer and executing package from there or by dtsRun. However, I believe it is possible to change output file name from vb if you include dts related library (I don't remember the name).

    You may find a solution from links posted by Satya:http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=2262


    I've found libraries:
    ...Microsoft SQL Server80ToolsBinncusttask.dll#Microsoft DTS Custom Tasks Object Library
    ...Microsoft SQL Server80ToolsBinndtspkg.dll#Microsoft DTSPackage Object Library
  3. mmarovic Active Member

    Here is the piece of code I found somewhere:

    Dim DTSpkg1 As DTS.Package

    Set DTSpkg1 = New DTS.Package
    DTSpkg1.LoadFromSQLServer g_strServer, , , DTSSQLStgFlag_UseTrustedConnection,, , , "loadSomething"
    DTSpkg1.Connections("Microsoft Excel 8.0").DataSource = strPath + "dataexcelFileName.xls"
    DTSpkg1.FailOnError = True

    Hope it helps.
  4. Jon M Member

    I didn't not find what i'm looking for, thanks for the replies though.<br /><br />Anyway, I was able to dynamically create the destination file name using Dynamic Properties Task that is by setting the DataSource as below Query:<br /><br />select convert(char(<img src='/community/emoticons/emotion-11.gif' alt='8)' />,getdate(),112) +'.txt'<br /><br /><br />Jon M
  5. mmarovic Active Member

    Thank you for the feeback, I didn't know I can set even destination file name using dynamic properties task.
  6. Madhivanan Moderator

    See if you find such scripts here


    Failing to plan is Planning to fail

Share This Page