c# - How to pass multiple value in one parameter in one variable to OLEDB Source of SSIS

Question

I am trying to pass multiple values as one parameter. e.g: I need to get the employeeID in (1,2,3,4,5), employeeID field data type is Integer.

Query:

select * from emp where employeeID = ?

I Create a variable 'v_employeeID' with the value 1 and passed the parameter mapping in OLEDB Source. I can pass one value but I cant pass multiple value.

Answer 1

One workaround is to create a function:

CREATE FUNCTION [dbo].[fn_GenerateIDs]
(
 @psCSString VARCHAR(8000)
)
RETURNS @otTemp TABLE(sID VARCHAR(20))
AS
BEGIN
 DECLARE @sTemp VARCHAR(10)

 WHILE LEN(@psCSString) > 0
 BEGIN
  SET @sTemp = LEFT(@psCSString, ISNULL(NULLIF(CHARINDEX(',', @psCSString) - 1, -1),
                    LEN(@psCSString)))
  SET @psCSString = SUBSTRING(@psCSString,ISNULL(NULLIF(CHARINDEX(',', @psCSString), 0),
                               LEN(@psCSString)) + 1, LEN(@psCSString))
  INSERT INTO @otTemp VALUES (@sTemp)
 END

RETURN
END

Which will return you the table of IDs when passed in format : '1,2,3,4'

Such as :

select *
from fn_GenerateIDs('1,2,3')

Results:

+----+
| ID |
+----+
| 1  |
+----+
| 2  |
+----+
| 3  |
+----+

And then you would have to pass parameter in varchar format : "1,2,3,4,5" and compare it with the Employee table as :

    select * from emp where    
    EXISTS ( select 1 from fn_GenerateIDs('1,2,3') where employeeID = sID)

Another option if you are using SQL Server 2016 is to use a newly added function STRING_SPLIT

Which require 2 parameters: String of ids, and a separator

Such as:

select * from dbo.emp
inner join string_split('1,2,3,4',',') on dbo.emp.employeeID = value