Disclaimer: I am new to Visual Studio and SQL, so I`ll try to explain everything as best as I can. Please be patient :) 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.