?
Solved

SSIS comma-delimited variable not working

Posted on 2009-02-23
12
Medium Priority
?
1,455 Views
Last Modified: 2013-11-10
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

0
Comment
Question by:Viewpointcs
10 Comments
 

Author Comment

by:Viewpointcs
ID: 23717033
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.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23719915
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...
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23720394
Hope CCONO is of Datatype Int.

Try this where claue:

where cast(CCONO as varchar(20)) in ('5','10','20','25')
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 252 total points
ID: 23721916
You won't be able to pass in a variable that contains a comma delimited string because the entire string will be treated as a single value.  You need to use a function to SPLIT the comma delimited string into rows.

You can use this modification to your WHERE clause:

from CSTMST
        join bHQCO on (100+CCONO = HQCo)
 
where CCONO in (select theValue from [dbo].[fn_DelimitedToTable](?,',')



With this function:
if object_id('[dbo].[fn_DelimitedToTable]') is not null
     drop function [dbo].[fn_DelimitedToTable]
go
create function [dbo].[fn_DelimitedToTable](@DelimitedString nvarchar(max), @Delimiter nvarchar(32))
returns @Values TABLE
     (ident         int not null identity primary key clustered
     ,thePosition   int not null
     ,theValue      nvarchar(max)
     )
as
/************************************************************
*
*    Author:        Brandon Galderisi
*    Last modified: 07-Oct-2008
*    Purpose:       splits an input string (@DelimitedString) 
*                   on a delimiter (@delimiter) and outputs 
*                   a table of values.
*    
*
*************************************************************/
begin
 
insert into @Values (thePosition,theValue)
		select n, substring(@delimiter + @DelimitedString + @delimiter, n + (datalength(@delimiter)/2), charindex(@delimiter, @delimiter + @DelimitedString + @delimiter, n + datalength(@delimiter)/2) - n - datalength(@delimiter)/2) as string_value
		from	dbo.vw_Nums
		where
			n <= (datalength(@delimiter + @DelimitedString + @delimiter)/2) - (datalength(@delimiter)/2)
			and substring(@delimiter + @DelimitedString + @delimiter, n, (datalength(@delimiter)/2)) = @delimiter
 
 
 
return
end
/*
-- The purpose of vw_Nums is if the source database does not have a numbers table.  This view (vw_nums)
-- will allow the parsing of up to 4 billion+ character strings with the above function.  Whether a static
-- table or this view is used for fn_DelimitedToTable, it can only split a string as long as the source 
-- numbers table.
Requires a nunbers table or this view:
create view vw_Nums
as
with   cte0 as (select 1 as c union all select 1), 
       cte1 as (select 1 as c from cte0 a, cte0 b), 
       cte2 as (select 1 as c from cte1 a, cte1 b), 
       cte3 as (select 1 as c from cte2 a, cte2 b), 
       cte4 as (select 1 as c from cte3 a, cte3 b), 
       cte5 as (select 1 as c from cte4 a, cte4 b), 
       nums as (select row_number() over (order by c) as n from cte5)
       select n from nums 
 
 
 
Sample Usage:
select * from [dbo].[fn_DelimitedToTable]('a|%25basdf|%25c|%25d','|%25')
select theValue from [dbo].[fn_DelimitedToTable]('a','|')
select * from [dbo].[fn_DelimitedToTable]('a basdf c d',' ')
*/
GO

Open in new window

0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 248 total points
ID: 23722165

Or, if it is a string, then make the param to be     ,5,10,15,20,

then

where charindex(','+convert(varchar,ccono)+',', ?) > 0
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23722241
Won't that prevent it from utilizing any possible index on CCONO?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23722706
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.
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 23724755
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
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 23791134
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.
 
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 25492283
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.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question