Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Using SSIS to Maintain the File System Expand / Collapse
Author
Message
Posted Friday, February 24, 2012 12:04 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 20, 2014 10:05 AM
Points: 128, Visits: 920
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
Posted Saturday, February 25, 2012 6:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 3, 2012 7:22 AM
Points: 2, Visits: 49
Why not just use the inbuilt File System Task?
Post #1257768
Posted Saturday, February 25, 2012 7:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 12, 2014 8:13 PM
Points: 5, Visits: 259
Thank you Stan.
Post #1257773
Posted Saturday, February 25, 2012 11:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:42 AM
Points: 13,295, Visits: 11,086
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
Posted Monday, February 27, 2012 1:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 10:39 AM
Points: 1,194, Visits: 787
Good One
Post #1258070
Posted Monday, February 27, 2012 7:20 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 8:18 PM
Points: 17,824, Visits: 15,756
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
Post #1258236
Posted Monday, February 27, 2012 1:44 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 7:18 AM
Points: 280, Visits: 826
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
Posted Monday, February 27, 2012 1:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 3, 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
Posted Wednesday, February 29, 2012 3:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 29, 2014 4:36 AM
Points: 2, 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).
Post #1259831
Posted Wednesday, February 29, 2012 5:37 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 3:23 PM
Points: 116, Visits: 55
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".
Post #1259878
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse