SSIS Question Help...

  • Hi Guys,

    What I am trying to do, Extract the data from SQL table and Insert in Email Body and email to user. I got good article on Internet, I follow all steps as it is, but still I am getting error.

    Here is the link

    [/url]

    But I am getting Error: here is my error. Please guide me how I can fix this error.

    Thank in advance!

    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 ST_7f59d09774914001b60a99a90809d5c5.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.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 Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

  • rocky_498 (6/25/2014)


    Hi Guys,

    What I am trying to do, Extract the data from SQL table and Insert in Email Body and email to user. I got good article on Internet, I follow all steps as it is, but still I am getting error.

    Here is the link

    [/url]

    Not a bad article, but that's how to send information to a procedure and redirect so you can see it during testing to a msgbox. I would have expected a Send Mail task here in a For Each Loop from a recordset object.

    But I am getting Error: here is my error. Please guide me how I can fix this error.

    Thank in advance!

    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)

    We're going to need a lot more specifics about what you're doing here. As in, what your script object code looks like. That's not a SSIS error, nor a SQL error. It's a .NET (C# I believe) error for arrays.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • That error message usually relates to a StringBuilder.AppendFormat method being called with mismatching parameters.

    By this, I am talking about a format string like this "Hello {1}" being passed one parameter, e.g.

    myStringBuilder.AppendFormat("Hello {1}", WORLD);

    when it should be

    myStringBuilder.AppendFormat("Hello {0}", WORLD);

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • MM,

    You are right, I am not good in .Net or c#, Here is the code that I am using. If you could help me, That would be big help for me.

    Thank You.

    /*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_7f59d09774914001b60a99a90809d5c5.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

    public void Main()

    {

    Variables varCollection = null;

    string header = string.Empty;

    string message = string.Empty;

    Dts.VariableDispenser.LockForWrite("User::EmailMessage");

    Dts.VariableDispenser.LockForWrite("User::ItemId");

    Dts.VariableDispenser.LockForWrite("User::ItemName");

    Dts.VariableDispenser.LockForWrite("User::ItemType");

    Dts.VariableDispenser.GetVariables(ref varCollection);

    //Set the header message for the query result

    if (varCollection["User::EmailMessage"].Value == string.Empty)

    {

    header = "Execute SQL task output sent using Send Email Task in SSIS:";

    header += string.Format("{0}\t{1}\t\t\t{2}", "Item number", "Item name", "Item type");

    varCollection["User::EmailMessage"].Value = header;

    }

    //Format the query result with tab delimiters

    message = string.Format("{0}\t{1}\t{2}",

    varCollection["User::ItemId"].Value,

    varCollection["User::ItemName"].Value,

    varCollection["User::ItemType"].Value);

    varCollection["User::EmailMessage"].Value = varCollection["User::EmailMessage"].Value + message;

    Dts.TaskResult = (int)ScriptResults.Success;

    }

    }

    }

    Here is the article/ Web Link that I am copying. (Great link)

    http://stackoverflow.com/questions/6439663/how-to-send-the-records-from-a-table-in-an-e-mail-body-using-ssis-package

  • Hi, Sorry I can't see what is wrong there, so I suggest you place a breakpoint at the start of "main" and step through the code until it throws an error, then at least you will know the line it is failing on.

    http://msdn.microsoft.com/en-us/library/ms140033(v=sql.100).aspx

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

    Viewing 5 posts - 1 through 4 (of 4 total)

    You must be logged in to reply to this topic. Login to reply