Using SSIS 2008 to handle file retention

Question

Post reply

Using SSIS 2008 to handle file retention

dwilliscp

SSCrazy Eights

Points: 9270
More actions
November 12, 2014 at 8:37 am

#313010

Everything I have found, searching the internet, uses C# 2010 code, and when I put the code into 2008 it will not compile. So there is a red X in the Script Task, in the Control Flow stating this.
Does anyone have documentation on how to set this up using SSIS 2008?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246971 More actions · Answer 1

Can you provide a link or some background to your question please? I don't know what you are referring to.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 2

I did finally get the C# syntax for 2008... so this is what I ended up with, in the script task box. Note it connects to our file and print server and then uses a 5 day retention:

using System;

using System.IO;

using System.Data;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

namespace ST_9b79b370225a48b2b3e9cb568006674e.csproj

{

[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]

public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

{

#region VSTA generated code

enum ScriptResults

{

Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,

Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

};

#endregion

public void Main()

{

// string FolderPath = Dts.Variables["P04FilePah"].Value.ToString();

string FolderPath = "\\\\R2D2\\DB_Backup\\SQLP01";

//move trace files to completed folder

string[] oldFiles = Directory.GetFiles(FolderPath, "*.zip");

foreach (string currFile in oldFiles)

{

FileInfo currFileInfo = new FileInfo(currFile);

if (currFileInfo.LastWriteTime < (DateTime.Now.AddDays(-5)))

{

File.Delete(currFile);

}

Dts.TaskResult = (int)ScriptResults.Success;

}

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 3

BTW I created a script task for each server, that way I could disable a server's removal of database files if needed (this came up last week and we needed to clean up a mistake that happened in Prod).