locked
SSIS - Using Variables in Execute SQL Task Error RRS feed

  • Question

  • The following query in my Execute SQL Task throws the following error when run - [Execute SQL Task] Error: Executing the query "SELECT @columnz = COALESCE(@columnz + ',[' + times..." failed with the following error: 
    "Must declare the scalar variable "@columnz".". Possible failure reasons: Problems with the query, 
    "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Query:

    drop table tmpNCPCNCDownstreamMaxUtilization3wks

    select node, max(utilization) as max_Utilization, DATE
    into tmpNCPCNCDownstreamMaxUtilization3wks
    from stage_ncpcncdownstream_temporal
    WHERE Date BETWEEN DATEADD(day, -20, GETDATE()) AND GETDATE() 
    group by node, date
    order by node
    go

    alter table tmpNCPCNCDownstreamMaxUtilization3wks add Timestamp varchar(50)
    go

    update tmpNCPCNCDownstreamMaxUtilization3wks
    set Timestamp = 'WeekOf_' + cast(left(Date, 11) as varchar)
    go

    --drop table tmpNCPCNC_DownstreamNodeUtilizationMaxPivot
    --go

    declare @columnz varchar (8000)
    go
    SELECT @columnz = COALESCE(@columnz + ',[' + timestamp + ']','[' + timestamp+ ']')
    FROM tmpNCPCNCDownstreamMaxUtilization3wks group by timestamp order by timestamp
    go

    --pivot contents in @columns
    declare @query varchar(8000)
    SET @query ='select * into tmpNCPCNC_DownstreamNodeUtilizationMaxPivot from 
    (select node, timestamp, Max_Utilization from tmpNCPCNCDownstreamMaxUtilization3wks)a
    pivot
    (
    sum(max_Utilization) for timestamp in('+ @columnz +')
    )as p'

    execute (@query)

    --empty contents from NCPCNCDownstreamMaxUtilization3wks
    update tmpNCPCNCDownstreamMaxUtilization3wks
    set timestamp =''

    truncate table tmpNCPCNC_MaxDownstreamNodeUtilzationChart3Week

    --load to table to be joined to Node feature class
    insert into tmpNCPCNC_MaxDownstreamNodeUtilzationChart3Week
    (Node, TwoWeeksAgo, PreviousWeek, CurrentWeek)
    select * from tmpNCPCNC_DownstreamNodeUtilizationMaxPivot

    --calculate average utilization for all values in each record
    update tmpNCPCNC_MaxDownstreamNodeUtilzationChart3Week
    set average = (coalesce(twoweeksago, 0) + coalesce(previousweek, 0) + coalesce(currentweek, 0))/3
    from tmpNCPCNC_MaxDownstreamNodeUtilzationChart3Week


    Scott

    Tuesday, March 3, 2015 11:54 PM

Answers

  • Issue is with variable @columnz.

    Scope of variables is only limited to the batch and if batch is over Variable is no more accessible.

    Reproduce Steps: 

    DECLARE @columnz int = 0
    Go --Scope of @columnz ends here
    -----------------
    SELECT @columnz  -- Ends up with error

    So you have to remove GO keyword. I have commented 2 of them in below:

    DROP TABLE tmpNCPCNCDownstreamMaxUtilization3wks
    SELECT node
    	,max(utilization) AS max_Utilization
    	,DATE
    INTO tmpNCPCNCDownstreamMaxUtilization3wks
    FROM stage_ncpcncdownstream_temporal
    WHERE DATE BETWEEN DATEADD(day, - 20, GETDATE())
    		AND GETDATE()
    GROUP BY node
    	,DATE
    ORDER BY node
    GO
    ALTER TABLE tmpNCPCNCDownstreamMaxUtilization3wks ADD TIMESTAMP VARCHAR(50)
    GO
    UPDATE tmpNCPCNCDownstreamMaxUtilization3wks
    SET TIMESTAMP = 'WeekOf_' + cast(left(DATE, 11) AS VARCHAR)
    GO
    --drop table tmpNCPCNC_DownstreamNodeUtilizationMaxPivot
    --go
    DECLARE @columnz VARCHAR(8000)
    --GO
    SELECT @columnz = COALESCE(@columnz + ',[' + TIMESTAMP + ']', '[' + TIMESTAMP + ']')
    FROM tmpNCPCNCDownstreamMaxUtilization3wks
    GROUP BY TIMESTAMP
    ORDER BY TIMESTAMP
    --GO
    --pivot contents in @columns
    DECLARE @query VARCHAR(8000)
    SET @query = 'select * into tmpNCPCNC_DownstreamNodeUtilizationMaxPivot from 
    (select node, timestamp, Max_Utilization from tmpNCPCNCDownstreamMaxUtilization3wks)a
    pivot
    (
    sum(max_Utilization) for timestamp in(' + @columnz + ')
    )as p'
    EXECUTE (@query)
    --empty contents from NCPCNCDownstreamMaxUtilization3wks
    UPDATE tmpNCPCNCDownstreamMaxUtilization3wks
    SET TIMESTAMP = ''
    TRUNCATE TABLE tmpNCPCNC_MaxDownstreamNodeUtilzationChart3Week
    --load to table to be joined to Node feature class
    INSERT INTO tmpNCPCNC_MaxDownstreamNodeUtilzationChart3Week (
    	Node
    	,TwoWeeksAgo
    	,PreviousWeek
    	,CurrentWeek
    	)
    SELECT *
    FROM tmpNCPCNC_DownstreamNodeUtilizationMaxPivot
    --calculate average utilization for all values in each record
    UPDATE tmpNCPCNC_MaxDownstreamNodeUtilzationChart3Week
    SET average = (coalesce(twoweeksago, 0) + coalesce(previousweek, 0) + coalesce(currentweek, 0)) / 3
    FROM tmpNCPCNC_MaxDownstreamNodeUtilzationChart3Week


    -Vaibhav Chaudhari

    • Proposed as answer by Eric__Zhang Wednesday, March 4, 2015 12:07 PM
    • Marked as answer by Eric__Zhang Tuesday, March 10, 2015 2:23 AM
    Wednesday, March 4, 2015 6:57 AM