Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2006
    Posts
    3

    Unanswered: Using DTS .. Dynamic Dest Filename

    I need to use DTS to create a dynamic destination filename based on the value of a field. I tried creating a DTS package using the wizard. It works fine if I wanted a static filename for the destination filename. I tried creating a Dynamic Properties Task by selecting Connection2/OLEDB Properties/Data Source and then using a SQL Statement to return a value for the name of the file. It doesn't seem to run the query everytime the DTS is ran. The first time I run it it names the file correctly but after that it keeps naming the file the old value from the SQL statement. Any idea what I need to do to fix this?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why do you need to use DTS?

    Can't you use bcp?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2006
    Posts
    3
    Will using BCP allow me to copy data from a table in the database and write to a file Everyday at a specific time?
    Last edited by dndaughtery; 01-30-06 at 14:34.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ABS(
    Quote Originally Posted by dndaughtery
    Will using BCP allow me to copy data from a table in the database and write to a file Everyday at a specific time?
    )

    Code:
    USE Northwind
    GO
    
    DECLARE @cmd varchar(8000), @fn varchar(8000)
    
    SELECT @fn = 'myTestFile_'
    	+REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(varchar(25),GetDate(),121),'-','_'),':','_'),' ','_'),'.','_')
    SELECT @fn
    
    SELECT @cmd = 'bcp Northwind.dbo.Orders out d:\'+@fn+' -c -S' + @@SERVERNAME + ' -T'
    
    EXEC master..xp_cmdshell @cmd
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2006
    Posts
    3
    Will this run everyday without me needing to manually run it? Could you comment the code...I'm new to SQL so I didn't quite understand the code.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    All you have to do is cut and paste the code in the Query Analyzer and execute it...you find the file on the serveres C:\ drive

    Also, for you to schedule it, you need to create a scheduled job in sql server

    Do you know what Enterprise Manager, Query Analyzer and Books on line are?

    These are the client tools that you should have installed.

    Since you mention DTS, it sounds like you should have them.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •