Using SSIS to Maintain the File System

Question

Using SSIS to Maintain the File System

SQLRNNR

SSC Guru

Points: 281334
More actions
February 23, 2012 at 11:47 pm

#257272

Comments posted to this topic are about the item Using SSIS to Maintain the File System
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Viewing 15 posts - 1 through 15 (of 20 total)

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Great article, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Rob Sonders SSC Enthusiast Points: 119 More actions · Answer 2

Rob Sonders

SSC Enthusiast

Points: 119

February 24, 2012 at 5:34 am

#1451333

Could you post the Script Task code in C# please.

mark.hammond SSC Veteran Points: 267 More actions · Answer 3

Good grief! Talk about using a sledgehammer to drive a tack.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Koen Verbeeck (2/24/2012)
Great article, thanks.

Thanks Koen

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Rob Sonders (2/24/2012)
Could you post the Script Task code in C# please.

If somebody out there knows how to do that, they are welcome to post it. I don't have that code.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

mark.hammond (2/24/2012)
Good grief! Talk about using a sledgehammer to drive a tack.

You're welcome to post your more simple elegant solution. As was stated in the onset of the article, there are many ways to perform this task. Many people try to use maintenance plans to perform file cleanup on the OS. Unfortunately, those fail more often than not. Also, that option is also built on SSIS as well.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

mark.hammond SSC Veteran Points: 267 More actions · Answer 7

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.

You can probably use SSIS to trigger FTP processes. The question is WHY would you do that.

I CAN run across the freeway at rush hour. Just because I CAN doesn't mean I SHOULD.

tmcc SSC Rookie Points: 33 More actions · Answer 8

Good example of using SSIS to manage files. For those that using logging to the file system for package runs.. this type of program will help clean up.

Mad Myche SSCommitted Points: 1593 More actions · Answer 9

I was kinda disappointed when I realized that this was not going to be an article on Denali's filestream methods.

Director of Transmogrification Services

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 10

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;

}

steveyc SSC Rookie Points: 44 More actions · Answer 11

steveyc

SSC Rookie

Points: 44

February 25, 2012 at 6:51 am

#1451687

Why not just use the inbuilt File System Task?

Rob Sonders SSC Enthusiast Points: 119 More actions · Answer 12

Rob Sonders

SSC Enthusiast

Points: 119

February 25, 2012 at 7:18 am

#1451690

Thank you Stan.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 13

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.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 14

sharath.chalamgari

SSCertifiable

Points: 5680

February 27, 2012 at 1:51 am

#1451923

Good One