Hi,

I m not trying to copy to sharepoint. I am just loading a excel file from sharepoint to sql table.

Here in below code i am comparing sharepoint file modified date with the date stored in sql table and taking the file which is latest in sharepoint.

Hope this helps you.

public void Main()

{

// TODO: Add your code here

string Db_Server = Dts.Variables["Db_Server_Name"].Value.ToString();

string Db_Database = Dts.Variables["Db_Database_Name"].Value.ToString();

string SP_Path = Dts.Variables["User::SharePoint_Path"].Value.ToString();

string[] files = System.IO.Directory.GetFiles(SP_Path);

System.IO.FileInfo finf;

DateTime lastDate = new DateTime();

string lastFile = string.Empty;

foreach (string f in files)

{

finf = new System.IO.FileInfo(f);

if (finf.LastWriteTime >= lastDate)

{

lastDate = finf.LastWriteTime;

lastFile = f;

}

}

Dts.Variables["User::File_Name_With_Path"].Value = lastFile;

Dts.Variables["User::File_Load_Date_Time"].Value = lastDate;

//Connect to database table to check latest file is already there or not by comparing file date modified date

string cstr = "server=" + Db_Server + ";database=" + Db_Database + ";Integrated Security=SSPI;";

string sqlquery = @"Select distinct top 1 ISNULL(File_Load_Date, '') AS File_Load_Date

From tbl_Staging_Test

Order By File_Load_Date DESC";

using (SqlConnection conn = new SqlConnection(cstr))

{

try

{

conn.Open();

SqlCommand cmd1 = new SqlCommand(sqlquery, conn);

cmd1.CommandType = CommandType.Text;

DateTime db_Load_Time = Convert.ToDateTime(cmd1.ExecuteScalar());

int result = DateTime.Compare(db_Load_Time, lastDate);

//compare sharepoint date modified date with database value

if (result < 0)

{

Dts.Variables["User::File_Exist"].Value = false;

}

else if (result >= 0)

Dts.Variables["User::File_Exist"].Value = true;

}

catch (SqlException ex)

{

MessageBox.Show(ex.ToString());

}

finally

{

conn.Close();

}

}

Dts.TaskResult = (int)ScriptResults.Success;

}