Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Mar 2008
    Posts
    13

    Unanswered: Naming a DTS file

    Can I schedule a SQL Server 2000 DTS package to run assigning it a name with the date that it ran? (i.e. DTSPackage_20080925)?
    Last edited by ChrysW; 09-25-08 at 11:31.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Uhm......why?
    Are you creating a different DTS package for each date? Are you saving DTS packages that only need to be run once?
    You can name your DTS package anything you want, of course, as long as it is a valid file name.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2008
    Posts
    13
    I'm sorry, I wasn't very clear with my question. I've created a DTS package that exports data to a txt file. This file will be FTP'd to a vendor and they require the date of the export file to be included in the name. This file is scheduled to run on a weekly basis. Is there any way to save the file with the date as part of the file name without having to manually enter the date?

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6

    Post

    easy. you can have your DTS point to static file name like C:\export.txt.

    Add another step to your job or script or whatever that does a little something like...

    DECLARE @copystring varchar(500)

    SET @copystring = 'copy C:\export.txt C:\export' + CAST(GETDATE() as VARCHAR(20)) + '.txt'

    xp_cmdshell @copystring

    Details may vary.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You could also declare the filename as a variable in your DTS package and create is on the fly with the date concatenation.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    or create an ActiveX script with date in a variable
    Something like this, yeh I know it needs some cleaning.

    ************************************************** ***************
    ' Visual Basic ActiveX Script
    '************************************************* ***********************
    Dim bValidDate
    Function Main()
    bValidDate = False
    DTSGlobalVariables("FileName").Value = GetTodaysFile
    DTSGlobalVariables("BaseFileName").Value = GetTodaysBaseFile

    if (bValidDate = True) Then
    Main = DTSTaskExecResult_Success
    Else
    Main = DTSTaskExecResult_Failure
    End If
    End Function

    Function GetTodaysFile()
    'Assume that BusinessDate is YYYYMMDD format
    dim sPath
    sPath = "\\sharefs\shared\fmc\MicroHedge\SW_Positions2 MH_"
    GetTodaysFile = sPath & GetDate & ".txt"
    End Function

    Function GetTodaysBaseFile()
    'Assume that BusinessDate is YYYYMMDD format
    dim bFile
    bFile = "SW_Positions2MH_"
    GetTodaysBaseFile = bFile & GetDate & ".txt"
    End Function

    Function GetDate()
    Dim sDate

    If IsDate( DTSGlobalVariables("CurrentDate").Value ) Then
    sDate = DTSGlobalVariables("CurrentDate").Value
    bValidDate = True
    GetDate = FormatDates(Year( sDate )) & "." & FormatDates(Month(sDate) ) & "." & FormatDates(Day( sDate ))
    Else
    bValidDate = False
    End If
    End Function

    Function FormatDates(sValue)
    if ( sValue < 10) then
    FormatDates = "0" & sValue
    else
    FormatDates = sValue
    End If
    End Function

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    yeah.

    after and before getting burned by the 2000 to 2005 transition, I do as little of the work in DTS/SSIS as possible. Really I only need it for writing files and occasionally importing them.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by Thrasymachus
    yeah.

    after and before getting burned by the 2000 to 2005 transition, I do as little of the work in DTS/SSIS as possible. Really I only need it for writing files and occasionally importing them.

    I got burned by "Parameter sniffing". I still refuse to learn SSIS and just use DTS and then import the package(s) to 2005. I'm such a lazy bastard.

  9. #9
    Join Date
    Mar 2008
    Posts
    13
    I'll give these a try. Thanks
    Last edited by ChrysW; 09-25-08 at 17:27.

  10. #10
    Join Date
    Mar 2008
    Posts
    13
    Thrasymachus~

    When you say add another step, what do you mean? Where/how do I add the step.


    blindman~

    Can you tell me how to declare the filename as a variable and have it save to that filename?

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Follow blindman's advice. When converting to 2K5 you'll have to revisit it, but it's a quick revisit which will retain the concept.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by ChrysW
    Thrasymachus~

    When you say add another step, what do you mean? Where/how do I add the step.


    blindman~

    Can you tell me how to declare the filename as a variable and have it save to that filename?
    I assume you are running a job. jobs have steps.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by ChrysW
    blindman~

    Can you tell me how to declare the filename as a variable and have it save to that filename?
    I've only had to do this two or three times. I have an example of it on a system at another client, but I will not be back there until Tuesday.
    If nobody has answered you by then, or you haven't found it in Books Online or by googling, pop me a reminder on Tuesday to get it for you.
    It is not difficult, just not intuitive.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Mar 2008
    Posts
    13
    Hi blindman~

    Just wanted to send you a reminder for this. If you can send me what you have please.

    I truly appreciate everyones help.
    Chrys

  15. #15
    Join Date
    Mar 2008
    Posts
    13
    Quote Originally Posted by Thrasymachus
    easy. you can have your DTS point to static file name like C:\export.txt.

    Add another step to your job or script or whatever that does a little something like...

    DECLARE @copystring varchar(500)

    SET @copystring = 'copy C:\export.txt C:\export' + CAST(GETDATE() as VARCHAR(20)) + '.txt'

    xp_cmdshell @copystring

    Details may vary.

    This is not working for me. I receive the following error:

    The system cannot find the drive specified.
    NULL

Posting Permissions

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