Post reply

Generate unique File Names on Export

  • July 25, 2006 at 12:49 pm

    #118886

    Good day all,

    I have been trying to replicate a process that I use in DTS and of course, I can not get it to work in SSIS. It uses ActiveX script to generate the unique filename so that you never over write data (for stats etc).

    This a portion of the ActiveX Script from the 2000 process

    ' **** USE SYSTEM DATE & TIME TO CREATE UNIQUE NAME OF FILE - i.e. Stats-Aug22-14-49-DOWNLOAD.txt

    file_name = "Stats-" & MonthName(Month(date),1) & day(date) & "-" & hour(time) & "-" & minute(time) & "-UpdateStats.xls"

    ' **** SPECIFY CONNECTION WHERE FILE NAME PROPERTY WILL BE CHANGED

    Set oConn = dtsglobalvariables.parent.connections("Microsoft Excel 97-2000")

    oConn.DataSource = file_location & file_name

    How can I replicate this process in SSIS? The ActiveX Script component in SSIS does not support this 2000 function.

    Please help!!!

    Much appreciated for anytime you put in to this question.

    Thanks,

    Steve

  • July 25, 2006 at 4:08 pm

    #651495

    Steve

    I'd try an execute SQL task and assign that value to a user defined variable.  Then I'd set the connection string expression to that variable.  Hope this helps.

     

    Marvin Dillard
    Senior Consultant
    Claraview Inc

  • July 25, 2006 at 10:14 pm

    #651524

    you would have to use a script task... have a look at this link. worked for me

    http://blogs.conchango.com/jamiethomson/archive/2005/09/14/2149.aspx

  • July 26, 2006 at 6:43 am

    #651595

    Thanks guys for the help.

    The article posted there does create and move the file, but doesnt help me to uniquely name it. I'm gonna have to create a further variable i think though, to be able to create the unique name.

    Thanks guys for the direction.

    Steve

  • July 26, 2006 at 9:23 am

    #651670

    change the code from the example to look something like this inside the Try portion of the routine:

    ' DTS Varialbe "Destination" is a path to write the file to

    Dim OutputFile As String = Dts.Variables("Destination").Value.ToString

    If OutputFile.Substring(OutputFile.Length - 1, 1) <> "\" Then

    OutputFile = OutputFile & "\"

    End If

    OutputFile = OutputFile & Format(Now(), "YYYYMMddHHmmss")

    File.Move(Dts.Variables("source").Value.ToString, OutputFile)

    This will give you a unique file name based on the date and time that the file was created.  You could also put a name in before the format function so that you can give it a more readable name.

    OutputFile = OutputFile & "DevDatabase_" & Format(Now(), "YYYYMMddHHmmss")

  • July 26, 2006 at 1:23 pm

    #651735

    Hey Bill,

    This works awesome. Another question now. How do I in SSIS then be able to export to that "dynamic" named file. In DTS I would just connect an Excel Destination, that gets called by the ActiveX script and gets changed in the process. How can I do that in SSIS?

    Thanks again,

    Steve

  • July 26, 2006 at 1:51 pm

    #651738

    You can set the name of the file dynamically by using an expression on the Flat File/Excel/whatever connection manager. That expression can in fact do all the work for you - no need to bother messing about with script tasks.

    -Jamie

     

    Jamie Thomson
    http://sqlblog.com/blogs/jamie_thomson

  • July 27, 2006 at 11:32 am

    #651957

    Sorry Steve, I haven't had an opportunity to explore SSIS yet.

  • July 27, 2006 at 11:36 am

    #651958

    Thanks all for the help.....I am close to replicating what I can do in DTS....but right now I've just come up with a pseudo cheater way to do it. This is copying a template, uniquely naming it with the time its produced and then exporting the data to it. I want to avoid the template option all together.

    BTW, Bill, SSIS is pretty awesome and powerful compared to DTS.

  • August 3, 2006 at 6:13 pm

    #653238

    Hey Jamie,

    How can I set the filename using an expression? The expression does not allow me to format the date and time in the format YYYYDDMMHHMM. If you know of way to do that could you please point me in the right direction. Your blogs have been an invaluable resource for me in learning about the ins and outs of SSIS. Keep blogging.

    Thanks

    ~KD

  • August 4, 2006 at 12:00 am

    #653251

    KD,

    Its a string manipulation problem. Pull out all of the different parts of the date using DATEPART function and concatenate them together.

    -Jamie

     

    Jamie Thomson
    http://sqlblog.com/blogs/jamie_thomson

Viewing 11 posts - 1 through 10 (of 10 total)

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