Facing issue with Execute Sql Task

Question

I am having issue with Execute script task. Currently the task is executing stored procedure and result is assigned to variable. Now I want to execute both the stored procedure and sql select statement in single execute task I tried to acheive it by using dynamic sql statements as shown below.

SQl Script

 DECLARE @Query                  NVARCHAR(250)
DECLARE @ParmDefinition         NVARCHAR(250)
DECLARE @Id           int
DECLARE @Usr_Id         VARCHAR(50)
DECLARE @TCId       INT
DECLARE @IsSuccess         bit

Set @Query = 'Exec InsertSp @Id,@Usr_Id,@TCId OUTPUT'
 
SET @ParmDefinition = '@Id INT,@Usr_Id VARCHAR(50),@TCId INT OUTPUT'
 
EXEC sp_executesql @Query,   
     @ParmDefinition,
     @Id=2,
     @Usr_Id='domain\fn.lastname',
     @TCId=@TCId OUTPUT

Set @Query = 'SELECT  TOP (1) @IsSuccessOut = IsSuccess
FROM dbo.TableA AS a
INNER JOIN dbo.TableB AS b
ON a.Id = b.Id
WHERE (b.Name = ''LoadDaily'')
ORDER BY StartTime DESC'
 
SET @ParmDefinition = '@IsSuccessOut INT OUTPUT'

EXEC sp_executesql @Query,@ParmDefinition,@IsSuccessOut=@IsSuccess output

SELECT @TCId AS 'TCId',@IsSuccess as 'IsSuccess'

I created variable and assigned the above query to it.

In Execute SQl task,I have assigned the variable

i set result of the variable to two variables When I am trying to run package, not able to get values of output to result variables. Please let me know how can i get out put of above sql.

Answer 1

A simple version of your package works, so let's break it down.

Your Execute SQL Task has 3 variables at play. Whatever you crossed out in the first screenshot, which I will call User::SQLSource and is of type String. I have this defined as SELECT 0 AS TCId, CAST(1 AS bit) as IsSuccess;

I have two variables for the result set. User::IsSuccess which is a Boolean type and User::TCId which is type Int32.

SCR Echo Back

I favor a script task like this to echo the value of variables into the information event log

using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_EchoBack
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            bool fireAgain = false;
            string message = "{0}::{1} : {2}";
            foreach (var item in Dts.Variables)
            {
                Dts.Events.FireInformation(0, "SCR Echo Back", string.Format(message, item.Namespace, item.Name, item.Value), string.Empty, 0, ref fireAgain);
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
    }
}

Where did it go wrong?

You should be able to recreate what I have shown here and get the same results. Once that's done, start adding your more complex pieces back in.

Given your error of

Execute SQL Task: An error occurred while assigning a value to variable "TCId"

I could recreate that same error by having a query in my Execute SQL Task that generates a result set.

Multi-statements are fine as long as no recordsets are generated

DECLARE @foo int;
SELECT @foo = 1 FROM sys.tables AS T; 

SELECT 0 AS TCId, CAST(1 AS bit) AS IsSuccess;

Queries that generate results, will

DECLARE @foo int;
SELECT @foo = 1 FROM sys.tables AS T; 

-- This will mess things up.
SELECT 1, 1 FROM sys.tables;

SELECT 0 AS TCId, CAST(1 AS bit) AS IsSuccess;

I would take your existing query and run it in SSMS. One of your sp_executesql is generating a result set. There could also be a trigger associated to InsertSp that is generating a result set and that's fouling up the metadata.

The other thing I'd point out is you don't need to alias your columns with a single quote in your final statement there. SQL server uses square brackets, preferred, or double quotes if you must if you are dealing with a poorly named column.