IT Journey: Rename the files in a folder using the SSIS(SQL Server Integration Services) or SSDT Tools(Visual Studio 2019)

Renaming the files in a folder using SSIS is very easy. We need just two tasks.

1) ForEachLoop Container

2)File System task

These both are available in Control-Flow Task.

First Place For Each Loop Container and then place File System Task.

Second Create a variables as shown below.

Filename : This variable is used for Dynamically getting the Filename in SSIS For Each Loop container. We need to set some default name (abc.txt) so that it will create failure in build.

FilePath : This variable is static. You can hardcode the variable path. Or You can create connection and get the File path. Here I created variable and Kept Some Local Path.

SourceFileName : This variable is Dynamic. Use the expression to use the Filename variable so that when ever you get value from For each Loop it will set to SourceFileName so that you will not get error.

You cannot use the same variable for setting the filename and getting the filename.

Error at File System Task: Failed to lock variable "G:\SSISFiles\IMG_ 20190203_165954.jpg" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

DestFileName : This variable is used for Dynamically setting the filename. We can write expression how to rename the files.

Suppose I have image files from different source like whatupp, mails and vedios but i need to sort all based on the timestamp but the inital name was different. So I can use this replace the name of file so that all will be in sorted manner.

After setting the variables we need to set the For Loop container values to pickup dynamically

in folder.

Double click on For Each Loop and Go to the Collection tab. Select Enumerator as For Each File Enumerator.

In Expressions set Directory for the File Path Variabe

FileNameRetieval will get Filename and sets to Filename variable.

In Variable Mappings, Use the the same sequnce to assign the variables using the same order

Index starts from Zero.

Click Ok. Edit the File System task inside the For each loop container.

Set OverwriteDestination to False so that it will not overwrite the files if it matches.

For Destination and Source connection set the Paramaeters IsDestinationPathVarible and

IsSourcePathVariable to True. By default it will be false. Here we have parameterised to use dynamically so we need to set to "True"

SourceVariable : This variable is used for Dynamically getting the Filename in SSIS For Each Loop container. We have wrtitten this expresssion in variable definition. So it will be same and Filename and can be accessed across SSIS Package.

DestinationVariable : This variable is Dynamic. We have set the same to replace "IMG"

name to "VID".

Also do not use the expression to use variable which has been set in For Each Loop Container to set in File System Task.

Error at File System Task: Failed to lock variable "for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Ex:

----------

Before running the Task. You can see there are different file types but I want to keep same name format. I can do that using this simple task.