SQL Server Performance Forum – Threads Archive
DTS and Dynamic FilenameHi 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,
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
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.
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
Thank you for the feeback, I didn’t know I can set even destination file name using dynamic properties task.
See if you find such scripts here
www.sqldts.com Madhivanan Failing to plan is Planning to fail