Solved

SSIS - save files with unique name

Posted on 2011-03-11
5
562 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

690 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