SSIS Package: Can't convert System.Object to L

2019-08-01 04:23发布

问题:

I've got an SSIS package that emails users based on a certain criteria, I now want to track all the users that were notified in this manner and send a summary to one of our teams. I was wanting to store the usersname of those notified in a variable as a List(Of String), but I can't seem to do this in a a Script Task, here is my code:

Public Sub Main()
    Dim vars As Variables
    If Dts.Variables("Users").Value Is Nothing Then
        Dim users As New List(Of String)
        users.Add("Enrollees: \n")
        Dts.VariableDispenser.LockOneForWrite("Users", vars)
        vars.Item("Users").Value = users
    End If

    'errors out on this line
    Dim userList As List(Of String) = DirectCast(Dts.Variables("Users").Value, List(Of String))
    'errors out on this line

    userList.Add(Dts.Variables("FirstName").Value.ToString() & " " & Dts.Variables("LastName").Value.ToString() & " (" & Dts.Variables("Email").Value.ToString() & " )\n")
    Dts.VariableDispenser.LockOneForWrite("Users", vars)
    vars.Item("Users").Value = userList

    Dts.TaskResult = Dts.Results.Success
End Sub

The variable Users is set to type Object, and I know you can store complex types in there because I've stored ADO.NET record sets in them, but I've never done so in code via a script task. I've tried CType too and it gives the same error. What am I doing wrong?

Also I know I could just store the variables as comma separated in a string and split it out later, but I want to know why this isn't working and stick to a more object based approach.

Thanks.

回答1:

The problem is that the Users variable has never been set to a List(Of String) - but it's not Nothing, either. Instead, SSIS has helpfully initialized it to a System.Object, which of course can't be cast to a List(Of String).

Public Sub Main()
    Dim msg As String
    If Dts.Variables("Users").Value Is Nothing Then
        msg = "Dts.Variables(""Users"").Value is Nothing"
    Else
        msg = "Dts.Variables(""Users"").Value is an object of type " + Dts.Variables("Users").Value.GetType().FullName
    End If
    Dts.Events.FireInformation(0, "Main", msg, "", 0, True) ' Information: Dts.Variables("Users").Value is an object of type System.Object

    ' rest of your code follows

Fortunately, all you need to do is initialize Users to a List(Of String) in a Script task somewhere.



标签: .net ssis