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

SSIS Question Help... Expand / Collapse
Author
Message
Posted Wednesday, June 25, 2014 4:00 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 216, Visits: 786
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=http://sqlsaga.com/ssis/how-to-display-the-value-of-a-variable-using-a-script-task-in-ssis/][/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()
Post #1586177
Posted Wednesday, June 25, 2014 4:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 6,249, Visits: 7,404
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=http://sqlsaga.com/ssis/how-to-display-the-value-of-a-variable-using-a-script-task-in-ssis/][/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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1586181
Posted Wednesday, June 25, 2014 4:40 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:42 PM
Points: 1,787, Visits: 5,695
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


  • MMGrid Addin
  • MMNose Addin


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

  • Post #1586190
    Posted Wednesday, June 25, 2014 9:24 PM
    SSC Veteran

    SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

    Group: General Forum Members
    Last Login: Yesterday @ 3:08 PM
    Points: 216, Visits: 786
    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
    Post #1586215
    Posted Thursday, June 26, 2014 2:22 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 4:42 PM
    Points: 1,787, Visits: 5,695
    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


  • MMGrid Addin
  • MMNose Addin


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

  • Post #1586283
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse