Problem passing list of int's in OLE DB Source parameter

Question

I am using SQL SERVER 2012 and Visual Studio 2017 with SSDT (SQL Server Data Tools) installed, and have a query like the following in an SSIS OLE DB Source:

The parameter is set up like this:

and the ClientIDList variable is a string that has 1 or more int's delimited by commas.

When ClientIDList has only one value (e.g. 999000), the query returns as expected. However, if the variable has 2+ values (e.g. 999000,999001), it returns the following error:

So far, the only way I have found to resolve this is to create a string variable using the following expression:

"SELECT Name, Email FROM Customers WHERE ClientId IN (" + @[User::ClientIDList] + ")"

If I then use the 'SQL command from variable' option and pass the query as a variable, it works with both 1 and more than one values in the WHERE clause. While this is one solution to my problem, it is not very nice in terms of readability and maintainability for other developers. Does anyone know how I can make my original approach work?

Thanks!

Answer 1

Hi @Ryan Rigby ,

Please check if the datatype of column ClientId is int.
If yes, please write number value in variable ClientIDList.

Please refer to the following pictures:

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

So far, the only way I have found to resolve this is to create a string variable using the following expression:

"SELECT Name, Email FROM Customers WHERE ClientId IN (" + @[User::ClientIDList] + ")"

If I then use the 'SQL command from variable' option and pass the query as a variable, it works with both 1 and more than one values in the WHERE clause.

The dynamic SQL approach you mentioned is the easiest.

The other approach would be to create a stored procedure that receives a parameter as (1) string or (2) XML or (3) JSON that contains a list of integers.

• (1) STRING_SPLIT() function and (3) JSON API would require SQL Server 2016.
• (2) XML would work starting from SQL Server 2008 onwards.

Here is a conceptual example for you.
SQL

DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, vehicleMake VARCHAR(20));
INSERT INTO @tbl (vehicleMake) VALUES
('Chevrolet'),
('Tesla'),
('Audi'),
('Nissan');

DECLARE @ParameterList VARCHAR(max) = '1,2';

-- XML and XQuery
-- SQL Server 2008 onwards
DECLARE @separator CHAR(1) = ',';

;WITH rs AS
(
    SELECT xmldata = TRY_CAST('<root><r>' + 
         REPLACE(@ParameterList, @separator, '</r><r>') + '</r></root>' AS XML)
)
SELECT tbl.* 
FROM rs CROSS APPLY xmldata.nodes('/root/r/text()') AS t(c)
 INNER JOIN @tbl AS tbl ON tbl.id = c.value('.','INT');

-- STRING_SPLIT()
-- SQL Server 2016 onwards
SELECT t.*
FROM @tbl AS t INNER JOIN 
     STRING_SPLIT(@ParameterList, ',') AS ss
  ON t.ID = ss.value;

Output

+----+-------------+
| ID | vehicleMake |
+----+-------------+
|  1 | Chevrolet   |
|  2 | Tesla       |
+----+-------------+