SSIS SQL Task - invalid number of result bindings

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SSIS and Import/Export (2012)

SSIS SQL Task - invalid number of result bindings

Author

Topic

Hawk73ku
Starting Member

1 Post

Posted - 2014-03-20 : 23:41:17

SSIS SQL Task: Single Row Result Set

Error: invalid number of result bindings returned for the ResultSetType

Problem: Existing package template was designed to merge small sets of data but not insert 10s of millions of rows as in an initial load or reload and caused transaction log to fill.

SQL Task Code was updated to test for data in target using a conditional: If Exists Do Merge ELSE Do Insert

Previously was just a merge that Output $Action to @ChangeSum and then @ChangeSum queried for updates and inserts and returned resultset

That all worked but after injecting new code I receive the error There is an invalid number of result bindings returned for the ResultSetType that I don't know how to troubleshoot or what is being returned.

In another FORUM it was suggested the new conditional logic was probably confusing SSIS but no offer of how to resolve.

Inject New Code:

IF OBJECT_ID('tempdb..##TblTemp', 'U') IS NOT NULL
DROP TABLE ##TblTemp

Declare @sql nvarchar(max);
set @sql = @TestForData
exec (@sql);

IF EXISTS (select top 1 * from ##TblTemp)
Begin

--Beginning of existing code

begin transaction;

begin try

declare @MergeQuery varchar(max)

set @MergeQuery = convert(varchar(max), @MergeQuery1) + convert(varchar(max), @MergeQuery2)
+ ' ' + convert(varchar(max), @MergeQuery3)
+ ' ' + convert(varchar(max), @MergeQuery4)
+ ' ' + convert(varchar(max), @MergeQuery5);

exec(@MergeQuery);

end try

begin catch

declare
@Message VARCHAR(4000)
,@Severity INT
,@State INT;

select
@Message = ERROR_MESSAGE()
,@Severity = ERROR_SEVERITY()
,@State = ERROR_STATE();

if @@TRANCOUNT > 0
rollback transaction;

raiserror(@Message, @Severity, @State);

end catch;

if @@trancount > 0
begin

commit transaction;

end

--End of existing code

End
else
Begin
declare @InsertQuery nvarchar(max)
set @InsertQuery = convert(varchar(max),@InsertQuery1)

exec (@InsertQuery);
end
Drop Table ##TblTemp
=================================

SSIS Variable @InsertQuery1:

This variable is executed in SQL Task and the last 3 lines I expect a single row of Insert & Update counts to be returned.

declare @ChangeSum table(change varchar(25));
declare @Inserted int = 0;
declare @Updated int = 0;

While 1 = 1
Begin
INSERT INTO [R_Paid].[BusCodeF454x93]
OUTPUT Inserted.Sta3n INTO @ChangeSum
SELECT TOP 1000 s.*
FROM [R_Stage].[BusCodeF454x93] s

WHERE NOT EXISTS
(
SELECT 1
FROM [R_Paid].[BusCodeF454x93]
WHERE STA3N=S.STA3N and [BusCodeF454x93IEN] = s.[BusCodeF454x93IEN]

)
IF @@ROWCOUNT = 0 BREAK
END

set @Inserted = (select count(*) from @ChangeSum );
set @Updated = 0;

select @Inserted as Inserted, @Updated as Updated;

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-07 : 15:33:17

This code is difficult to read. However, it looks to me that in the "old" code you're not returning anything. In the "new" code you end by returning a result set. If your ssis task does not expect a result set, it would discard it. However if your ssis tasks expects a result set and executes the old code, what does it get? From the code above, it looks like nothing, which would throw the error you are seeing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources