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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy