I am using rowVersion to detect rows that need to be re-extracted for my ETL.  The first step in the ETL is to get MIN_ACTIVE_ROWVERSION() from the database, as well as the rowVersion from the last successful ETL run.  Both of these values are saved in variables in SSIS.  Since SSIS doesn't have an associated data type, these values are converted to nvarchar(512) before getting sent to SSIS.  

I'm now trying to create a stored procedure to grab the data out of the source system.  The sproc takes RowVersionMin and RowVersionMax as input parameters.  Those parameters are then converted to varbinary(max), and I use them in a query to select the appropriate rows.

When I run the sproc in SSMS, it runs without a problem.  However, when I try to use the sproc as a data source in SSIS, I get the following error: "Error converting data type nvarchar to varbinary".  Any ideas why SSIS has a problem with this, but SSMS does not?  The sproc code looks like this:

ALTER PROCEDURE [dbo].[dw_DimComment_DataLoad]
	@RunType varchar(3),
	@RowVersionMin nvarchar(512),
	@RowVersionMax nvarchar(512)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;


	--Create local variables to prevent parameter sniffing
	declare @RunTypeLoc varchar(3),
	@RowVersionMinLoc varbinary(max),
	@RowVersionMaxLoc varbinary(max)

	set @RunTypeLoc = @RunType;
	set @RowVersionMinLoc = CONVERT(varbinary(max), @RowVersionMin, 1);
	set @RowVersionMaxLoc = CONVERT(varbinary(max), @RowVersionMax, 1);



	if @RunTypeLoc = 'INC' or @RunTypeLoc = 'HIS'
	begin
		select ...
		from Comments
		where verCol >= @RowVersionMinLoc and verCol < @RowVersionMaxLoc;
	end

	if @RunTypeLoc = 'REL'
	begin
		select ...
		from Comments
		where ...;
	end 

END

Thanks!

Any columns in the select that may cause it? When run in SSMS, there's definitely no error message (even if it returned results there may still be an error)

No, the columns in the select don't cause any problems, and there are no error messages in SSMS.  Oddly enough, if I change the sproc to the following, the issue goes away in SSIS:

ALTER PROCEDURE [dbo].[dw_DimComment_DataLoad]
	@RunType varchar(3),
	@RowVersionMin nvarchar(512),
	@RowVersionMax nvarchar(512)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;


	--Create local variables to prevent parameter sniffing
	declare @RunTypeLoc varchar(3),
	@RowVersionMinLoc nvarchar(512),
	@RowVersionMaxLoc nvarchar(512)

	set @RunTypeLoc = @RunType;
	set @RowVersionMinLoc = @RowVersionMin;
	set @RowVersionMaxLoc = @RowVersionMax;



	if @RunTypeLoc = 'INC' or @RunTypeLoc = 'HIS'
	begin
		select ...
		from Comments
		where verCol >= CONVERT(varbinary(max), @RowVersionMinLoc, 1) and verCol < CONVERT(varbinary(max), @RowVersionMaxLoc, 1);
	end

	if @RunTypeLoc = 'REL'
	begin
		select ...
		from Comments
		where ...;
	end 

END

• Marked as answer by Smithochris 14 hours 53 minutes ago