September 2, 2013 at 5:17 am
Hi,
I need to get the latest file from sharepoint. To get the file names in array, I am using the below code in script task
string[] files = System.IO.Directory.GetFiles(@"my sharepoint link");
But this line is giving me error. Yhe error is below:
Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.ArgumentException: URI formats are not supported.
at System.IO.Path.NormalizePathFast(String path, Boolean fullCheck)
at System.IO.Path.NormalizePath(String path, Boolean fullCheck)
at System.IO.Path.GetFullPathInternal(String path)
at System.IO.Directory.InternalGetFileDirectoryNames(String path, String userPathOriginal, String searchPattern, Boolean includeFiles, Boolean includeDirs, SearchOption searchOption)
at System.IO.Directory.GetFiles(String path, String searchPattern, SearchOption searchOption)
at System.IO.Directory.GetFiles(String path)
at ST_eebeeafd4d024485b0d5d21101efb47b.csproj.ScriptMain.Main() in <some path>:line 47
--- End of inner exception stack trace ---
at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
The same code is running fine if I give local system path or some network disk path. I can understand from the above error that I need to use some reference to system.web, but I need help here.
Please help me in resolving this issue.
Thanks
Rajneesh
September 2, 2013 at 3:48 pm
You are at a site which is devoted to SQL Server, but it is not apparent where SQL Server comes into play here. Maybe it is a Sharepoint forum you need. Or am I missing something?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
December 2, 2014 at 6:45 am
Just had this issue in a SSIS file system task using 2008 R2 and trying to copy to sharepoint.
Anyone ever managed to get this progressed ?
December 2, 2014 at 7:56 am
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;
}
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply