asked on

SSIS comma-delimited variable not working

We have a basic SQL Task that performs an insert. There is only one variable. If we hard-code the CCONO numbers the Task runs fine. When we enter a comma-delimited string variable (i.e. 5,10,20,25) using SSIS variables we receive the following error:

"An error occurred while extracting the result into a variable of type (DBTYPE_I4)."

Tried changing the variable to another type, but they don't seem to like commas. Tried adjusting parameter size. Just looking for the key ingredient when passing a string variable in SSIS.

If we hard-code the variable values, the query runs just fine.

insert bARCM (CustGroup, Customer, Name,SortName,TempYN,Address,City,State,Zip,Address2,Status,CreditLimit,SelPurge,StmntPrint,
StmtType,FCType,FCPct,MarkupDiscPct,HaulTaxOpt,InvLvl,MiscOnInv,MiscOnPay,PrintLvl,SubtotalLvl,SepHaul,ExclContFromFC,BillAddress,BillCity,BillState,BillZip,BillAddress2)
 
select 
CustGroup,
CCUST,
CNM25,
CAST(UPPER(substring(replace(CNM25,' ',''),1,10)) as varchar(10)) + CAST(CCUST as varchar(5)),
'N',
case when CA25A='' then null else CA25A end,
case when CCITY='' then null else CCITY end,
case when CST='' 	then null 	else CST end,
case when CZIP='' 	then null 	else CZIP end,
case when CA25B='' then null else CA25B end,
'A',
999999999,
'N',
'N',
'O',
'N',
0,
0,
0,
0,
'N',
'N',
1,
1,
'N',
'N',
case when CDLA1='' then null else CDLA1 end,
case when CDLCI='' then null else CDLCI end,
CASE when CDLST='' then null else CDLST end,
case when CDLZP='' then null else CDLZP end,
case when CDLA2='' then null else CDLA2 end
 
from CSTMST 
	join bHQCO on (100+CCONO = HQCo)
 
where CCONO in (?)

Open in new window

Viewpointcs

ASKER

We tested this behavior with both OLE DB and Native SQL connectors, got the same result. Since we are passing a string, we have tried it both with no single quote, and single quotes around the ? variable marker and in the variable value setting.

Mark Wills

well it is a string variable that you are putting in there, so, what if you had your parameter as '5','10','20','25' and then have :

where convert(varchar,cocono) in (?)

not quite sure how the parameter is being interpreted by SSIS - I have not actually had this example in real life before, but think if you can at least get them to the same base, then it gives you a fighting chance...

Raja Jegan R

Hope CCONO is of Datatype Int.

Try this where claue:

where cast(CCONO as varchar(20)) in ('5','10','20','25')

ASKER CERTIFIED SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BrandonGalderisi

Won't that prevent it from utilizing any possible index on CCONO?

Mark Wills

Yep, most likely, and the function has some impacts on that as well. I was considering ease of use over index - it is an import staging area so kind of expect table scans, especially given the join : join bHQCO on (100+CCONO = HQCo). So do not think it is a particularly negative approach all things considered.

Steve Hogg

Since the variable contains a string of data, the variable has to be a string. Are we already past this point, if so - sorry.
Variable, String, Value= 5,10,20,25

nmcdermaid

You might be able to use expressions in SSIS to effectively create 'dynamic SQL' which is what you'll need to do to include this as literal SQL.
Let me know if you are interested and I will post back.

BrandonGalderisi

I believe that http:#a23721916 is a viable solution as well as http:#a23722165 minimally. There are probably other possible solutions, but I have not reviewed them all.