SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Jason Brimhall / Using SSIS to Maintain the File System / Latest Posts

RE: Using SSIS to Maintain the File System

InspektorDerrick — Thu, 01 Mar 2012 06:00:57 GMT

[quote][b]mark.hammond (2/29/2012)[/b]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.[/quote]SSIS is not dependent on the SQL Server database engine. Just install it on another server.[quote][b]mark.hammond (2/29/2012)[/b]To my point, though - this solution imposes the constraint of the [u]availability[/u] of SQL Server to perform tasks that have no inherent reliance on the [u]existence[/u], much less the availability, of SQL Server.[/quote]Do you prefer to maintain scheduled jobs on 345 different servers or one job server that executes the jobs for all of them? Of course there may exist many cases where it is not smart to use SSIS for these jobs, but there are also numerous situations where it is the best solution.

RE: Using SSIS to Maintain the File System

mark.hammond — Wed, 29 Feb 2012 17:37:19 GMT

[quote][b]dirty (2/29/2012)[/b][hr]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).[/quote]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 [u]availability[/u] of SQL Server to perform tasks that have no inherent reliance on the [u]existence[/u], 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".

RE: Using SSIS to Maintain the File System

InspektorDerrick — Wed, 29 Feb 2012 15:07:30 GMT

[quote][b]mark.hammond (2/24/2012)[/b][hr]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.[/quote]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).

RE: Using SSIS to Maintain the File System

steveyc — Mon, 27 Feb 2012 13:47:09 GMT

[quote][b]Koen Verbeeck (2/25/2012)[/b][hr][quote][b]steveyc (2/25/2012)[/b][hr]Why not just use the inbuilt File System Task?[/quote]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.[/quote]Yes and i dont remember it being any worse than any other aspect of SSIS..........

RE: Using SSIS to Maintain the File System

pveilleux — Mon, 27 Feb 2012 13:44:34 GMT

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.

RE: Using SSIS to Maintain the File System

SQLRNNR — Mon, 27 Feb 2012 07:20:21 GMT

[quote][b]sharath.chalamgari (2/27/2012)[/b][hr]Good One [/quote]Thanks

RE: Using SSIS to Maintain the File System

sharath.chalamgari — Mon, 27 Feb 2012 01:51:13 GMT

Good One

RE: Using SSIS to Maintain the File System

Koen Verbeeck — Sat, 25 Feb 2012 23:06:51 GMT

[quote][b]steveyc (2/25/2012)[/b][hr]Why not just use the inbuilt File System Task?[/quote]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.

RE: Using SSIS to Maintain the File System

Rob Sonders — Sat, 25 Feb 2012 07:18:38 GMT

Thank you Stan.

RE: Using SSIS to Maintain the File System

steveyc — Sat, 25 Feb 2012 06:51:47 GMT

Why not just use the inbuilt File System Task?

RE: Using SSIS to Maintain the File System

Stan Kulp-439977 — Fri, 24 Feb 2012 12:04:45 GMT

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; } }}

RE: Using SSIS to Maintain the File System

Mad Myche — Fri, 24 Feb 2012 10:53:05 GMT

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

RE: Using SSIS to Maintain the File System

tmcc — Fri, 24 Feb 2012 09:39:54 GMT

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.

RE: Using SSIS to Maintain the File System

mark.hammond — Fri, 24 Feb 2012 08:01:03 GMT

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.

RE: Using SSIS to Maintain the File System

SQLRNNR — Fri, 24 Feb 2012 07:55:18 GMT

[quote][b]mark.hammond (2/24/2012)[/b][hr]Good grief! Talk about using a sledgehammer to drive a tack.[/quote]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.

RE: Using SSIS to Maintain the File System

SQLRNNR — Fri, 24 Feb 2012 07:52:32 GMT

[quote][b]Rob Sonders (2/24/2012)[/b][hr]Could you post the Script Task code in C# please.[/quote]If somebody out there knows how to do that, they are welcome to post it. I don't have that code.

RE: Using SSIS to Maintain the File System

SQLRNNR — Fri, 24 Feb 2012 07:51:11 GMT

[quote][b]Koen Verbeeck (2/24/2012)[/b][hr]Great article, thanks.[/quote]Thanks Koen

RE: Using SSIS to Maintain the File System

mark.hammond — Fri, 24 Feb 2012 06:43:27 GMT

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

RE: Using SSIS to Maintain the File System

Rob Sonders — Fri, 24 Feb 2012 05:34:23 GMT

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

RE: Using SSIS to Maintain the File System

Koen Verbeeck — Fri, 24 Feb 2012 01:03:41 GMT

Great article, thanks.

Using SSIS to Maintain the File System

SQLRNNR — Thu, 23 Feb 2012 23:47:14 GMT

Comments posted to this topic are about the item [B]Using SSIS to Maintain the File System[/B]