Solved

SSIS - save files with unique name

Posted on 2011-03-11
5
554 Views
Last Modified: 2013-11-10
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?
0
Comment
Question by:PsychoDazey
  • 2
  • 2
5 Comments
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35110512
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
 
LVL 6

Author Comment

by:PsychoDazey
ID: 35110562
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
 
LVL 30

Expert Comment

by:Reza Rad
ID: 35111244
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
 
LVL 22

Accepted Solution

by:
8080_Diver earned 500 total points
ID: 35111437
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
 
LVL 6

Author Closing Comment

by:PsychoDazey
ID: 35112109
Thanks, appreciate the assist.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now