How to write to a Variable in script component

Question

I want to write a value to a variable in package using script component using C#.

presently i am using these code:

public class ScriptMain : UserComponent
{
string s, r, m;   
public override void PreExecute()
{
    base.PreExecute();

}

public override void PostExecute()
{
    base.PostExecute();

    Variables.SNumber = s;
    Variables.RNumber = r;
    Variables.MNumber = m;       
   }

public override void Input0_ProcessInputRow(Input0Buffer Row)
{


    if (Row.Col1.Equals("MyName"))
    {

       s = Row.Column4.Substring(122, 5).Trim();
       r  = Row.Column8.Substring(5, 14).Trim();
       m = Row.Column8.Substring(66, 4).Trim() + "." + Row.Column8.Substring(70,  
              2).Trim();   
   }     

}

}

SNumber,Mnumber and RNumber are my global variables in the packages.After the script component iam using derived column to assign the values. But the variables has no value.Plz help me. Thanks

cairnz · Accepted Answer · 2011-03-31 19:20:20Z

4

It seems this is a script compoent in a data flow task. You cannot use variables in this sense, unfortunately. You'll either have to create columns that keep these values for a row-by-row basis, or you'll have to get creative.

One way here is to pipe your data flow to a recordset destination, create a variable of Object type, and variables to hold each column you need in this data flow. You can then use a Foreach Loop on the Recordset destination that's now stored in the Object variable. If you have something that needs to do stuff on a row-by-row basis that isn't a data flow per se, you can achieve this now, as the data flow task is called once for each row. (This is cumbersome, and more suitable for other needs than yours)

You should be able to achieve what you want to do above in a Derived Column.

Reference on Script component in Data Flow : http://msdn.microsoft.com/en-us/library/aa337079.aspx

answered Mar 31, 2011 at 19:20

cairnz

3,9571 gold badge21 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

brian Over a year ago

+1 cairnz. For Data Flow Tasks, I try to keep the tasks in between the source and destination to a minimum, as they are memory hogs. A derived column should be fine in this case though. Typically, I'll use a file pre-processor to scrub the file before loading. I'd be interested in the performance of the recordset destination.

cairnz Over a year ago

Just to elaborate a bit on why it doesn't work: SSIS processes rows in batches. In your script component you would be assigning local variables on a per-row basis, which would (let's say you're just putting incrementing numbers) - fill values 1 to 1000 in the local variables. Your PostExecute doesn't happen until all rows are processed so the package variables aren't updated until all rows are passed through.

user756519 · Accepted Answer · 2011-06-02 04:05:48Z

The process that you are trying to achieve in the question can be done with the help of the Output Columns in Script Component Transformation task instead of using the variables. The below example explains how this can be achieved and also it demonstrates how variables can be used within the script component. However the same functionality can be achieved using Derived Column Transformation task as well.

This example reads a CSV file and based on the first column value, it will extract value from second column starting from a give position and length. All these values will be stored in a variable.

Step-by-step process:

