Solved

SSIS - save files with unique name

Posted on 2011-03-11
5
560 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

738 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