August 15, 2011 at 3:22 pm
Hi
I wrote the following script to get the standard xml format from the xml variable. the xml variable holds the result set from a sql command.
/*
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
namespace ST_a883e533b47f43b6863c72147afa508b.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
/*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.
To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
To open Help, press F1.
*/
public void Main()
{
string xmlSummary = Dts.Variables["XMLResultSet"].Value.ToString().Replace("<ROOT>", "").Replace("</ROOT>", "");
Dts.Variables["Validation_XML"].Value = string.Format("<?xml version=\"1.0\" encoding=\"utf-8\"?><Report>{0}{1}</Report>", xmlSummary);
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}
I'm getting the following error
Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.
at System.Text.StringBuilder.AppendFormat(IFormatProvider provider, String format, Object[] args)
at System.String.Format(IFormatProvider provider, String format, Object[] args)
at System.String.Format(String format, Object arg0)
at ST_a883e533b47f43b6863c72147afa508b.csproj.ScriptMain.Main()
--- 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()
Can anyone please help me with this.
August 18, 2011 at 2:14 pm
What are you trying to do with this line:
Dts.Variables["Validation_XML"].Value = string.Format("<?xml version=\"1.0\" encoding=\"utf-8\"?><Report>{0}{1}</Report>", xmlSummary);
You are probably getting the error because {1} is not a valid format string.
Mwise
August 19, 2011 at 8:14 am
Thanks! for your reply.
I have a sql task which executes a query and stores the result set in a xml result set. i have to send an email of this result so i have to tranform the xml result set to html. before tranforming to html I want to covert this xml to a valid xml format that is the reason i'm trying to write a script task for this.
August 19, 2011 at 9:11 am
Are you trying to wrap the values in xmlSummary with "<?xml version=\"1.0\" encoding=\"utf-8\"?><Report>" and "</Report>"? You are getting an error is because {1} is not a format string - that that out and see what you get. If youou need to build your results differently, try concat.
Edit: What is your SQL command and results look like?
MWise
August 29, 2011 at 8:16 am
Thanks! lewis..... that helped me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply