SSIS – ODBC Parameter Size

odbcssis

I have created a SSIS package which runs every hour from my Machine. First part of my Package runs a Execute SQL Task and result set (single row, single column) is a comma separated list of Unique Record Ids which have been updated in last hour. (i.e. FJ1,FJ2,FJ3) I capture this result in a variable and when I User script tasks to display the value of this property, it appears as it should be.

I pass this Variable to Execute SQL Task on different ODBC Server using a Stored Procedure

My SQL Statement is as Following

call DeleteRowsById(?)

And then I have assigned my Variable (as SQL_Longvarchar) to Parameter 1 in Parameter mapping Section. (ParameterSize is -1) but it doesn't delete the records from the table. (I ran the SP manually in Workbench and it works as expected) Then I had an idea and as part of my SP I started making entries in a Temp table just to see whats coming up as variable. Insert in the table shows only first character from the variable and which is why it's not deleting any records.

Can someone please point me to the right direction as I can not find why my variable is being truncated.

Best Answer

I know this was asked a while ago, but maybe someone else will stumble across this answer and not spend the multiple hours that I've been bashing my head against this.

Turns out that you need to use the SQL_WVARCHAR type for Variables.

The following is my evidence, showing the SQL_WVARCHAR works.

OLEDB command from SQL Profiler:

exec sp_executesql N'exec dbo.usp_InsertJobLogODBC @P1,@P2,@P3,3,''Task [TestBIMLPackage] Starting''',N'@P1 varchar(6),@P2 varchar(2),@P3 varchar(10)','TestMe','01','2015-03-24'

ODBC command from SQL Profiler using SQL_VARCHAR

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@P1 varchar(6),@P2 varchar(2),@P3 varchar(10)',N'exec dbo.usp_InsertJobLogODBC @P1,@P2,@P3,3,''Task [TestBIMLPackage] Starting''','T','0','2'
select @p1

ODBC command from SQL Profiler using SQL_CHAR

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@P1 char(20),@P2 char(20),@P3 char(20)',N'exec dbo.usp_InsertJobLogODBC @P1,@P2,@P3,3,''Task [TestBIMLPackage] Starting''','T                   ','0                   ','2                   '
select @p1

ODBC command from SQL Profiler using SQL_WVARCHAR

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P1 nvarchar(20),@P2 varchar(20),@P3 nvarchar(20)',N'exec dbo.usp_InsertJobLogODBC @P1,@P2,@P3,3,''Task [TestBIMLPackage] Starting''',N'TestMe','01',N'2015-03-24'
select @p1
Related Question