SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 SSIS SQL Task - invalid number of result bindings
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hawk73ku
Starting Member

USA
1 Posts

Posted - 03/20/2014 :  23:41:17  Show Profile  Reply with Quote
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
Aged Yak Warrior

561 Posts

Posted - 04/07/2014 :  15:33:17  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000