Setting a variable value in a script destination

  • Maybe I am just missing something (like the entire .NET revolution...)

    Being absolutely wet behind the ears with .NET, I am really struggling to come to terms with the scripting tasks in SSIS.

    I have a variable (in this case called varRunBalances - Boolean) for which I set the scope to the entire package.

    Now during one of my dataflows I calculate if the Run Balances or not using a column derivation.

    For me the logical next step would be to create a script destination transformation where I have a simple statement

    If the run balances, then set the variable equal to True, else set the variable equal to False.

    How would I do this in a scripting component?

    :blush:

  • Where are you putting if the "Run Balances"? You are calculating this in a derived column? A derived column executes once per row in the data flow and adds or modifies columns in that data flow, so I must be missing something.

    You can set a variable value in a script component within a data flow. You have to set the read/write variables on the component to include the variable you want to update. I don't think you should have any need to to this though. Perhaps you could explain a bit better what you are trying to do. There are some count and aggregate components that will keep running totals of things and set variables for you without any code.

  • I am using the varRunBalances as a boolean variable which will indicate that my control flow is still in balance .

    For example:

    I have a source file, and a checksum file from a source provider who is notorious for not believing in data integrity (actually it took a lifetime of insisting before they gave me this prized checksum file)

    The first task that I do is to perform a rowcount transformation against the sourcefile (due to change this to an aggregator, because I need to sum on some fields later, but rowcount is fine for this example).

    I load this to a variable, and then merge this with the rowcount found in the checksum file, and used a derived column to work out if the run still balances at that point.

    What I want to do is end this with a scripting destination where I set the run balances variable.

    However, I want to re-use this run balances variable at several other intervals simply to make sure that I am loading the correct data once I get to the very final step.

    Then, wherever I set the run balances, in the control flow I can branch out with expressions to email and force my run into failure.

    Therefore, my variable has a scope of the package rather than the data transformation.

    The forcing to failure is because all this will sit in a for each file enumerator, and I dont want to process any further once the run doesnt balance.

    I have everything else working perfectly, just not the setting of the variable.

    I added varRunBalances as a readwrite variable to the script, and tried the following:

    Dts.Variables("varRunBalances").Value = False

    Doesnt like this (I think its because its a scripting destination?)

    Then I tried the following:

    Me.VariableDispenser.LockForWrite("varRunBalances")

    Me.ReadWriteVariables("varRunBalances").Value = False

    This compiled fine, but when it runs, it comes back with an error saying that the variable is locked.

    What am I missing here (the obvious thing is some serious experience in .NET coding)

  • Note bing the kind of person to hang around and wait, and after a frustrating morning on google, I managed to locate some really interesting examples.

    But the one that did it for me came from Conchango (When in SSIS doubt, ALWAYS look at Conchango first, Jamie you rock!)

    http://blogs.conchango.com/jamiethomson/archive/2006/07/18/SSIS_3A00_-But-it-used-to-work-in-DTS-_2800_7_2900_-_2D00_-Capture-a-value-from-inside-the-data-pump-into-a-variable.aspx

    I know I should really have put this into a PostExecute method, but I felt there wouldnt be any real difference if I were to do it in the ProcessInputRow method.

    Herewith, my code used

    '1. Local variable declaration

    Dim RunBalances As Boolean

    '2. Decide if the run balances(true) or not (false)

    If Row.FileBalances = 1 Then

    RunBalances = True

    Else

    RunBalances = False

    End If

    '3. Add the package variable to the variable dispenser

    Dim vars As IDTSVariables90

    Me.VariableDispenser.LockOneForWrite("varRunBalances", vars)

    '4. Set the package variable, and unlock it

    vars("varRunBalances").Value = RunBalances

    vars.Unlock()

    Once more, kudos to the fellahs at Conchango

  • Hi all,

    I am very new to SSIS and microsoft technology please help me!!

    I followed same steps to update my variable but no success.

    I declared a variable as varPUI & set default value as "OLD_VALUE" and wrote the below code in script component

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    '

    ' Add your code here

    '

    With Row

    Me.VariableDispenser.LockOneForWrite("varPUI", vars)

    vars("varPUI").Value = "New Value"

    vars.Unlock()

    End With

    End Sub

    the updated varaible I am writing into a flatfile

    it is not updating the variable. actual code I want to write is something else if it works fine.

    Please help me where I am going wrong

    Thanks in advance

    Regards,

    -Srinath

  • hey Srinath,

    I can recall when I updated my first variable in a scripting destination, and it was quite interesting....

    First off, are you updating your variable, in your controlflow, as a scripting source, transformation, or destination.

    There are methods for each of these.

    If its a scripting destination, or even transformation, then first off, why?

    Microsoft deem it to be bad practice. In saying that, there is a time and a place for everything. Justify, and we will see what we can do

    ~PD

  • Hi PD,

    Thanks for a quick response, I was getting mad with this.

    Iam doing it in data flow on transformation and code is written in scirpt component.

    my actual requirement is I want to generate sequence number for a duplicate ids like.

    ids rowCount(which I need to generate)

    C_1000075 1

    C_1000147 1

    C_1000147 2

    C_1000271 1

    C_1000271 2

    C_1000271 3

    Please help me how to do it in SSIS. I am a informatica person, there i can do it very easily.

    requirement is very simple but I am unable to implement with SSIS

    Thanks in advance

    Regards,

    -Srinath

  • Look at the following posting:

    http://www.sqljunkies.com/WebLog/sqlbi/archive/2005/05/30/15684.aspx

    This will give you a fairly good idea of what needs to happen, in order for you to set a value during your transformation, and use this as a new column.

    Please shout if it doesnt make sense.

    ~PD

  • No it is not updating the variable

    I really dont know where exactly I am going wrong

    what I am doing is,

    declared a variable called varPUI at package level as int32

    reading the excel file--> sorter trans...-->script component(to generate seq. numbers)---> derived column trans(to derive new column from variable)---> flatfile destination

    in script component I have put varPUI variable under read write variables.

    and below is my code in script component (I tried many options)

    ----

    Imports System

    Imports System.Data

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

    Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

    Public Class ScriptMain

    Inherits UserComponent

    Dim vars As IDTSVariables90

    Dim i As Int32

    Public Overrides Sub PreExecute()

    MyBase.PreExecute()

    i = Variables.varPUI

    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    '

    ' Add your code here

    '

    ' Me.Variables.varPUI = "1"

    ' MsgBox("inside")

    i = i + 1

    End Sub

    ' Public Overrides Sub PostExecute()

    'Dim vars As IDTSVariables90

    ' Me.VariableDispenser.LockOneForWrite("varPUI", vars)

    ' vars(0).Value = i

    ' vars.Unlock()

    'MyBase.PostExecute()

    ' Me.VariableDispenser.LockOneForWrite("varPUI", vars)

    ' vars("varPUI").Value = ss

    ' vars.Unlock()

    ' End Sub

    End Class

    ----

  • Whoa!

    Quite a messy script....

    Without looking at that script, here is one (assuming that you use varPUI, and that varPUI is in the read/write section of your variables)

    - Add a pre-execute SQL task, which will get the max sequence number from your table

    Add a variable called varmaxPUI

    Add a TSQL statement "SELECT COALESCE( MAX(PUI), 0 ) AS LastPUI FROM yourTableWithSequenceNumber"

    Change the Results Set to Single Row

    In the Results Set section, add a Results set. Result Name = LastPUI, Variable Name = User::varmaxPUI

    Why? Because this will give you the last sequential number from your table (assuming that you want to insert using the next sequential number

    - Add a transformation script, and add varPIO to the read/write

    In the Inputs and Outputs Section, Under Outputs, add an output, and add a column. NB: Make sure the output remains Output 0.

    Name the column PUI ---> Four Byte Unsigned Int works for me

    - In the script design, add the following:

    Imports System

    Imports System.Data

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

    Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

    Public Class ScriptMain

    Inherits UserComponent

    Dim NEXTPUI As Int32

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    NEXTPUI = NEXTPUI + 1

    Row.PUI = NEXTPUI

    End Sub

    Public Overrides Sub PreExecute()

    MyBase.PreExecute()

    NEXTPUI = Variables.varmaxPUI

    End Sub

    End Class

    Notice a couple of things:

    a) NEXTPUI is declared inside the class, not inside any of the Subs

    b) The PreExecute simply sets the NEXTPUI equal to what the variable returns

    c) You increment NEXTPUI on a per row basis, and provide that detail to the PUI column

    Try it, and let me know

    ~PD

  • HI PD,

    MANY MANY THANKS FOR YOUR KIND HELP!!!

    it worked fine

    shame on my ignorance

    your help made my day

    Regards,

    -Srinath

  • Only a pleasure Srinath,

    I know what its like when you are just unleashed on SSIS, you tend to scan the fine print and go for what you are after.

    Many a time I have revisited an article quite a few times because I did this, normally when there is a deadline.

    Therefore, I am busy (have not yet perfected) disciplining myseelf to read from start to finish thoroughly.

    Good luck on your endeavours with SSIS man

    ~PD

    ps> Read up on package configurations early, you are going to need them soon enough

Viewing 12 posts - 1 through 11 (of 11 total)

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