?
Solved

SSIS - save files with unique name

Posted on 2011-03-11
5
Medium Priority
?
564 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 2000 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

777 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