SSIS 讀取Table設定，刪除檔案或目錄
１。先在資料庫建立Table
檔名: DeleteFile 目錄定時清檔設定檔
|
欄位名稱 |
欄位中文說明 |
欄位型態 |
欄位長度 |
備註 |
|
ShareFolder_Name |
共用檔案目錄名稱 |
varchar |
100 |
|
|
KeepDay |
保留天數 |
int |
||
|
Skip_SubFolder |
忽略次目錄 |
char |
1 |
Y:表示skip此share folder下的次目錄，僅比對檔案 N:表示比對範圍包含次目錄及此目錄的檔案，次目錄只比對該次目錄的日期，若超過期限則將該次目錄刪除 |
Primary Key：ShareFolder_Name
CREATE TABLE [dbo].[DeleteFile](
[ShareFolder_Name] [varchar](100) NOT NULL,
[KeepDay] [int] NOT NULL,
[Skip_SubFolder] [char](1) NOT NULL,
)
GO
2.SSIS新增指定碼工作－＞編輯指令碼
public void Main()
{
_ConString = ((SqlConnection)(Dts.Connections["ADO.NTE.ACS"].AcquireConnection(Dts.Transaction) as SqlConnection)).ConnectionString;
using (SqlConnection cn = new SqlConnection(_ConString))
{
string ShareFolder_Name = "", Skip_SubFolder = "";
int KeepDay = 0;
//讀取 DeleteFile 檔批次刪除設定
string strSQL = "select ShareFolder_Name,KeepDay,Skip_SubFolder "
+ " from dbo.DeleteFile with(nolock) ";
cn.Open();
SqlCommand com = new SqlCommand(strSQL, cn);
SqlDataReader reader = com.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
ShareFolder_Name = reader["ShareFolder_Name"].ToString(); //共用檔案目錄名稱
KeepDay = Convert.ToInt16(reader["KeepDay"].ToString()); //保留天數
Skip_SubFolder = reader["Skip_SubFolder"].ToString(); //忽略次目錄
if (Directory.Exists(ShareFolder_Name))
{
//刪除檔案、目錄
DeleteFile(ShareFolder_Name, KeepDay, Skip_SubFolder);
//刪除目錄
if (Skip_SubFolder == "N")//不忽略次目錄
{
DeleteDirectory(ShareFolder_Name, KeepDay, Skip_SubFolder);
}
}
}
}
reader.Close();
}
Dts.TaskResult = (int)ScriptResults.Success;
}
private void DeleteFile(string ShareFolder_Name, int KeepDay, string Skip_SubFolder)
{
#region "刪除檔案"
//傳回指定目錄中所有的檔名
string[] FileList = Directory.GetFiles(ShareFolder_Name);
//刪除檔案
foreach (string f in FileList)
{
try
{
System.IO.FileInfo fi = new System.IO.FileInfo(f);
//取得檔案最後修改日期與今天，日期之間的「天數」
DateTime fi_LastWriteTime = File.GetLastWriteTime(f.ToString());
double DiffDay = new TimeSpan(DateTime.Now.Ticks - fi_LastWriteTime.Ticks ).TotalDays;
//若超過保留天數，則刪除
if (DiffDay > KeepDay)
{
fi.Delete();
}
}
catch (System.IO.IOException e)
{
Console.WriteLine(e.Message);
}
}
#endregion
}
private void DeleteDirectory(string ShareFolder_Name, int KeepDay, string Skip_SubFolder)
{
#region "刪除目錄"
//取得指定目錄中的所有子目錄名稱
string[] SubFolderList = Directory.GetDirectories(ShareFolder_Name);
// 刪除目錄
foreach (string s in SubFolderList)
{
if (System.IO.Directory.Exists(s))
{
try
{
System.IO.DirectoryInfo di = new System.IO.DirectoryInfo(s);
//取得目錄最後修改日期與今天，日期之間的「天數」
DateTime Di_GetLastWriteTime = Directory.GetLastWriteTime(s.ToString());
double DiffDay = new TimeSpan(DateTime.Now.Ticks - Di_GetLastWriteTime.Ticks).TotalDays;
//若超過保留天數，則刪除
if (DiffDay > KeepDay)
{
di.Delete(true);
}
}
catch (System.IO.IOException e)
{
Console.WriteLine(e.Message);
}
}
}
#endregion
}