Create a CSV file as shown in screenshot #1. If the first column has the value Customer, we will extract 6 characters from 9th character on the second column value. If the first column has the value Vendor, we will extract 7 characters from 21st character on the second column value. For every other value, the package will assume value zero for second column value.
Create a table named dbo.Destination using the script under SQL Scripts section. The package will insert the CSV data into this table.
On the SSIS package, create 6 variables as shown in screenshot #2. Configure an OLE DB connection to connect to the SQL Server instance. Configure a Flat File connection to read the CSV file as shown in screenshots #3 - #6. Also, place a Data Flow Task on the Control Flow tab of the package.
Configure the Data Flow Tab with a Flat File Source, Script Component Transformation Task (Refer screenshot #7) and an OLE DB Destination.
Configure the Flat File Source task as shown in screenshots #8 and #9.
Configure the Script Component transformation task as shown in screenshots #10 and #11. A new Output Column named SNumber of data type four-byte signed integer [DT_I4] is created to store the extracted value. Replace the Script Component code with code displayed under Script Component Code section. PreExecute method reads the package variables' values and the method Input0_ProcessInputRow processes the logic to populate the Output Column SNumber.
Configure the OLE DB Destination task as shown in screenshots #12 and #13.
Screenshot #14 shows sample package execution.
Screenshot #15 shows the data in the table dbo.Destination after the package execution.

Hope that helps.

SQL Scripts: .

CREATE TABLE [dbo].[Destination](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Header] [varchar](50) NOT NULL,
    [Value] [varchar](50) NOT NULL,
    [SNumber] [int] NOT NULL,
 CONSTRAINT [PK_Destination] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

Script Component Code:

C# code that can be used only in SSIS 2008 and above. .

/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    IDTSVariables100 varCollection = null;
    string customer = string.Empty;
    int customerPosition = 0;
    int customerLength = 0;
    string vendor = string.Empty;
    int vendorPosition = 0;
    int vendorLength = 0;

    public override void PreExecute()
    {
        this.VariableDispenser.LockForRead("User::Customer");
        this.VariableDispenser.LockForRead("User::CustomerPosition");
        this.VariableDispenser.LockForRead("User::CustomerLength");
        this.VariableDispenser.LockForRead("User::Vendor");
        this.VariableDispenser.LockForRead("User::VendorPosition");
        this.VariableDispenser.LockForRead("User::VendorLength");
        this.VariableDispenser.GetVariables(out varCollection);

        customer = varCollection["User::Customer"].Value.ToString();
        customerPosition = Convert.ToInt32(varCollection["User::CustomerPosition"].Value);
        customerLength = Convert.ToInt32(varCollection["User::CustomerLength"].Value);
        vendor = varCollection["User::Vendor"].Value.ToString();
        vendorPosition = Convert.ToInt32(varCollection["User::VendorPosition"].Value);
        vendorLength = Convert.ToInt32(varCollection["User::VendorLength"].Value);

        base.PreExecute();
    }

    public override void PostExecute()
    {
        base.PostExecute();
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        if (Row.Header.ToString().Trim() == customer)
        {
            Row.SNumber = Convert.ToInt32(Row.Value.Substring(customerPosition, customerLength));
        }
        else if (Row.Header.ToString().Trim() == vendor)
        {
            Row.SNumber = Convert.ToInt32(Row.Value.Substring(vendorPosition, vendorLength));
        }
        else
        {
            Row.SNumber = 0;
        }
    }

}

Screenshot #1:

Screenshot #2:

Screenshot #3:

Screenshot #4:

Screenshot #5:

Screenshot #6:

Screenshot #7:

Screenshot #8:

Screenshot #9:

Screenshot #10:

Screenshot #11:

Screenshot #12:

Screenshot #13:

Screenshot #14:

Screenshot #15:

Charles Menguy · Accepted Answer · 2012-04-18 05:15:09Z

2

Select your variable as ReadWriteVariable on the Transform Editor then use the code below:

this.ReadWriteVariables["User::YourVariable"].Value = "New Value";

edited Apr 18, 2012 at 5:15

Charles Menguy

41.6k18 gold badges97 silver badges117 bronze badges

answered Apr 17, 2012 at 15:16

dcostelloe

211 bronze badge

Comments

grapefruitmoon · Accepted Answer · 2011-03-31 15:34:34Z

1

Looks like you could avoid a Script Component completely here and just use a Derived Column Task, creating 3 new columns for SNumber, RNumber and MNumber, with an expression like

[Col1]=="MyName" ? ltrim(Substring([Column4],122,5)) : NULL(DT_WSTR,5)

and so on.

answered Mar 31, 2011 at 15:34

grapefruitmoon

3,0281 gold badge23 silver badges27 bronze badges

2 Comments

lch Over a year ago

the reason why iam using the script task is: Col1 has two types of values "MyName" & "Myvendorname" the source is a flat file if the col1 has myvenodr value the columns to select are different i used ragged right to extract the columns if the col1 has myname then i need to extract a value which is a combination of col4 & col5 however iam not using col4 & col5 if col1 is myvendor.so before the conditional spirit i want to use script component to get the value assign to a variable then use it in destination.

grapefruitmoon Over a year ago

You can still do that in a Derived Column, eg [Col1]=="MyName" ? ltrim(Substring([Column4],122,5)) : [Col1]=="MyVendorName" ? <your logic> : NULL(DT_WSTR,5)

Collectives™ on Stack Overflow

How to write to a Variable in script component

4 Answers 4

2 Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related