"Value of type 'Integer' cannot be converted to Output0Buffer" error within DataFlowTask, in Visual Studio Professional 2017

huangapple go评论55阅读模式
英文:

"Value of type 'Integer' cannot be converted to Output0Buffer" error within DataFlowTask, in Visual Studio Professional 2017

问题

Here's the translated portion of your text:

免责声明:我对Visual Studio和SQL都不太熟悉,所以我会尽量解释得清楚。请耐心等待 "Value of type 'Integer' cannot be converted to Output0Buffer" error within DataFlowTask, in Visual Studio Professional 2017
第二个声明:我绝不希望以任何方式更改源数据库和目标数据库中的表格。

我正在尝试创建一个SSIS包,在SQL Server 2016中运行,通过从Visual Studio Pro 2017部署到SSISDB目录。 SQL源和目标中的表如下:

源:3个表,全部包含在同一个数据库中。其中一个已经具有所需的列/行/数据,因此不需要对其进行任何更改。其他两个在设计上几乎相同,它们是问题所在。我需要将它们的列解除枢轴,以便它们成为行,并在数据流任务中创建的ID列中插入一些ID,使用Derived Column转换。稍后会详细介绍这些ID。

目标:1个表,有自己的列。

数据流任务如下,从源到目标(我将仅关注发生问题的流分支,以保持简洁和简明):

  1. ADO.Net源。它使用查询选择所需的数据。这个查询有效。如果您需要它,我可以发布它。
  2. 派生列。在这里,我创建了ID列,表达式为“SameColumn”,数据类型为DT_WSTR,长度为10。
  3. 数据转换。只是匹配源和目标的数据类型。
  4. 解除枢轴。在这里,我将列解除为行。目标列称为Measure,我还使用Pivot Key Values列作为行的名称。
  5. 脚本组件。问题出在这里。我将稍后发布脚本。我创建了输出列,添加了输入列,使它们可读写,并将Output 0的SynchronousInputID属性设置为0,以便创建Output0Buffer类。我在连接管理器中创建了与源的连接,并插入了脚本。
  6. 合并全部。在这里,我将目标中的列与脚本组件中的列进行匹配。
  7. ADO.Net目标。我映射了列。

所以,脚本本身。它看起来像这样(注意:它是在ChatGPT的帮助下创建的):

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute>
<CLSCompliant(False)>

Public Class ScriptMain
    Inherits UserComponent

    Private outputBuffer As Output0Buffer

    Public Overrides Sub PreExecute()
        MyBase.PreExecute()
        outputBuffer = Me.ComponentMetaData.OutputCollection(0).Buffer
    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        Dim id As String = ""

        Select Case Row.PivotKeyValue.ToString()
            Case "Column 1"
                id = "Value1"
            Case "Column 2"
                id = "Value 2"
            Case "Column 3"
                id = "Value 3"

            '以此类推;总共有14列和值

        End Select

        outputBuffer.ID = id
        outputBuffer.Measure = Row.Measure
        outputBuffer.Date = Row.CopyofDate
        outputBuffer.Time = Row.CopyofTime
        outputBuffer.PivotKeyValue = Row.PivotKeyValue

    End Sub

End Class

希望这能帮助你解决问题。如果需要更多帮助,请随时提问。

英文:

Disclaimer: I am new to Visual Studio and SQL, so I`ll try to explain everything as best as I can. Please be patient "Value of type 'Integer' cannot be converted to Output0Buffer" error within DataFlowTask, in Visual Studio Professional 2017
Second disclaimer: I DO NOT want to alter in any way the tables in the source and destination DBs.

I am trying to create and SSIS package to run in SQL Server 2016, by deploying it from Visual Studio Pro 2017 in the SSISDB catalog. The tables in the SQL source and destination are as follows:

Source: 3 tables, all of them contained in the same DB. one of them already has the necessary columns/rows/data in it, so no alteration needs to be made to it. the other two are pretty much the same, design-wise, and they are the problem. I need to unpivot their columns, in order for them to become rows and, also, insert some IDs in an ID column I created within the Data Flow Task, using a Derived Column transform. More on these IDs later.

Destination: 1 table, with it`s own columns.

