Using SSIS to Maintain the File System

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jason Brimhall » Using SSIS to Maintain the File System

21 posts, Page 2 of 3

««1 2 3 »»

Using SSIS to Maintain the File System

Rate Topic

Display Mode

Topic Options

Author

Message

Stan Kulp-439977

Posted Friday, February 24, 2012 12:04 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 10, 2013 9:56 AM
Points: 77, Visits: 595

using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Math;
using Microsoft.SqlServer.Dts.Runtime;

namespace ST_ac77e2644b9b4a1090164b90072fd897.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 SourcePath = null;
int PurgeDays = 0;
string FileExtension = null;

PurgeDays = Convert.ToInt32(Dts.Variables["User::DaysToKeep"].Value);
SourcePath = Convert.ToString(Dts.Variables["User::DirectoryToMaintain"].Value);
FileExtension = Convert.ToString(Dts.Variables["User::FileExtension"].Value);

foreach (FileInfo file in new DirectoryInfo(SourcePath).GetFiles())
{
if (((DateTime.Now - file.LastWriteTime).Days > PurgeDays) & (file.Extension == FileExtension))
{
try
{
file.Delete();

}
catch (Exception ex)
{

}
}

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

Post #1257587

steveyc

Posted Saturday, February 25, 2012 6:51 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, July 03, 2012 7:22 AM
Points: 2, Visits: 49

Why not just use the inbuilt File System Task?

Post #1257768

Rob Sonders

Posted Saturday, February 25, 2012 7:18 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, February 23, 2013 7:44 AM
Points: 4, Visits: 200

Thank you Stan.

Post #1257773

Koen Verbeeck

Posted Saturday, February 25, 2012 11:06 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:54 AM
Points: 9,364, Visits: 6,462

steveyc (2/25/2012)

Why not just use the inbuilt File System Task?

Have you ever used it before and made it dynamic with expressions?
It can be a nightmare, and .NET is easier, more elegant and you're in control more.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1257899

sharath.chalamgari

Posted Monday, February 27, 2012 1:51 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, September 17, 2012 7:30 AM
Points: 1,038, Visits: 679

Good One

Post #1258070

SQLRNNR

Posted Monday, February 27, 2012 7:20 AM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:46 PM
Points: 18,732, Visits: 12,329

sharath.chalamgari (2/27/2012)

Good One

Thanks

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1258236

pveilleux

Posted Monday, February 27, 2012 1:44 PM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 09, 2013 6:50 PM
Points: 259, Visits: 758

Sweet - thanks for sharing....

One thing I found was for the step "Split FileList", the SQL statement being executed has a variable name in it. I had to remove it, due to an error, and replace it with a ?. It went from this:

Select Item
From stringsplitter(@FileExtensionList,',')

to this:

Select Item
From stringsplitter(?,',')

with the Parameter Mapping page looking the same as what you described. Just wanted to mentioned this in case anyone else ran into the error.

I also tweaked it a bit to delete files based on hours, rather than days. We run into an issue with days sometimes, if a backup takes longer yesterday than it did today. The file we would be deleting would not be more than 24 hours old.

Post #1258504

steveyc

Posted Monday, February 27, 2012 1:47 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, July 03, 2012 7:22 AM
Points: 2, Visits: 49

Koen Verbeeck (2/25/2012)

steveyc (2/25/2012)

Why not just use the inbuilt File System Task?

Have you ever used it before and made it dynamic with expressions?
It can be a nightmare, and .NET is easier, more elegant and you're in control more.

Yes and i dont remember it being any worse than any other aspect of SSIS..........

Post #1258506

InspektorDerrick

Posted Wednesday, February 29, 2012 3:07 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 13, 2013 1:34 PM
Points: 2, Visits: 49

mark.hammond (2/24/2012)

My point is why use a database process to act on a file system. Let the database system do what it is intended to do and, likewise, let the file system do what it is intended to do.

SSIS is not a "database process", it is a flexible tool to execute various tasks e.g. ETL on various types of datasources and destinations: ftp, file services, databases, ...

The file system itself is not able to do the cleanup work as you suppose, you have to use a program or a script (e.g. powershell, vbscript, batch script).

Post #1259831

mark.hammond

Posted Wednesday, February 29, 2012 5:37 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 7:20 AM
Points: 113, Visits: 52

dirty (2/29/2012)

Forgive me. I thought SSIS (SQL Server Integration Services) would be dependent on SQL Server. Maybe you know of some way to make it operate independently.

I do not have any argument with the content of this solution. As far as I know, it will do everything Jason suggests it will do.

To my point, though - this solution imposes the constraint of the availability of SQL Server to perform tasks that have no inherent reliance on the existence, much less the availability, of SQL Server. This SSIS process will work fine until one day when SQL Server is unavailable for some reason (maintenance, error, upgrade, whatever).

On that day, the file cleanup process will not run. Someone will ask "Why didn't it run?". And Jason will have to answer, "It did not run because I chose to implement the process using the tool with which I was comfortable (his words), not necessarily the tool that was proper for the task".

Post #1259878

« Prev Topic | Next Topic »

21 posts, Page 2 of 3

««1 2 3 »»

Permissions