SSIS - save files with unique name

I am using SQL server 2005 standard edition.  I am downloading excel files over an FTP and then importing the data into my database.  Once the file is downloaded it is replaced with another file of the same name on the FTP site.  I want to save the file I downloaded to a directory on my server for historical reasons but I dont want to overwrite the existing files.  How would I go about making sure the files have a unique name?
LVL 6
PsychoDazeyAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
8080_DiverConnect With a Mentor Commented:
Okay, I'll try.

I am going to assume that you have (or will create ;-) 3 User Variables of String type:
SourcePathedFileName
TargetFileNamePattern
TargetPathedFileName
.

I am also going to assume that you have a fixed archive path/filename pattern that is known and a fixed fully pathed source filename that is known.  Further more, I am assuming that you will have populated the appropriate variables with the fully pathed and filenames/filename-patterns.  Finally, I am going to assume that you want to have the archived file given the same file type as the source file.

Now, you will need a Script Task in your SSIS package.  The SourcePathedFileName and TargetFileNamePattern should be listed in the ReadOnlyVariables while the TargetPathedFileName should be list in the ReadWriteVariables.  In the test of the Script, you will need to put the code shown in the attached Script Text.

Now, you will need two File Connection Managers:
SourceFileConnection
TargetFileConnection
.

Then you set an Expression in each of Connection Managers to set the Connectionstring based on the pathed filename variables.


' Script Text
Dim TargetFileName As String

TargetFileName = Dts.Variables("TargetFileNamePattern").Value.ToString()+ _
                  DateTime.Now.ToString("yyyymmddHHmmss") & _
                  Dts.Variables("SourcePathedFileName").Value.ToString().Substring(Dts.Variables("SourcePathedFileName").Value.ToString().Length()- 3, 4)

Dts.Variables("TargetFileNamePattern").Value = TargetFileName.ToString()

Dts.TaskResult = Dts.Results.Success

Open in new window

0
 
8080_DiverCommented:
I am sort of assuming that you are setting up or have set up an SSIS package to handle this.  If so, then you can use a User Variable to set up the new file name and then use a System File Task to copy the file from the initial location to an archive folder with the new name.  Given that the file comes in daily, I would still append the date/time (formatted as yyyymmddhhnnss) to the initial filename as it is moved to the Archive folder.  The FIilename User Variable can be set up in a Script Task.
0
 
PsychoDazeyAuthor Commented:
Yes, I am using an SSIS package to do this.  Can you give me an example of how to append the date/time using a variable?
0
 
Reza RadConsultant, TrainerCommented:
you can use Expression to create a file name based on Date and time of downloading the file.
the GETDATE() expression function can be used in this way.
0
 
PsychoDazeyAuthor Commented:
Thanks, appreciate the assist.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.