Post reply

SSIS Variable - changing time portion on date stamp

  • December 17, 2007 at 12:42 pm

    #181379

    I have created a variable to identify a time stamp on my SSIS package for a file I am importing via the ftp task in SSIS but have encountered a problem where the last number in the time portion of the date/time-stamp can be one of three numbers-0,1, or 2.

    My time-stamp variable looks like the following:

    "GradebookItems" + "_"+(DT_WSTR,4)YEAR(GETDATE()) + "-"+RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + "-"+RIGHT("0" + (DT_WSTR,2)DAY(GETDATE()), 2) + "_12-00-00.txt"

    The number can either be "_12-00-00.txt", "_12-00-01.txt", or "_12-00-02.txt"

    Can anyone help with this?? I think I am at my wits end! If you can see better way to do what I have defined here, I welcome some critiquing!

    TIA~

    Tena

  • December 17, 2007 at 1:39 pm

    #760817

    I guess I asked a tough one, huh?

  • December 17, 2007 at 1:40 pm

    #760818

    You could use a script task in SSIS as a replacement for the generic FTP task which will give you more flexibility. i.e. use mget with a wildcard ("_12-00-*.txt") and iterate through the files within in a ForEach loop. You can refer to this post for more info on the FTP script task.

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1258083&SiteID=1

    Tommy

    Follow @sqlscribe

  • December 18, 2007 at 5:27 am

    #760952

    Thanks very much for the suggestion, but I am not familiar with scripting at all...:(

    Is there just a way to make the last part of the file a wildcard?

    TIA~

    Tena

  • December 18, 2007 at 7:45 am

    #761026

    An alternative approach would be to use an execute SQL task to build an FTP command file using xp_cmdshell - i.e.

    DECLARE @FileName varchar(100)

    DECLARE @Cmd1 varchar(1000)

    DECLARE @Cmd2 varchar(1000)

    SET @FileName = 'mget /GradeBookItems_' + LEFT(REPLACE(CONVERT(CHAR(8), GetDate()-1, 10), '-', SPACE(0)),4) + '*.TXT'

    SELECT @Cmd1 = 'echo lcd C:\TEMP\MYLOCALWORKINGDIR>C:\TEMP\MYFTPCOMMANDFILE.txt'

    EXEC master..xp_cmdshell @Cmd1

    SELECT @Cmd2 = 'echo ' + @FileName + '>>C:\TEMP\MYFTPCOMMANDFILE.txt'

    EXEC master..xp_cmdshell @Cmd2

    Then use a execute process command task and call http://ftp.exe (%SYSTEMROOT%\System32\ftp.exe) using the command file created in the execute SQL task. FTP command help available via http://www.nsftools.com/tips/MSFTP.htm

    Good luck 🙂

    Tommy

    Follow @sqlscribe

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply