Link to home
Start Free TrialLog in
Avatar of sqlagent007
sqlagent007Flag for United States of America

asked on

how can I modify this c# script code to look for a folder with name LIKE "archive_*"?

I have a nightly job from another system that drops a new folder on a share with the name "archive_YYYY-MM-DD".

I found some cool code that will find the most recent folder in a specific directory, but I also need to filter on name.

Can anybody help me tweak this code to return the most recent folder with the name "archive_"? There are other folders that get created in that share, and so sometimes this code will pick the wrong folder I need because some other process wrote  newer folder.

public void Main()
{
    Variables varCollection = null;

    Dts.VariableDispenser.LockForRead("User::RootFolder");
    Dts.VariableDispenser.LockForWrite("User::RecentFolder");
    Dts.VariableDispenser.GetVariables(ref varCollection);

    string rootFolder = varCollection["User::RootFolder"].Value.ToString();
    DateTime previousFolderTime = DateTime.MinValue;
    string recentFolder = string.Empty;

    foreach (string subFolder in System.IO.Directory.GetDirectories(rootFolder))
    {
        DateTime currentFolderTime = System.IO.Directory.GetCreationTime(subFolder);
        if (previousFolderTime == DateTime.MinValue || previousFolderTime <= currentFolderTime)
        {
            previousFolderTime = currentFolderTime;
            recentFolder = subFolder;
        }
    }

    varCollection["User::RecentFolder"].Value = recentFolder;

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

Open in new window

Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

If you want to check to see if rootFolder starts with string "archive_":

if (rootFolder.Substring(0, 8) == "archive_")
	{
		foreach (string subFolder in System.IO.Directory.GetDirectories(rootFolder))
		{
			DateTime currentFolderTime = System.IO.Directory.GetCreationTime(subFolder);
			if (previousFolderTime == DateTime.MinValue || previousFolderTime <= currentFolderTime)
			{
				previousFolderTime = currentFolderTime;
				recentFolder = subFolder;
			}
		}
	}

Open in new window

Avatar of sqlagent007

ASKER

The root folder is never "archive_"..only the sub folders will have archive_ or tape_, etc. Can I use the substring within the listing of the sub folders? I think that is where i need it. Sorry..I am not a c# person...
Is there a way to do something like this:
foreach (string subFolder.[b]substring(0,7) == "archive_" [/b] in System.IO.Directory.GetDirectories(rootFolder))

Open in new window

sorry..I tried to add a bold tag in  a code tag..

foreach (string subFolder.substring(0,7) == "archive_" in System.IO.Directory.GetDirectories(rootFolder))
Avatar of it_saige
If you look at the parameter list for GetDirectories, you will see that a parameter is provided for a search pattern - DirectoryInfo.GetDirectories Method (String)

So you could simply use:
public void Main()
{
	Variables varCollection = null;

	Dts.VariableDispenser.LockForRead("User::RootFolder");
	Dts.VariableDispenser.LockForWrite("User::RecentFolder");
	Dts.VariableDispenser.GetVariables(ref varCollection);

	DirectoryInfo dir = new DirectoryInfo(varCollection["User::RootFolder"].Value.ToString());
	DateTime previousFolderTime = DateTime.MinValue;
	DirectoryInfo recentFolder = new DirectoryInfo();

	if (dir.Exists)
	{
		foreach (var subFolder in dir.GetDirectories("archive_*"))
		{
			DateTime currentFolderTime = subFolder.CreationTime;
			if (previousFolderTime == DateTime.MinValue || previousFolderTime <= currentFolderTime)
			{
				previousFolderTime = currentFolderTime;
				recentFolder = subFolder;
			}
		}

		varCollection["User::RecentFolder"].Value = recentFolder.FullName;

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

Open in new window


Discussed in this previous EE PAQ - http:/Q_28630718.html#a40649141

-saige-
re:> Can I use the substring within the listing of the sub folders?

You can use

if (YourVariable.Substring(0, 8) == "archive_")

wherever you need.
I am not sure if the server is  on .Net 4. I am getting a red line under foreach, and the tool tip says: cannot convert type 'System.IO.DirectoryInfo' to 'string'. I can't upgrade the net framework now
Ok, I am on .net 4..however I still not able to get the code to work.

User generated image
You are getting the error because GetDirectories returns an array of DirectoryInfo objects.  So you want to use either 'var' or 'DirectoryInfo' instead of 'string':
foreach (var subFolder in dir.GetDirectories("archive_*"))

Open in new window

Or
foreach (DirectoryInfo subFolder in dir.GetDirectories("archive_*"))

Open in new window

Not
foreach (string subFolder in dir.GetDirectories("archive_*"))

Open in new window


Also GetDirectories is valid in .NET 3.0 and .NET 3.5.

-saige-
Refer to the code in my original post, it should compile and run.

-saige-
Thank you @saige, but SSIS script is complaining about line 55:  DirectoryInfo recentFolder = new DirectoryInfo();

Says: Error      1      'System.IO.DirectoryInfo' does not contain a constructor that takes '0' arguments      

See file with screen shot..I did copy \ paste of your code...thank you for your help!
ee.ssis.script.task2.JPG
ASKER CERTIFIED SOLUTION
Avatar of it_saige
it_saige
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I may have something misconfigured, because anytime I use the "LockForRead" or "LockForWrite", I get a deadlock error.

Error: A deadlock was detected while trying to lock variables "User::RootFolder" for read access and variables "User::RecentFolder" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out.

Not sure if I can just not use the LockForWrite, but when I tried commenting out just those lines, the script would not compile.

Thanks for all the help..I think we are almost there...
ee.ssis.script.task3.JPG
I am still confused as to why the explicit LockForRead and LockForWrite don't work, but I was able to comment that stuff out and just let the script task handle the locking..I may be in for larger trouble down the road..but for now it is working. I ended up replacing the varCollection with "Dts.Variables" and commenting out the lock statements..its working, so I am will move on. Thanks experts!
Thanks!
Answered here:
If you've put the variable in the ReadOnlyVariables or ReadWriteVariables properties on the script task, you need to remove it. When you explicitly handle locking (which is the better option), you don't need to include it there, or it tries to lock it twice.
-saige-