Post reply

Adding today's date to the file name of a .CSV everyday

  • September 29, 2008 at 11:10 am

    #198668

    I would like to add the date (not the time) to a .CSV file name produced using SSIS package. In this case it will be run daily, so every day there will be a new file name with the current date. Does anyone know if there is a step by step process I can follow to do this?

    example:

    MyNewFile29092008.csv

    MyNewFile28092008.csv

    MyNewFile27092008.csv

    thanks

  • September 29, 2008 at 12:11 pm

    #878546

    Is this what you are searching for?

    SELECT REPLACE(CONVERT(VARCHAR(15), GETDATE(),101),'/','')

    For today this returns 09292008

    or this

    SELECT REPLACE(CONVERT(VARCHAR(15), GETDATE(),104),'.','')

    For today this returns 29092008

    or this (Thanks to Jeff Moden for reminding me of the DATEPART function)

    SELECT CAST(DATEPART(dd,GETDATE()) AS VARCHAR(4)) + RIGHT('0' + CAST(DATEPART(mm,GETDATE()) AS VARCHAR(2)),2) +

    CAST(DATEPART(yy,GETDATE()) AS VARCHAR(4))

    The RIGHT('0' + .....) is to provide the month as 2 characters as shown in your request.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • September 30, 2008 at 5:03 am

    #878755

    bitbucket,

    Thank you for the SQL for just todays date. I am more looking for a step-by-step process of how to include this date in the file name everyday. So what & how I need to configure in Business Intelligence Studio, preferably with screen shots.

    Thanks,

    maui

  • September 30, 2008 at 3:07 pm

    #879192

    something along the lines of

    exec master.dbo.xp_cmdshell 'bcp MyDB.dbo.cust_Lookup out c:\cust_Lookup'+CAST(DATEPART(dd,GETDATE()) AS VARCHAR(4)) + RIGHT('0' + CAST(DATEPART(mm,GETDATE()) AS VARCHAR(2)),2) +

    CAST(DATEPART(yy,GETDATE()) AS VARCHAR(4))+'.csv -n -c -T -S MyServer'

  • September 30, 2008 at 3:29 pm

    #879208

    thanks for everyones help, here is what I was looking for. http://www.sqlnerd.com/ssis_dynamic_dates.htm

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

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