Download multiple files through the FTP in SQL Server - SSIS by serverku

We have completed the reading of earlier methods to receive single file and receive file dynamically through the FTP in SSIS. Hope you like those posts. Previously we have downloaded a file which has specific extension with format of current date.

The same thing and criteria will be repeated again, but one thing is different and it's not downloading a single file and not the specific current date format. It's a download of multiple files which are having an extension with current year like you can see in below shot.

1. Here you can see the files from FTP, among them we will download files meet with the criteria. You can see target files.

2. Drag and drop FTP Task as shown on screen.

3. Choose new FTP connection option for to connect FTP and apply FTP configuration as shown below.

4. Here we want to download files, so we need to go ahead with 'Receive' operation.

5. Configure Local Folder for file to be downloaded.

6. Create one variable for the Remote Path for the FTP and assign the value of the FTP directory from where the file will be received.

7. Here we haven't given static value of Remote Path and file name, but the qualified name will be created dynamically. So apply expression for the Remote Path option like following.

8. Finally, execute the created ssis package and you will see the files received in the local folder. You can see the target files received.

What is the next? Stay tuned for the next posts.

Download file having specific extension in SQL Server - SSIS by serverku

Earlier we have seen some of the methods to download single file from FTP using tsql scripts and SSIS. So it's the same with single file, but a little bit different as we will download the file which have specific extension. Like we will have to receive the files which have extension of specific format of current date.

1. Here you can see the files from FTP, among them we will download one file. You can see target file.

2. Drag and drop FTP Task as shown in screen.

3. Choose new FTP connection option for to connect FTP and apply FTP configuration as shown below.

4. Here we want to download files, so we need to go ahead with 'Receive' operation.

5. Configure Local Folder for file to be downloaded.

6. Create one variable for the Remote Path for the FTP and assign the value of the FTP directory from where the file will be received.

7. Here we haven't given static value of Remote Path and file name, but the qualified name will be created dynamically. So apply expression for the Remote Path option like following.

8. Finally, execute the created ssis package and you will see the file received in the local folder. You can see the target file received in local directory.

Just it. Please share your ideas and comments here. Hope you enjoyed this post. Stay tuned for more posts.

Archive files based on number of files configuration in SQL Server - SSIS by serverku

Hope you read earlier posts to archive files which are older as per days and you liked them. If you have not read then you can go there by following where you have various scenarios with the same,

1. Archive old database backup files using TSQL Script - SQL Server
2. Delete files from specified folder using File System Task in SQL Server - SSIS
3. Delete files from specified folder using Script Task in SQL Server - SSIS
4. Delete files older than retention period from specified folder using Script Task in SQL Server - SSIS
5. Delete files older than retention period from folder with parameter using Script Task in SQL Server - SSIS

Recently I was asked by my friends to delete the files bases on number of files, not based on days. Such a way we can keep number of files every time and rest files get archived. I will give you an example here as I have three folders and want to keep the different number of files in each folder only. Says keep 3 files in BackupFolder1, keep 2 files in BackupFolder2 and keep 1 file in BackupFolder3. So how can we achieve it? Let's move on the steps,

First we will check the files existing in folders,

We are creating a SSIS package here and will use tasks to perform the archive process,

Also the variables used in the whole task flow,

1.
You can see the flow of the tasks, so we will be here for task 1 which will have fetch the records from a table which really need to be archived. We have inserted folder paths and a number of the files which need to keep in each folder. Let's view the records in a table,

Now we will create a task1 with Execute SQL Tasks and get the list of archive folders as figured below,

It fetches the folders and archive numbers for each row and assign to a variable which is object data type. For variable assignment you need to set a ResultSet option to 'Full result set' as above. After that, you mapping variables are as following for output,

2.
We need to go through the values coming out from variable and then map it to two variables again for archive folder name and archive numbers using Foreach Loop Container,

You can see how the object variable holds the values and assigned to other variables again as per below shot,

3.
Now use Execute SQL Task again and get the list of the files which need to deleted, You need to write some SQL statement to get those files as per archive numbers. So we will look the configuration and variable value mapping and using them in the script. You need to enable xp_cmdshell from the configuration.

Same as earlier steps we need to put the SQL query in SQL Statement option and set 'Full result set' for ResultSet, You can see two arrows which will be input values of the script which going to be executed, You can collect the same script here,

CREATE TABLE #TempFiles
(
Seq int identity(1,1),
FileName varchar (500)
)

DECLARE @FullPath varchar(500), @sql varchar(500)

SET @FullPath = ?
SET @sql = 'dir /B /O-D '+ @FullPath

INSERT INTO #TempFiles (FileName)
EXEC xp_cmdshell @sql

SELECT @FullPath + FileName  as FullName
FROM #TempFiles
WHERE FileName is not null
AND Seq > ?

DROP TABLE #TempFiles

After completion of above to get the resulted values in another variable and pass them to the next step,

4.
Use Foreach Loop Container to process, delete operation for each files same steps we did earlier for Foreach Loop,

5.
Finally, use the Script Task to delete the file one by one as I did in below image. You need to pass the value of the file name as read only,

