Search code examples
c#ssisssis-2008

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


Solution

  • 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