SSIS - How to rename file downloaded from FTP where full filename is not known

Martyn Griggs 0 Reputation points
2023-03-02T17:31:13.32+00:00

I have a package which downloads 2 csv files from FTP server each day, the filename contains the date & time like so:

HeaderEDIInvoiceData-2023-03-02 05:00.csv

DetailsEDIInvoiceData-2023-03-02 05:00.csv

I use a variable expression to look for the files with the current date and download them - the time is not always exactly the same, so I used a wildcard in my expression for that part.

This part is working - the files download to my local folder E:\B2BE\B2BE successfully.

What I now want to do, is re-name the files to:

Header.csv

Details.csv

(once the package has completed, these would be archived to another folder)

What I am struggling with is how to do the re-name of each file as I don't know the full name of each csv file in advance for the task. In essence my logic is:

If there is a file which starts with "Header....." then re-name it to Header.csv

But I can't seem to work out how to do this. I cannot rename the files on the FTP server as others use that.

I tried to use a For Each Loop with a file enumerator and a variable for the filename, but when I add a File System task into the For Each Loop and try to use the variable as the source part of the File System rename task, it doesn't seem to pass anything and the task doesn't run, saying that the variable I created in the For Each Loop is empty.

What am I doing wrong? Or maybe there is a simpler way?

Some shots of my package below:

Many thanks for your help

Martyn

image

User's image

User's image

User's image

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,451 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Yitzhak Khabinsky 24,941 Reputation points
    2023-03-02T20:45:45.46+00:00

    Hi @Martyn Griggs,

    It is very easy to implement via SSIS Script Task.

    
    void Main()
    {
    	const String OriginalFileDirectory = @"e:\Tempy";
    	const String fileWildcard = "*.csv";
    
    	try
    	{
    		string[] fileEntries = Directory.GetFiles(OriginalFileDirectory, fileWildcard);
    
    		if (fileEntries.Length > 0)
    		{
    			string RenamedFileFullyQualified = string.Empty;
    			string caseSwitch = string.Empty;
    			string FileName = string.Empty;
    
    			foreach (string fileFullyQualifiedPath in fileEntries)
    			{
    				caseSwitch = Path.GetFileNameWithoutExtension(fileFullyQualifiedPath);
    				switch (caseSwitch)
    				{
    					case string s when s.StartsWith("Header"):
    						FileName = "Header";
    						break;
    					case string s when s.StartsWith("Details"):
    						FileName = "Details";
    						break;
    				}
    				
    				// concise version
    				//FileName = (caseSwitch.StartsWith("Header") ? "Header" : caseSwitch.StartsWith("Details") ? "Details" : "Unknown");
    				
    				RenamedFileFullyQualified = Path.Combine(
    								   Path.GetDirectoryName(fileFullyQualifiedPath),
    								   FileName +
    									Path.GetExtension(fileFullyQualifiedPath));
    
    				File.Move(fileFullyQualifiedPath, RenamedFileFullyQualified);
    				Console.WriteLine("Renamed '{0}' file into '{1}'", fileFullyQualifiedPath, RenamedFileFullyQualified);
    			}
    		}
    		else
    		{
    			Console.WriteLine("No files '{0}' in the folder '{1}'", fileWildcard, OriginalFileDirectory);
    		}
    	}
    	catch (Exception ex)
    	{
    		Console.WriteLine(ex.Message);
    	}
    }