Click here to Skip to main content
15,867,686 members
Articles / Database Development / SQL Server / SQL Server 2008

Copy Files from One Location to Another and Rename Them after Putting Time Stamp

Rate me:
Please Sign up or sign in to vote.
4.84/5 (20 votes)
27 Apr 2011CPOL4 min read 182.7K   3.2K   18   10
A program to demonstrate the copy files from one location to another and rename them after putting time stamp by using Sequence Container, Foreach Loop Container, File System Task and Script task

Introduction

Recently, we encountered a situation where given some text files in a source folder, we needed to copy them to a destination folder and the file names had to be time stamped. This is a very common task and in the following paragraphs, we will explore how to do this. In this article, I agree that there are many steps for which I need not have given a diagrammatic approach, but by keeping in mind that many new SSIS developers will like a complete step by step approach, I have presented the content in its entirety.

Background

SSIS has simplified the approach of accomplishing very complex tasks for which we would have otherwise needed to write lots of code. This article will show the usage of working with Sequence Container, Foreach Loop container, File System Task and Script Task components. The example upon which the article has been written is a very common task that many of us have faced. Henceforth, I thought of documenting the same for those who have not yet encountered it but will do so in the near future.

Given Input

The source folder is as under:

1.jpg

Expected Output

The objective is to copy these files from the source location to the destination folder such that the file names should be renamed as per the timestamp. The expected output should be as under:

2.jpg

Step To Be Carried Out

Step 1

Let us create a new BIDS project and choose Integration Services Project as the project template:

3.jpg

Click OK.

Step 2

We have renamed the default package to FileCopyAndRename_pkg.dtsx.

4.jpg

Step 3

Let us drag and drop a Sequence Container on to the Control Flow window and add a Foreach Loop Container inside it. Next, let us add a File System Task inside the Foreach loop container. At this point of time, the package design looks as under:

5.jpg

Step 4: (Configure the Foreach Loop Container)

Double click on the Foreach Loop container for opening up the Foreach Loop Editor.

6.jpg

Out of the various Foreach enumerators found in the Collection tab, we will choose only the Foreach File enumerator. We will also specify the source folder name (which is C:\Sourcefolder in this case) and since we will deal only with text files, so the files will be filtered by .txt extension as shown below:

7.jpg

In the variable mapping section, we will choose <New Variable>:

8.jpg

The Add Variable screen will be configured as under:

9.jpg

Click OK.

Since after every iteration, the Foreach enumerator will return only one value, the variable's index should be mapped to 0.

10.jpg

Click OK to save the settings for the Foreach container.

Step 5: (Configure the File System Task)

Let us now configure the File System Task by double clicking on it and the File System Task Editor opens up:

11.jpg

Let us click on the DestinationConnection.

12.jpg

After clicking on the <New connection…>, the File connection Manager Editor opens up.

13.jpg

Where the UsageType will be Existing folder and we need to specify the Destination folder which is D:\DestinationFolder in this case. Click on OK. The other changes are highlighted as under:

14.jpg

Since we are interested in copying the files, henceforth the Operation type has been set to Copy File. The source path variable is indeed available and hence it is set to true. And finally, we need to specify the Source Variable name. Click OK.

Step 6

At this stage, if we run the package, we will find that the files have been copied to the destination folder (without renaming).

But our objective is also to rename the file names after putting the time stamp. For that reason, we will add another Sequence container and a Script Task component should be embedded inside it.

The package design now looks as under:

15.jpg

Step 7: (Configure the Script Task Component)

Double click on the Script component task and the Script task editor opens. Click on the Edit Script button.

16.jpg

And add the below in the main method:

C#
public void Main()
        {            
            DirectoryInfo directoryInfo = new DirectoryInfo(@"D:\DestinationFolder");
            //if the director exists then proceed
            if (directoryInfo.Exists)
            {
                var fileList = directoryInfo.GetFiles();

                foreach (FileInfo fleInfo in fileList)
                {
                    var newFileName = GetNewFileName(fleInfo);
                    //copies the new file names
                    fleInfo.CopyTo(newFileName, true);
                    //delete the old/original files
                    fleInfo.Delete();
                }
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            else
            {
                MessageBox.Show("Directory not found", "Invalid directory", 
		MessageBoxButtons.OK, MessageBoxIcon.Information);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
        }
        
        // Function to get the new file name        
        private static string GetNewFileName(FileInfo fleInfo)
        {
            var shortDate = DateTime.Now.ToShortDateString().Replace("/", string.Empty);
            var timeInMilliSec = DateTime.Now.Millisecond.ToString();
            var format = string.Format("{0}_{1}", shortDate, timeInMilliSec);
            var extension = ".txt";
            return Path.Combine(fleInfo.DirectoryName, 
		string.Concat(fleInfo.Name.Split('.')[0], "_", format, extension));
        }

Of course, we need to add reference to System.IO. Build the code and close the Script Task Editor.

Step 8

Next let's run the package. And it runs as expected.

17.jpg

Conclusion

Hope this small experiment has helped you in understanding how to work with Sequence Container, Foreach Loop container, File System Task and Script Task components. We have seen how to configure those components, how to use all those together to work. We have also seen the usage of variables in the package. We can even pass the Directory names and file extension at runtime to the Script Task component by setting those into variables.

Thanks for reading.

History

  • 28th April, 2011: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Comments and Discussions

 
PraiseReview Pin
Member 1272257314-Sep-16 20:58
Member 1272257314-Sep-16 20:58 
I was using SSIS for the first time and this explanation is simple , easy to understand and also helped me to do what I was looking for.


Thank You Niladri_Biswas
Thank You Code Project
QuestionDate format change Pin
Member 1176411310-Aug-15 6:32
Member 1176411310-Aug-15 6:32 
Questionvisual basic Pin
Mahi Mahi24-Apr-15 18:59
Mahi Mahi24-Apr-15 18:59 
AnswerRe: visual basic Pin
Member 1340452611-Sep-17 10:28
Member 1340452611-Sep-17 10:28 
QuestionGreat post, this is all what i wanted. Pin
Member 1075029415-Apr-14 0:14
Member 1075029415-Apr-14 0:14 
GeneralNice example Pin
rchacko24-Jul-13 1:18
rchacko24-Jul-13 1:18 
QuestionHi Can you help me how to add progress bar in this code Pin
Ganapathy_Rajan14-Jun-13 3:25
Ganapathy_Rajan14-Jun-13 3:25 
GeneralMy vote of 5 Pin
kotieswar chowdary31-Jul-11 3:53
kotieswar chowdary31-Jul-11 3:53 
GeneralMy vote of 5 Pin
Member 43208444-May-11 15:49
Member 43208444-May-11 15:49 
GeneralVSTS 2010 RC does not support SSIS Package Development Pin
Wühlmaus28-Apr-11 0:51
Wühlmaus28-Apr-11 0:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.