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

    Thanks,
    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

    [edited]

    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
    DTSpkg1.Execute
    ...

    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
    www.sqldts.com

    Madhivanan

    Failing to plan is Planning to fail

Share This Page