CODEWITHSUNDEEP
c#ssisssis-2008
mehtat_90
mehtat_90

Reputation: 628

Delete files based on variable SSIS Script

I have a script in ssis which deletes the file and I need to modify the script by adding variable so that process can be executed dynamically. I would appreciate if some can help me out by showing how to add variable in the script below:-

enter public void Main()
    {
        int RetentionPeriod = 0;
        string directoryPath = @"\\ABCD\EFG\HIJ";--need to add location variable
        string[] oldFiles = System.IO.Directory.GetFiles(directoryPath, "*.csv");
        foreach (string currFile in oldFiles)
        {
            FileInfo currFileInfo = new FileInfo(currFile);
            if (currFileInfo.LastWriteTime < (DateTime.Now.AddDays(-RetentionPeriod)))---need to add date variable here
            {
                currFileInfo.Delete();
            }
        }
        // TODO: Add your code here
        Dts.TaskResult = (int)ScriptResults.Success;
    }
}

}

As shown in the script I need to add two variable namely location variable and Date variable.Both the variables have string data type

I know this question is very silly but I have no exp in writing SSIS scripts

Upvotes: 1

Views: 4572

Answers (2)

Don Goodman
Don Goodman

Reputation: 1

this is over coded: try this: set a variable as answered above to contain the file path Add this to main after setting a System.IO; reference

public void Main()
{
    string FilePath = Dts.Variables["UserControl::File"].Value.ToString();
    if (File.Exists(FilePath))
    {
        File.Delete(FilePath);
    }
    Dts.TaskResult = (int)ScriptResults.Success;
}

Upvotes: 0

Matt
Matt

Reputation: 14341

string directoryPath = Dts.Variables["User::NameOfStringVariable"].Value

You also have to add the variable to the ReadOnly list on the script task configuration. here is a screen shot from another question that shows you where to make the variable accessible to the script:

enter image description here

and in case you don't know where/how to add a variable to a package. One easy way is to right click on the grey area of the control flow and choose variables and that will bring up the variables window then simply add the variable with the appropriate datatype that you want.

enter image description here

Upvotes: 2

Related Questions