none
How to assign any column value coming from source to variable in data flow task in ssis

    Question

  • Hi,

    I am calculating one column in derived column in DTF and then I want to use that column in variable as I want to use that value in my next data flow task.

    Can you please tell me how can I do that?

    I tired one method using script component as transformation is DFT.

    In that, in readwritevariable, I gave one datetime user variable  MAX_PBPS_EFFECTIVE_DT

    and in I/p and O/p, I used that field 'MAX(MEP_EEFF_DT) '

    and in edit script, I wrote below script

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
            '
             Variables.MAXPBPSEFFECTIVEDT = Row.MAXMEPEEFFDT
            '
        End Sub

    But when I ran the package it gave me error that no row assign to variable.

    Please help me soon. Thanks in advance.


    Vicky

    Wednesday, November 20, 2013 4:38 PM

Answers

  • I am not able to completely visualizing the code but it seems like the issue that SSIS can not handle NULL for variables

    it is always recommended to deal with NULL exception like below

    if (String.IsNullOrEmpty(Row.MAXMEPEEFFDT))
    {
     // do something
    }
    else
    {
     Variables.MAXPBPSEFFECTIVEDT = Row.MAXMEPEEFFDT
    }

    However, for more details about this issue read below issue

    Why doesn't the Script Task code assign any value to ReadWriteVariables?


    Hope this will help

    • Marked as answer by Vicky134 Wednesday, November 20, 2013 6:20 PM
    Wednesday, November 20, 2013 5:37 PM
  • The question itself doesnt make much sense to me as in data flow task you'll have multiple values coming for the derived column. So when you say you want to store it in variable then it has to be a object variable. And for storing it you might need to retrieve it from the table where you finally populate in the first data flow task. And you can use execute sql task after the data flow task for that.

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Vicky134 Wednesday, November 20, 2013 6:20 PM
    Wednesday, November 20, 2013 5:40 PM

All replies

  • I am not able to completely visualizing the code but it seems like the issue that SSIS can not handle NULL for variables

    it is always recommended to deal with NULL exception like below

    if (String.IsNullOrEmpty(Row.MAXMEPEEFFDT))
    {
     // do something
    }
    else
    {
     Variables.MAXPBPSEFFECTIVEDT = Row.MAXMEPEEFFDT
    }

    However, for more details about this issue read below issue

    Why doesn't the Script Task code assign any value to ReadWriteVariables?


    Hope this will help

    • Marked as answer by Vicky134 Wednesday, November 20, 2013 6:20 PM
    Wednesday, November 20, 2013 5:37 PM
  • The question itself doesnt make much sense to me as in data flow task you'll have multiple values coming for the derived column. So when you say you want to store it in variable then it has to be a object variable. And for storing it you might need to retrieve it from the table where you finally populate in the first data flow task. And you can use execute sql task after the data flow task for that.

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Vicky134 Wednesday, November 20, 2013 6:20 PM
    Wednesday, November 20, 2013 5:40 PM