SSIS comma-delimited variable not working

Posted on 2009-02-23
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,






CAST(UPPER(substring(replace(CNM25,' ',''),1,10)) as varchar(10)) + CAST(CCUST as varchar(5)),


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,

















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

Question by:Viewpointcs

    Author Comment

    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.
    LVL 51

    Expert Comment

    by: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...
    LVL 57

    Expert Comment

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

    Try this where claue:

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

    Accepted Solution

    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]
    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)
    *    Author:        Brandon Galderisi
    *    Last modified: 07-Oct-2008
    *    Purpose:       splits an input string (@DelimitedString) 
    *                   on a delimiter (@delimiter) and outputs 
    *                   a table of values.
    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
    			n <= (datalength(@delimiter + @DelimitedString + @delimiter)/2) - (datalength(@delimiter)/2)
    			and substring(@delimiter + @DelimitedString + @delimiter, n, (datalength(@delimiter)/2)) = @delimiter
    -- 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
    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',' ')

    Open in new window

    LVL 51

    Assisted Solution

    by:Mark Wills

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


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

    Expert Comment

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

    Expert Comment

    by: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.
    LVL 17

    Expert Comment

    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
    LVL 30

    Expert Comment

    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.
    LVL 39

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Suggested Solutions

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now