Generate unique File Names on Export

Question

Generate unique File Names on Export

Steve-335899

SSChasing Mays

Points: 639
More actions
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

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

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

Marvin D SSCarpal Tunnel Points: 4058 More actions · Answer 1

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

Da Posh SSC Enthusiast Points: 135 More actions · Answer 2

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

Steve-335899 SSChasing Mays Points: 639 More actions · Answer 3

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

Bill Scott SSCrazy Points: 2338 More actions · Answer 4

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")

Steve-335899 SSChasing Mays Points: 639 More actions · Answer 5

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

Jamie Thomson SSChampion Points: 11805 More actions · Answer 6

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

Bill Scott SSCrazy Points: 2338 More actions · Answer 7

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

Steve-335899 SSChasing Mays Points: 639 More actions · Answer 8

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.

KD-355437 SSC Rookie Points: 31 More actions · Answer 9

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

Jamie Thomson SSChampion Points: 11805 More actions · Answer 10

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