.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Delete files older than 3 days using SSIS tasks

Posted By:      Posted Date: October 08, 2010    Points: 0   Category :Sql Server

I want to delete all files in a given folder that are older than 3 days from today's date.

I tried using a "Foreach Loop container"  with a "File System" task inside it but found I couldn't access any file properties such as the file creation date. Am I using the wrong task for this job?






P.S. History of the problem:

I used the sql 2005 Database maintenance program to setup our database backup jobs. One day I noticed that the free space on the drive where we keep the dumps had grown small. I found that we had at least 4 weeks of dumps in there, not the 3 days I wanted to keep!  I looked but could not find the "delete file" option in the SSIS package generated by the Database Maintenance Wizard. No wonder the files were piling up.

View Complete Post

More Related Resource Links

Delete Folders which is older than some specific days

Hi All, I have scenerio where we are taking daily backup files as datebasename_<today'sdate>.Dat_bak under databaseName_<today's date> folder under root database_backup folder.  So I need to delete folders which are older than 10 days where it automatically deletes backup files also.  Can anyone tell me how to delete folders which are older than some specific days?  Btw, we are using sql server 2005 enterprise edition Thanks in advance Anand R  

Which task would use to copy, move or delete files in SSIS

can any one help me for this question any link of examples for the same highly appreciable
Regards, Amitesh Srivastva

Migrate DB + files outside DB into SharePoint using SSIS and scripts


Hello, just wanted to ask a question about SSIS and SharePoint, any input will be greatly appreciated.... did search the forum but could not find anything....

So basically we have a SQL Server DB with the metadata ONLY, WITHOUT the related files(doc/ppt/pdf..etc) which should've been in the DB and they are on a separate web server inside folders. Each row in the DB has a key (column) which matches the related folder's name, so for row with ID #112, there's a folder named ID112 on the web server with the relevant files inside. There could be more than 8+ files for one folder.  

Now, I think it is possible but just wanted to double check, would it be possible to use SSIS and some kind of a script to get the metadata inside the SQL DB AND the related files inside those folders, combine & relate them and upload them into SharePoint as a custom list with file attachments? would they display the attachment normally (like the paperclip icon in a sharepoint list...etc) I wonder if anyone has tried this...

Any guidance, thoughts, suggestions for other solutions, inputs will be great!!! Thank you!!



Receive/Delete Multiple FTP Files based on condition using SCRIPT(VB) TASK

I am trying to receive and delete multiple FTP files from a remote FTP server using Script task Below is the code   Dim FTPConnMgr As ConnectionManager FTPConnMgr = Dts.Connections("FTP Connection Manager") Dim FTPClientConn As FtpClientConnection = New FtpClientConnection(FTPConnMgr.AcquireConnection(Nothing)) Dim FileTimeStampNew As String = "20100913021807" Dim remoteFileNames(0) As String remoteFileNames(0) = Dts.Variables("FtpRemoteDirectory").Value & "*" & FileTimeStampNew & ".*" 'Below hardcoded FileName works good. But the problem is there are lots of file in the FTP folder that I dont want to receive 'remoteFileNames(0) = Dts.Variables("FtpRemoteDirectory").Value & "Company_alpha_Full_20100913021807.xml" Dim localPath As String = Dts.Variables("FtpLocalDirectory").Value FTPClientConn.Connect() FTPClientConn.ReceiveFiles(remoteFileNames, localPath, False, False) FTPClientConn.Close() Dts.TaskResult = ScriptResults.Success End Sub End Class If i specifically mention the RemotefileNames this works fine but, when I say *.* it executes succefully but doesn't receives the file. Please advice how to receive multiple file based on a variable BR, AWM

Problem importing text files with binary zeros (0x00) via SSIS(SQL2005). It is all fine when using D

Hi.   There is a "text" file generated by mainframe and it has to be uploaded to SQL Server. I've reproduced the situation with smaller sample. Let the file look like following: A17     123.17  first row          BB29    493.19  second             ZZ3     18947.1 third row is longer And in hex format: 00:  41 31 37 20 20 20 20 20 ? 31 32 33 2E 31 37 20 20  A17     123.17  10:  66 69 72 73 74 20 72 6F ? 77 0D 0A 42 42 32 39 20  first row??BB29 20:  20 00 20 34 39 33 2E 31 ? 39 20 20 73 65 63 6F 6E     493.19  secon30:  64 0D 0A 5A 5A 33 20 20 ? 20 20 20 31 38 39 34 37  d??ZZ3     1894740:  2E 31 20 74 68 69 72 64 ? 20 72 6F 77 00 69 73 20  .1 third row is 50:  6C 6F 6E 67 65 72       ?                          longer          I wrote "text" in quotes because sctrictly it is not pure text file - non-text binary zeros (0x00) happen sometimes instead of spaces (0x20).   The table is: CREATE TABLE eng ( src varchar (512) )   When i upload this file into SQL2000 using DTS or Import wizard, the table contains: select src, substring(src,9,8), len(src) from eng <               src                ><substr>             <len> A17     123.17  first row           123.17                  25BB29                                493.19                  22ZZ3     18947.1 third row           18947.1                 35   As one can see, everything was importe

