I have an SSIS package on server 1. It does a SQL query on SQL db located on server 2 via the OLEDB source sql command text. The query is:

SELECT * FROM PRODUCTS
WHERE PRODUCT_NAME IN (?)

This fails, since? is a scalar value and not a table. To fix this there are 2 options:

1. Use STRING_SPLIT
2. 

   Using OLE DB Source SQL Command

   If you are using an OLE DB Source component, you can use the following SQL Command:

   DECLARE @String varchar(100) = ?;
   
   SELECT * FROM PRODUCTS
   WHERE PRODUCT_NAME IN (
   SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value' 
   FROM  
   (     
   SELECT CAST ('<M>'   REPLACE(@String , ',', '</M><M>')   '</M>' AS XML) AS Data            
   ) AS A 
   CROSS APPLY Data.nodes ('/M') AS Split(a)
   )
   

   Test

   Using the AdventureWorks2017 database, I used the following query to search for the person's information stored in the [Person].[Person] table while the filter is on the PersonType column:

   DECLARE @String varchar(100) = ?;
   
   SELECT * FROM [AdventureWorks2017].[Person].[Person]
   WHERE PersonType IN (
   SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value' 
   FROM  
   (     
   SELECT CAST ('<M>'   REPLACE(@String , ',', '</M><M>')   '</M>' AS XML) AS Data            
   ) AS A 
   CROSS APPLY Data.nodes ('/M') AS Split(a)
   )
   

   

   In the OLE DB Source Editor, if we click on the Parameters button, a parameter is recognized. I will create a new variable and use it as a parameter as shown in the image below:

   

   The variable data type should be set to String and the value is set to EM,SC which are both symbols used in the PersonType column.

   

   Now, if we click on the Preview button in the OLE DB Source Editor, the accurate data is visualized.