SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using SSIS to Maintain the File System


Using SSIS to Maintain the File System

Author
Message
Stan Kulp-439977
Stan Kulp-439977
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2345 Visits: 1143
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:BigGrinaysToKeep"].Value);
SourcePath = Convert.ToString(Dts.Variables["User:BigGrinirectoryToMaintain"].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
steveyc
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 58
Why not just use the inbuilt File System Task?
Rob Sonders
Rob Sonders
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 355
Thank you Stan.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59871 Visits: 13297
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
sharath.chalamgari
sharath.chalamgari
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1952 Visits: 798
Good One
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64289 Visits: 18570
sharath.chalamgari (2/27/2012)
Good One


Thanks



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

pveilleux
pveilleux
Old Hand
Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)

Group: General Forum Members
Points: 364 Visits: 1031
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.
steveyc
steveyc
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 58
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..........
InspektorDerrick
InspektorDerrick
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 62
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).
mark.hammond
mark.hammond
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 59
dirty (2/29/2012)
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).


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".
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search