Script Component Source script

Question

Post reply

Script Component Source script

Oscar_Boots

Old Hand

Points: 362
More actions
July 6, 2019 at 3:09 am

#3660379
Hi Forum,
I have a Script Component used as a Data Source and I want to export those exact results to Destination.
So my .csv table has these fields;
CustomerID
PersonID
StoreID
TerritoryID
AccountNumber
rowguid
ModifiedDate
In the script editor, at the CreateNewOutputRows line, I need to add code that returns the table values to the Destination.
Thanks
```
    public override void CreateNewOutputRows()
    {
        /*
          Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
          For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
        */

        MyOutputBuffer.AddRow();
        MyOutputBuffer.PersonID = MyOutputBuffer.PersonID;  
                   
    }
```
The error I get is "The property or indexer MyOutputBuffer.PersonID cannot be used in this context because it lacks the get accessor"
What do I need to change in this code>
Thanks

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16251 More actions · Answer 1

error is basically stating that you can not populate the destination column with itself

MyOutputBuffer.AddRow();

MyOutputBuffer.PersonID = MyOutputBuffer.PersonID;

should be

MyOutputBuffer.PersonID = my_source_value;

see the following link for some examples

https://docs.microsoft.com/en-us/sql/integration-services/extending-packages-scripting-data-flow-script-component-types/creating-a-source-with-the-script-component?view=sql-server-2017

Oscar_Boots Old Hand Points: 362 More actions · Answer 2

Thanks frederico_fonseca,

So what do I replace the 'MyOutputBuffer.PersonID' value with to get it to output the 'PersonID' value for that row (and then the rest of the rows?)

If it was VB, I would write something like;

MyOutputBuffer.PersonID = PersonID.value

I've read that & other articles but I need some actual script to use.

Thanks

Phil Parkin SSC Guru Points: 247180 More actions · Answer 3

CSV files are not tables. If you are importing from a CSV, you should not need to use a script component source. Instead, use a flat file source.

Oscar_Boots Old Hand Points: 362 More actions · Answer 4

Thanks Phil,

I need to use a Script Component so please can you give any advice on my question as it is stated.

I don't mind what type of source file it (OR what is comprised of etc etc)

I want to use a file of any common source, (.xls, .txt, .csv) and connect it to a Script Component as a source & output the same table data.

That's it.

Thanks if you can help with this.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 5

Oscar_Boots wrote:

Thanks Phil, I need to use a Script Component so please can you give any advice on my question as it is stated. I don't mind what type of source file it (OR what is comprised of etc etc) I want to use a file of any common source, (.xls, .txt, .csv) and connect it to a Script Component as a source & output the same table data. That's it. Thanks if you can help with this.

Presumably you have written some code to read your source file?

Then you'd need something like this, as part of the loop:

MyOutputBuffer.PersonID = [PersonID from source]

Oscar_Boots Old Hand Points: 362 More actions · Answer 6

Thanks,

My code as above;

public override void CreateNewOutputRows()
    {
        /*
          Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
          For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
        */

        MyOutputBuffer.AddRow();
        MyOutputBuffer.PersonID = MyOutputBuffer.PersonID;  
                   
    }

So using your code if the table name is Customer, then your code might read as follows;

MyOutputBuffer.PersonID = [PersonID from Customer]

Is that what you mean?

Phil Parkin SSC Guru Points: 247180 More actions · Answer 7

If this is a script component source, and you are reading from a file, then there is not yet any concept of tables.

All that you can do here is add rows to the script component output buffer.

What you will then need to do is connect the script component to your OLEDB destination.

Once you have done that, you can map the columns coming out of the script component to the columns in your OLEDB destination.

Oscar_Boots Old Hand Points: 362 More actions · Answer 8

Hi Moderator,

Why are you removing my posts when they illustrate a solution that may be useful to others??

Oscar_Boots Old Hand Points: 362 More actions · Answer 9

Thanks but I've found the answer I was after.

This post shows a similar issue & the solution.

Search under Reza Rad & Script-Component-as-Source.

I have adapted it to suit my project.

The script uses an Array and a While Loop on data that is delimited by a pipe symbol '|'

There are some pieces of script that need to be added in the VB script editor, but the main part I was asking about is below;

Hopefully someone else may find this useful.

Public Overrides Sub CreateNewOutputRows()
        '
        ' Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
        ' For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
        '
        Dim lineIndex As Integer = 0
        While (Not sr.EndOfStream)
            Dim line As String = sr.ReadLine()
            Dim columnArray As String() = line.Split(Convert.ToChar("|"))
            If (columnArray.Length > 1) Then
                MyOutputBuffer.AddRow()
                MyOutputBuffer.CustomerID = columnArray(0)
                MyOutputBuffer.PersonID = columnArray(1)
                MyOutputBuffer.StoreID = columnArray(2)
                MyOutputBuffer.TerritoryID = columnArray(3)
                MyOutputBuffer.AccountNumber = columnArray(4)
                MyOutputBuffer.rowguid = columnArray(5)
                MyOutputBuffer.ModifiedDate = columnArray(6)
            End If
            lineIndex = lineIndex + 1
        End While

    End Sub