none
how do i assign value to variable using script task

    Question

  • I am using script task first time can any one help me how to use script task for assigning value using sql

    DECLARE @Opportunity_Id AS VARCHAR(MAX)
    SET @Opportunity_Id = ''

    SELECT @Opportunity_Id =
                                        CASE @Opportunity_Id
                                              WHEN '' THEN ''''  + Opportunity_Id + ''''
                                              ELSE @Opportunity_Id + ','''  + Opportunity_Id + ''''
                                        END
    FROM price.Stg_Batch_Process

    print @opportunity_id

    My results are like this

     

    '0000169621','0000173296','0000185499','0000199013' and so on

     

    I would like to set this whole value to some variable with string datatype

     

    Friday, July 02, 2010 8:34 PM

Answers

  • Script Tasks in SSIS use VB.NEt or C# code, not T-SQL like you have listed.

    First, after defining your variable in the Variables section, add it in the ReadWrite property of the Script task. Then iside the script, you can read and write as follows:

    DIM MyVar as string

    MyVar = Dts.Variables("SomeVariableName").Value.ToString

    <add some code here to modify the value of MyVar>

    Dts.Variables("SomeVariableName").Value = MyVar

    But your code looks familiar...

    you want to access a database table somewhere and build a concatenated string, separated by commas, then use it in a SELECT statement, right?

    You are in for some slow response times with that method.


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Friday, July 02, 2010 10:29 PM
  •  

    • Take an execute sql task and save the result set into an object variable.
    • Then declare 2 string variables (stringa and stringb without any default value). 
    • Take a foreach loop and iterate through each value inside the object variable using foreachadoenumerator. Save each value stored in the object variabe to string variable (stringa)
    • take a script task inside foreach loop and assign the value of stringa to stringb by using following logic:
    If the length of stringb =0 then stringb=stringa
    else stringb = stringb +","+stringa

     


    Nitesh Rai- Please mark the post as answered if it answers your question
    Saturday, July 03, 2010 4:21 AM

All replies

  • hi I have a query which pull result from one the table

     

    DECLARE @Opportunity_Id AS VARCHAR(MAX)
    SET @Opportunity_Id = ''

    SELECT @Opportunity_Id =
                                        CASE @Opportunity_Id
                                              WHEN '' THEN ''''  + Opportunity_Id + ''''
                                              ELSE @Opportunity_Id + ','''  + Opportunity_Id + ''''
                                        END
    FROM price.Stg_Batch_ProcessDECLARE @Opportunity_Id AS VARCHAR(MAX)
    SET @Opportunity_Id = ''

    SELECT @Opportunity_Id =
                                        CASE @Opportunity_Id
                                              WHEN '' THEN ''''  + Opportunity_Id + ''''
                                              ELSE @Opportunity_Id + ','''  + Opportunity_Id + ''''
                                        END
    FROM price.Stg_Batch_Process

     

     

    but I am not able to assign it to variable

     

    I have selected single row as output with 0 as resultset and with list(variable name with string data type)

    Thursday, July 01, 2010 9:11 PM
  • Hello,

    What error you are getting?

    Use the variable type int32 and see if it works?

    When you run this query in SSMS , do you get any value, if yes , is it single value?

    Edited: Aminesh You need to look into your query, Did not get that what are you trying to achive. Seems like you are trying to create some value with '' and save into variable and use it somewhere?

     

    Thanks

    Thursday, July 01, 2010 9:18 PM
  • First of you appear to have duplicated the code, but I assume that was just a mistake in the post.

    I don't actually understand the query either, but if you start by making it work in (SSMS) then move it to SSIS it is often easier.

    If you want to use Single Row for the ResultSet then you need to return a row. If you run the SQL in a a query tool like SQL Server Management Studio (SSMS) you don't get the results grid, so there is no result set, single row or otherwise.

    I would try and change the query to actually return a row, it is simpler if you don't assign the result to to a T-SQL variable.

    Sometimes you do have a T-SQL variable that holds the result, but then you need to ensure you have a SELECT TO generate a ResultSet -

     

    SET NOCOUNT ON
    DECLARE @Var int = 1
    SELECT @Var + 1 AS V
    


    http://www.sqlis.com | http://www.konesans.com
    Friday, July 02, 2010 6:55 AM
  • Script Tasks in SSIS use VB.NEt or C# code, not T-SQL like you have listed.

    First, after defining your variable in the Variables section, add it in the ReadWrite property of the Script task. Then iside the script, you can read and write as follows:

    DIM MyVar as string

    MyVar = Dts.Variables("SomeVariableName").Value.ToString

    <add some code here to modify the value of MyVar>

    Dts.Variables("SomeVariableName").Value = MyVar

    But your code looks familiar...

    you want to access a database table somewhere and build a concatenated string, separated by commas, then use it in a SELECT statement, right?

    You are in for some slow response times with that method.


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Friday, July 02, 2010 10:29 PM
  • Hi Todd ;

    I am getting your point but is it possible for you that can you help me to write my sql code into script task familiar language.

     

    truely my job is under pressure if you can help me that will be really appreciatable 

     

    Saturday, July 03, 2010 12:18 AM
  •  

    • Take an execute sql task and save the result set into an object variable.
    • Then declare 2 string variables (stringa and stringb without any default value). 
    • Take a foreach loop and iterate through each value inside the object variable using foreachadoenumerator. Save each value stored in the object variabe to string variable (stringa)
    • take a script task inside foreach loop and assign the value of stringa to stringb by using following logic:
    If the length of stringb =0 then stringb=stringa
    else stringb = stringb +","+stringa

     


    Nitesh Rai- Please mark the post as answered if it answers your question
    Saturday, July 03, 2010 4:21 AM