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

Question

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

Martyn Griggs 0

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

User's image

Yitzhak Khabinsky 20,646 Reputation points

2023-03-02T20:26:45.25+00:00

@Martyn Griggs,

Are you ok to implement the file renaming via SSIS Script Task?
Martyn Griggs 0 Reputation points

2023-03-02T20:49:18.7366667+00:00

@Yitzhak Khabinsky Yes, I can use Script task, though I’m not great at C#

Yitzhak Khabinsky 20,646 Reputation points

2023-03-02T20:26:45.25+00:00

@Martyn Griggs,

Are you ok to implement the file renaming via SSIS Script Task?
Martyn Griggs 0 Reputation points

2023-03-02T20:49:18.7366667+00:00

@Yitzhak Khabinsky Yes, I can use Script task, though I’m not great at C#

Answer 1

Yitzhak Khabinsky 20,646

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);
	}
}

Martyn Griggs 0 Reputation points

2023-03-02T20:54:52.3+00:00

@Yitzhak Khabinsky Thanks, yes I follow your script and it does look to do what I wanted, so I will mark this as the answer. Much appreciated for your help!

Martyn Griggs 0

@Yitzhak Khabinsky I tried this today in our test environment, and it did not work after all. I inserted the code you gave above and changed the directory path to mine. The script compiles OK and the script task runs OK with no errors, but it does not rename the file. So maybe I am putting something wrong when I paste the code? And do I still need to use the For Each Loop container? If so, do I need to map any variables, and how? Forgive my lack of understanding, as I haven't used the Script task before. Below is my directory after running the Script task, with the file unchanged, and also the code from the Script task I created in case I made some mistake:

User's image


using System.IO;
namespace ST_73875dfd15154311bc3c56529596c2b6
{
    /// <summary>
    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// </summary>
	[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
	public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
	{
        #region Help:  Using Integration Services variables and parameters in a script
        /* To use a variable in this script, first ensure that the variable has been added to 
         * either the list contained in the ReadOnlyVariables property or the list contained in 
         * the ReadWriteVariables property of this script task, according to whether or not your
         * code needs to write to the variable.  To add the variable, save this script, close this instance of
         * Visual Studio, and update the ReadOnlyVariables and 
         * ReadWriteVariables properties in the Script Transformation Editor window.
         * To use a parameter in this script, follow the same steps. Parameters are always read-only.
         * 
         * Example of reading from a variable:
         *  DateTime startTime = (DateTime) Dts.Variables["System::StartTime"].Value;
         * 
         * Example of writing to a variable:
         *  Dts.Variables["User::myStringVariable"].Value = "new value";
         * 
         * Example of reading from a package parameter:
         *  int batchId = (int) Dts.Variables["$Package::batchId"].Value;
         *  
         * Example of reading from a project parameter:
         *  int batchId = (int) Dts.Variables["$Project::batchId"].Value;
         * 
         * Example of reading from a sensitive project parameter:
         *  int batchId = (int) Dts.Variables["$Project::batchId"].GetSensitiveValue();
         * */

        #endregion

        #region Help:  Firing Integration Services events from a script
        /* This script task can fire events for logging purposes.
         * 
         * Example of firing an error event:
         *  Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);
         * 
         * Example of firing an information event:
         *  Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain)
         * 
         * Example of firing a warning event:
         *  Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);
         * */
        #endregion

        #region Help:  Using Integration Services connection managers in a script
        /* Some types of connection managers can be used in this script task.  See the topic 
         * "Working with Connection Managers Programatically" for details.
         * 
         * Example of using an ADO.Net connection manager:
         *  object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);
         *  SqlConnection myADONETConnection = (SqlConnection)rawConnection;
         *  //Use the connection in some code here, then release the connection
         *  Dts.Connections["Sales DB"].ReleaseConnection(rawConnection);
         *
         * Example of using a File connection manager
         *  object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);
         *  string filePath = (string)rawConnection;
         *  //Use the connection in some code here, then release the connection
         *  Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);
         * */
        #endregion


		/// <summary>
        /// This method is called when this script task executes in the control flow.
        /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
        /// To open Help, press F1.
        /// </summary>
		public void Main()
		{
            void Main()
            {
                const String OriginalFileDirectory = @"E:\B2BE\B2BE";
                const String fileWildcard = "*.csv";

                try
                {
                    string[] fileEntries = Directory.GetFiles(OriginalFileDirectory, fileWildcard);

                    if (fileEntries.Length > 0)
                    {
                        string RenamedFileFullyQualified = string.Empty;

                        foreach (string fileFullyQualifiedPath in fileEntries)
                        {
                            if (Path.GetFileNameWithoutExtension(fileFullyQualifiedPath).StartsWith("Header"))
                                RenamedFileFullyQualified = Path.Combine(
                                               Path.GetDirectoryName(fileFullyQualifiedPath),
                                               "Header" +
                                                Path.GetExtension(fileFullyQualifiedPath));

                            if (Path.GetFileNameWithoutExtension(fileFullyQualifiedPath).StartsWith("Details"))
                                RenamedFileFullyQualified = Path.Combine(
                                               Path.GetDirectoryName(fileFullyQualifiedPath),
                                               "Details" +
                                                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);
                }
            }
            // TODO: Add your code here

            Dts.TaskResult = (int)ScriptResults.Success;
		}

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

	}
}

Yitzhak Khabinsky 20,646 Reputation points

2023-03-05T00:42:42.44+00:00

@Martyn Griggs,

I slightly modified c# code to make it more production grade. The same logic. And it is working well. Check it out.

You don't need anymore the SSIS For Each Loop container.

P.S. Please connect with me on LinkedIn.

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

1 answer

Activity