locked
SSIS SSDT 2012 Script Task Exception Error RRS feed

  • Question

  • SSIS SSDT 2012 Script Task

    3 Read Only Variables defined Package variables

    USer::RunDay, User::RunMonth, User::RunYear

    1 ReadWrite variable defined package variable

    User::RunMonthMinus2

    The Script

    Public Sub Main()
            Dim lYear As Integer
            Dim lMonth As Integer
            Dim sYear As String
            Dim sMonth As String
            Dim sMonthMinus2 As String
            lYear = Dts.Variables("user::RunYear").Value
            lMonth = Dts.Variables("user::RunMonth").Value
            If lMonth <= 2 Then
                lYear = lYear - 1
                If lMonth = 1 Then
                    lMonth = 11
                Else
                    If lMonth = 2 Then
                        lMonth = 12
                    End If
                End If
            Else
                lMonth = lMonth - 2
            End If
            sMonth = lMonth
            sYear = lYear
            sMonthMinus2 = sYear + "-" + sMonth + "-16"
            Dts.Variables("user::RunMonthMinus2").Value = sMonthMinus2
            Dts.TaskResult = ScriptResults.Success
        End Sub

    When I execute it in the debugger I get a pop up error:

    DTD Script Task: Runtime Error

    DTS Script task has encountered an exception in user code:

    Exception has been thrown by target of an invocation.

    In the box is a long list of objects, methods, and arguments.

    at System.RuntimeMethodHandle.InvokeMethod(arguments)

    at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(arguments)

    at System.Reflection.RuntimeMethodInfo.Invoke(arguments)

    at System.RUntimeType.InvokeMembat System.Runtimee(arguments)

    at Microsoft.SQLServer.DTS.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

    I have SQL Server 2012, VIsual Studio 2012 with SSDT 2012 installed.

    Can anyone decipher what the problem is?

    

    Thursday, June 12, 2014 7:42 PM

Answers

  • Hi DaveinMD,

    The issue occurs because you have used the wrong syntax for the Dts.Variables object reference. In VB, we should use Dts.Variables("RunYear").Value other than Dts.Variables("user::RunYear").Value.

    Regards,

    Mike Yin
    TechNet Community Support

    • Marked as answer by Mike Yin Monday, June 23, 2014 5:01 PM
    Thursday, June 19, 2014 2:51 AM

All replies

  • Hi ,

      From the error message couldn't infer any details. But i would like to recommend you to place a break point in the code and then step through each line of code to get the exact error line and message.


    Best Regards Sorna

    Friday, June 13, 2014 10:01 AM
  • What are the datatypes of variables used?

    Also why are you passing USer::RunDay to script? you're not using it anywhere inside


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, June 13, 2014 10:55 AM
  • I would recommend to use variable casting

      lYear = (int)Dts.Variables("user::RunYear").Value
            lMonth = (int) Dts.Variables("user::RunMonth").Value


    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, June 13, 2014 11:12 AM
  • Hi DaveinMD,

    The issue occurs because you have used the wrong syntax for the Dts.Variables object reference. In VB, we should use Dts.Variables("RunYear").Value other than Dts.Variables("user::RunYear").Value.

    Regards,

    Mike Yin
    TechNet Community Support

    • Marked as answer by Mike Yin Monday, June 23, 2014 5:01 PM
    Thursday, June 19, 2014 2:51 AM
  • Dropped the "user::" from the DTS variables and got the same result.
    Friday, June 20, 2014 5:08 PM