Now use the following script to perform actual tasks for deletion, Use below script in your script task,

You can copy same script from here,

--//Apply below line in #region namespaces
using System.IO;

--//Apply below code inside 
public void Main()
{
string FilePath = Dts.Variables["User::FileName"].Value.ToString();
System.IO.File.Delete(FilePath);
Dts.TaskResult = (int)ScriptResults.Success;
}

After running the package files will be deleted as per archive number defined in the tables for each folder.

You can schedule this package in some frequency. Hope this help you.

Delete files older than retention period from folder with parameter using Script Task in SQL Server - SSIS by serverku

After using some of the ways to delete the files from the particular folder like Delete file using Script Task and File System Task in SSIS. We have seen such methods and used logic to get the files from the loop container and process to delete them and the same thing applied for the scripts as well. We also looked for the file deletion which are older than some Retention period with a Script Task also.

In earlier posts we have not used any parameters in the scripts and directly applied folder path and retention period values there. But here I would like to use parameters for the folder path and retention period and pass through the scripts and delete them as per the condition.

1. Let check the files from the target folder.

2. Create parameters and set the values. Here it is going to be delete the files, which are older than 3 days from the E:\ImagesBackup folder in this case.

3. Drag and drop File System Task

4. Put the parameters as ReadOnlyVariables

5. Apply attached script in editor which have additional logic with condition to check the file last modified date and check if older than specified retention period or not. Here you can see the parameters used in the script.

Please note here we need to import system.IO namespace.

6. Turn on the final step and run package. Files older than the specified retention period to get deleted.

You can use the script below for the same as mentioned in the above image,

--// You need to apply below one line in "namespaces" region.
using System.IO;

--//You need to apply below lines inplace of  // TODO: Add your code here
int RetentionPeriod = Convert.ToInt32(Dts.Variables["User::Period"].Value.ToString());
string directoryPath = Dts.Variables["User::BackupFolder"].Value.ToString();
string[] oldFiles = System.IO.Directory.GetFiles(directoryPath, "*.*");

foreach (string currFile in oldFiles)
  {
         FileInfo currFileInfo = new FileInfo(currFile);
              
            if (currFileInfo.LastWriteTime < (DateTime.Now.AddDays(-RetentionPeriod)))
                {
                    currFileInfo.Delete();
                }
            }

Hope you liked this post. Stay tuned for more.

Delete files older than retention period from specified folder using Script Task in SQL Server - SSIS by serverku

Earlier we have seen in the Delete file using Script Task and file deletion using File System Task in SSIS. We have directly written the logic to get the files and delete from specified folder in the script for the Script Task. For File System Task we have Used Foreach Loop Container and variables to hold file names which are passed from earlier stage one by one and then finally used by the File System Task to delete it.

Here we have same, but don't delete all the files inside pass folder, but get deleted files which are older than the specified days. This is nothing but minor change in the script where we pass the folder path and retention period. Let's start the steps as follow,

1. Let check the files from the target folder.

2. Drag and drop Script Task.

3. Open script editor from the properties.

4. Apply attached script in editor which have additional logic with condition to check the file last modified date and check if older than specified retention period or not.

Please note here we need to import system.IO namespace.

5. Turn on the final step and run package. Files older than the specified retention period to get deleted.

You can also find script code here,

--// You need to apply below one line in "namespaces" region.
using System.IO;

--//You need to apply below lines inplace of  // TODO: Add your code here
int RetentionPeriod = 3;
string directoryPath = @"E:\ImagesBackup";
string[] oldFiles = System.IO.Directory.GetFiles(directoryPath, "*.png");
     foreach (string currFile in oldFiles)
            {
                FileInfo currFileInfo = new FileInfo(currFile);
                if (currFileInfo.LastWriteTime < (DateTime.Now.AddDays(-RetentionPeriod)))
                {
                    currFileInfo.Delete();
                }
            }

Hope you liked this post. Stay tuned for more.

Delete files from specified folder using Script Task in SQL Server - SSIS by serverku

Earlier we have seen for the file deletion using File System Task in SSIS. We have used it in Foreach Loop Container. Used a variable to hold file names which are passed from earlier stage one by one and then finally used by the File System Task to delete it.

Now I am going to use the Script Task to delete all files from a specified folder. Here I have added script to get each file from specified folder and then delete them as you can see in the following steps. Let's start to follow them.

1. Drag and drop Script Task.

2. Open script editor from the properties.

3. Apply attached script in editor and save it.

Please note here we need to import system.IO namespace.

4. Turn on the final step and run package. Files get deleted.

You can also find script code here,

--// You need to apply below one line in "namespaces" region.
using System.IO;

--//You need to apply below lines inplace of  // TODO: Add your code here

 string directoryPath = @"E:\TestFolder";
            string[] oldFiles = System.IO.Directory.GetFiles(directoryPath, "*.txt");
            foreach (string currFile in oldFiles)
            {
                FileInfo currFileInfo = new FileInfo(currFile);
                currFileInfo.Delete();
              
            }

Hope you liked this post. Stay tuned for more.