How to create a SSIS package to import records form SQl Server 2008 tables to text files

I am a newbie to SSIS and would like to create a package to accomplish the task referenced in the above title. Will appreciate any links and pointers in the right direction.   Thanksakoranteng

SSIS Package not logging all tasks when run as a job

We have few packages where we have enabled logging to msdb (sysssislog). And events selected are OnError, OnExecStatusChanged, OnPostExecute, OnPreExecute, OnTaskFailed, OnWarning. When running this package from BIDS solution, all the event log to the table. However, when it is deployed and run as a job only some of the events get logged to msdb. The package however does run correctly and the data is loaded fine. What we want is that every time a package runs, we should be able to see that each of the task has succeded or failed (like we used to in DTS) Could anyone advise on what the issue seems to be?

SSIS FTP Task works, but the files aren't there

I've created an FTP Task in my SSIS package, and when I run it in BIDS, it turns green and reports success, but when I look at the destintation folder, the file that should have been sent isn't there. I can send the same file to the same destination using the same credentials using WSFTP and it gets there. How can one begin to debug such a thing?

Use SSIS to read files in SharePoint library

Hi,I'm trying to use SSIS to read data from an Excel file in a SharePoint document library and I am having some problems with it.First I tried to use the Excel file as a source file in an Excel Source task which worked well in BIDS but not on the SQL server. ("CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER")Next I tried to read data from an Excel file on the SQL server file system which worked well on the SQL server.Next I tried to use a File System Task to copy the Excel file to the SQL server file system. This also worked well in BIDS but not on the SQL server. ("The file name \\sharepoint\mylibrary\myfile.xls specified in the connection was not valid.")I have also tried to let the SQL server agent run on a domain account that is set up as a reader on the SharePoint document library without success.Where am I going wrong?

Creating Multiple Excel files Using SSIS

Hello Friends,  I have to create multiple excel files(Per Year eg 2008 , 2009, 2010....) from datatable using SSIS  For that i have to use one template excel file that is already created on some location.  Any help would be appreciated.  Thanks Thanks

Uploading files produces older versiones

Hi,   I have a sharepoint document library in which i have no versionin enabled. This is my use case: 1) 10/2/2010 i upload a document called test.doc 2) 11/2/2010 i upload a modified document called test.doc 3) the contents in the file are those of 10/2/2010 4) mmm ok. I'll delete the file, emptied my trash bin. and try to upload test.doc (11/2/2010) again 5) the contents in the file are those of 10/2/2010!!!!!!!!!   I'm sure is not some local cache because i try uploading it from different machines and also open it from different machines. Same results. Why is this happening? the only workaroud is to change the name of the file: test1.doc, test2.doc. So, basically i have my versioning system for Sharepoint. It is really getting on my nerves.   Any help will be highly appreciated.   Thanks, Luis

Script tasks in SSIS 2005



Can anybody tell me if we can use the C#.net while encoding in the script tasks components in BI 2005?

Delete Old Backup Files that are saved in .RAR format



I have configured my SQL Server 2005 to take backups and I have another program running to compress these files to .RAR extension to save space.

I have a Maintenance Cleanup Task also configured to delete old .RAR files but it does not work.  I have specified the extension to look for to be RAR and the job runs without error but no files are deleted.

Is it that only BAK and TRN files are deleted using Maintenance Cleanup Task?

Retaining Errorlog files for only 90 days


Hi Experts ,

I have a requirement to retain the errorlogs in such a way that at any point of time it contains only 90 days worth of logs .But

-> We need not to try to drop ERRORLOG <-- the very first one

for example if you have 7 logs and one of the logs have a timestamp older thna 90 days from current date , then it would be deleted ...

I am applying my mind , but if its already there then I need not to reinvent the wheel ..


Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/

Deployment Of SSIS With Config Files


I went through other threads and links on this subject. Still one thing which concerns me is the config files.

My current folder path is C:\Karunakaran\folder1\folder2\

Under folder2 I have the following files

4 dtsx files
1 .database file
1 .dtproj file
1 .dtproj.user file
1 .sln file
1 .suo file

Config folder ( C:\Karunakaran\folder1\folder2\Config\)

Based on some of the threads, when I enabled package configuration, I changed the path from
C:\Karunakaran\folder1\folder2\Config\common.dtsconfig -> common.dtsconfig

Once I did this, when I open the project I get a dozen of warning similar to the one below

Warning loading <package1>.dtsx: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed.

What am I doing wrong here? If I have to deploy this to another box, what should I do to ensure that nothing breaks once its copied / installed?


SSIS Script task that FTP's files


I could not find the exact details on how to create a SSIS script that would ftp files on these forums, so I am adding my code to help save time for anyone else that might be wanting to do something similar.  Here is the VB code for my script task to FTP files (hope this helps someone):

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()


'Create the connection to the ftp server

how to delete files from folder in webserver


hi i m using fileupload concept and saving the files to a folder download.hw can i check the files names saved in that folder while site is hosted on webserver.is there any way to delete some of files from that folder runtime. 

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend