Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


uri format error in ssis 2008 R2 script task


uri format error in ssis 2008 R2 script task

Author
Message
rajn.knit07
rajn.knit07
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 198
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
Erland Sommarskog
Erland Sommarskog
SSC Eights!
SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)

Group: General Forum Members
Points: 991 Visits: 866
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
applebynick
applebynick
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 135
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 ?
rajn.knit07
rajn.knit07
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 198
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;
}
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search