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?