SSIS 包:无法将 System.Object 转换为 List(Of String) 以存储在包变量中? [英] SSIS Package: Can't convert System.Object to List(Of String) for storage in Package variable?

问题描述

我有一个 SSIS 包，可以根据特定条件向用户发送电子邮件，现在我想跟踪以这种方式通知的所有用户，并将摘要发送给我们的一个团队.我想将通知的用户名存储在变量中作为 List(Of String)，但我似乎无法在脚本任务中执行此操作，这是我的代码:

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

变量 Users 设置为类型 Object，我知道您可以在其中存储复杂类型，因为我已经在其中存储了 ADO.NET 记录集，但我从未通过脚本任务在代码中这样做过.我也试过 CType，它给出了同样的错误.我做错了什么?

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.

谢谢.

推荐答案

问题是 Users 变量从未设置为 List(Of String) - 但它不是 Nothing，也可以.相反，SSIS 已将其初始化为 System.Object，当然不能 转换为 List(Of String).

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

幸运的是，您需要做的就是在某个脚本任务中将用户初始化为 List(Of String).

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

这篇关于SSIS 包:无法将 System.Object 转换为 List(Of String) 以存储在包变量中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！