Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

uri format error in ssis 2008 R2 script task Expand / Collapse
Author
Message
Posted Monday, September 2, 2013 5:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 7:55 AM
Points: 43, Visits: 155
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
Post #1490588
Posted Monday, September 2, 2013 3:48 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:02 AM
Points: 823, Visits: 753
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?

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1490697
Posted Tuesday, December 2, 2014 6:45 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 2:21 AM
Points: 93, Visits: 118
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 ?
Post #1640684
Posted Tuesday, December 2, 2014 7:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 7:55 AM
Points: 43, Visits: 155
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;
}
Post #1640729
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse