Post reply

Assigning a new value to a Variable using a Script Task

  • September 3, 2008 at 12:17 pm

    #125159

    Hi,

    Please help.

    I am reading from Oracle and I am trying to pass it Variable, @Max_Modified_Date.

    I have followed SK's instructions on how to declare a package level variabe containing a SQL query that will parse, then creating a script task to update that variable, and then assigning the updated variable to the [DataReader Source].[SQLCommand] property in the Expressions for the Data Flow Task.

    My problem is that the Script task does not update the variable. 🙁

    In the Script task I try to assign the concatenation of the select statement that I actually want to use and the @Max_Modified_Date variable, to the previously declared package variable. But when I test it by using a Message Box, it keeps on returning only the original value that I assigned to the package variable.

    Do I need to change any of the originally declared variable's properties?

    If not, what am I missing? Why does it not assign the new value the the original Variable?

    Here is an example of the VB.Net code that I am trying to use in the Script Task:

    'Dts.Variables("User::SELECT").Value = "SELECT Col1, Col2 FROM Schema1.Table 1 WHERE Col1 <> Random Value AND Col2 > " & Dts.Variables("User::Max_Modified_Date").Value.ToString()

    Can you see any problems with this code?

    Any help will be greatly appreciated!

    Thanx- 😀

  • September 3, 2008 at 3:45 pm

    #866844

    Hi,

    did you lock the variable for writing?

    Let me know the variablename you are using for writing.

    it should be the same case variable.

    try using plus sign (+) instead of ampersand while concatenating 😉

    thanks

    Nilesh

  • September 3, 2008 at 4:17 pm

    #866860

    Check if the variable is getting assigned with proper value returned from oracle.

    Where are u assigning the value to the variable

    Regards
    Venkat
    http://sqlblogging.blogspot.com[/url]

  • September 4, 2008 at 1:45 am

    #866979

    Hi,

    I did put the Variable Name , User::SELECT, in the ReadWriteVariables property of the script task.

    Do I need to lock it in another way?

  • September 4, 2008 at 1:56 am

    #866980

    Hi Venkat,

    --Check if the variable is getting assigned with proper value returned from oracle.

    I have two variables: 'User::SELECT' contains the select statement that is used to parse against Oracle and it is declared at package level. The select statement contains all the columns that I am going to need, and one basic filter (no parameters or variables).

    'User::Max_Modified_Date' contains an integer value that is selected from my destination SQL Server 2005 table. I am trying to use this value, dynamically, to filter my select statement to Oracle, as you can see in the example of code in my first post. 'User::Max_Modified_Date' is assigned its value in an Execute SQL task. This seems to be fine.

    --Where are u assigning the value to the variable

    I am trying to assign a new value to the 'User::SELECT' variable in a script task. The new value of the 'User::SELECT' variable will also be a SELECT statement, but it will include an extra condition in the WHERE clause that uses the 'User::Max_Modified_Date' variable, as seen in the code example in my first post.

  • September 4, 2008 at 10:07 am

    #867257

    Hi,

    Please check the attachment. I have defined a variable `TimeStamp` and used the script task to assign a value at runtime. you can check if you are doing it this way.

    Also you can uncomment the mssgbox line in the scripttask to check the value after it is assigned.

    Hope this resolves your problem.

    Thanks

    Nilesh

  • September 4, 2008 at 10:32 am

    #867280

    Thanks!

    I will have a look!

  • September 5, 2008 at 3:02 am

    #867624

    hestenerwin (9/3/2008)

    'Dts.Variables("User::SELECT").Value = "SELECT Col1, Col2 FROM Schema1.Table 1 WHERE Col1 <> Random Value AND Col2 > " & Dts.Variables("User::Max_Modified_Date").Value.ToString()

    Can you see any problems with this code?

    First, ' sign means yout code is commented out :-)... Check what are the scopes of your variables. Put each used variable in appropriate property of your script task. Enclose your code in try... catch block. You don't have to use namespaces (in this context).

    I have prepared working script that does what you want (see attachment)

  • September 5, 2008 at 3:15 am

    #867627

    Thank you very much!

Viewing 9 posts - 1 through 8 (of 8 total)

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