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

• Download example - 37.23 KB

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:

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:

Step To Be Carried Out

Step 1

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

Click OK.

Step 2

We have renamed the default package to FileCopyAndRename_pkg.dtsx.

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:

Step 4: (Configure the Foreach Loop Container)

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

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:

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

The Add Variable screen will be configured as under:

Click OK.

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

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:

Let us click on the DestinationConnection.

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

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:

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:

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.

And add the below in the main method:

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.

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

• 28^th April, 2011: Initial post