Using SSIS to Maintain the File System

Question

Using SSIS to Maintain the File System

Viewing 6 posts - 16 through 20 (of 20 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

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[/url]
Learn Extended Events

pveilleux Ten Centuries Points: 1363 More actions · Answer 2

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 SSC Rookie Points: 44 More actions · Answer 3

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 SSC Rookie Points: 32 More actions · Answer 4

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 SSC Veteran Points: 267 More actions · Answer 5

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

InspektorDerrick SSC Rookie Points: 32 More actions · Answer 6

mark.hammond (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.

SSIS is not dependent on the SQL Server database engine. Just install it on another server.

mark.hammond (2/29/2012)
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.

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.