The Data Flow task is as follows, from source to destination (I will focus only on the flow branch the problem occurs, just to keep things to the point and concise):

  1. ADO.Net source. It uses a query to select the necessary data. This query work. If you need it, I can post it.
  2. Derived Column. Here, I created the ID column, with the expression "SameColumn", the data type being DT_WSTR, with the length of 10.
  3. Data Conversion. Just to match the data types of the source and destination.
  4. Unpivot. Here, I unpivoted the columns into rows. The destination column is called Measure and I also use the Pivot Key Values column as names for the rows.
  5. Script Component. Here the problem lies. I will post the script later. I created the output columns, added the input columns, made them ReadWrite, and set the SynchronousInputID property of Output 0, to 0, so that the Output0Buffer class could be created. I created the connection to the source, in Connection Manager and inserted the script.
  6. Union All. Here, I matched the columns from the destination to those from the script component.
  7. ADO.Net destination. I mapped the columns.

So, the script itself. It looks like this (note: it has been created with the help of ChatGPT):

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute>
<CLSCompliant(False)>

Public Class ScriptMain
    Inherits UserComponent

    Private outputBuffer As Output0Buffer

    Public Overrides Sub PreExecute()
        MyBase.PreExecute()
        outputBuffer = Me.ComponentMetaData.OutputCollection(0).Buffer
    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        Dim id As String = ""

        Select Case Row.PivotKeyValue.ToString()
            Case "Column 1"
                id = "Value1"
            Case "Column 2"
                id = "Value 2"
            Case "Column 3"
                id = "Value 3"
            
            'and so on; there are 14 columns and values in total

        End Select

        outputBuffer.ID = id
        outputBuffer.Measure = Row.Measure
        outputBuffer.Date = Row.CopyofDate
        outputBuffer.Time = Row.CopyofTime
        outputBuffer.PivotKeyValue = Row.PivotKeyValue

    End Sub

End Class

So, basically, what I want to do with this script, is to hardcode an ID (value 1, value 2, value 3 etc.) depending on the names (column 1, column 2, column 3 etc.) from the PivotKeyValue column.

There is an error that keeps popping up, that states:

> "Value of type 'Integer' cannot be converted to 'Output0Buffer'"

It refers to this line of the script:

outputBuffer = Me.ComponentMetaData.OutputCollection(0).Buffer

From what I understand, the PreExecute Sub is necessary to initiate the output. Otherwise, if I do not include it, the script builds successfully, but it returns an exception when debugging the Data Flow Task, stating: "Object reference not set to an instance of an object". Also, if I remove the outputBuffer.[...] lines, after the End Select, the code builds successfully and the debugging can be completed with no errors, but the Script Component does not output anything.

Any help and explanation would be greatly appreciated. Sorry if I provided too many details, I figured "the more, the better" in this case.

答案1

得分: 0

我弄清楚了。正确的代码如下:

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute>
<CLSCompliant(False)>

Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        Output0Buffer.AddRow()

        Dim id As String = ""

        Select Case Row.PivotKeyValue.ToString()
            Case "Column 1"
                id = "Value1"
            Case "Column 2"
                id = "Value 2"
            Case "Column 3"
                id = "Value 3"

            'and so on; there are 14 columns and values in total

        End Select

        Output0Buffer.ID = id
        Output0Buffer.Measure = Row.Measure
        Output0Buffer.Date = Row.CopyofDate
        Output0Buffer.Time = Row.CopyofTime
        Output0Buffer.PivotKeyValue = Row.PivotKeyValue

    End Sub

End Class
英文:

I figured it out. The correct code is as follows:

&lt;Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute&gt;
&lt;CLSCompliant(False)&gt;

Public Class ScriptMain
Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    Output0Buffer.AddRow()

    Dim id As String = &quot;&quot;

    Select Case Row.PivotKeyValue.ToString()
        Case &quot;Column 1&quot;
            id = &quot;Value1&quot;
        Case &quot;Column 2&quot;
            id = &quot;Value 2&quot;
        Case &quot;Column 3&quot;
            id = &quot;Value 3&quot;
        
        &#39;and so on; there are 14 columns and values in total

    End Select

    Output0Buffer.ID = id
    Output0Buffer.Measure = Row.Measure
    Output0Buffer.Date = Row.CopyofDate
    Output0Buffer.Time = Row.CopyofTime
    Output0Buffer.PivotKeyValue = Row.PivotKeyValue

End Sub

End Class

huangapple
  • 本文由 发表于 2023年4月19日 16:08:19
  • 转载请务必保留本文链接:https://go.coder-hub.com/76052113.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定