SSIS comma delimited string in where clause

Question

I am trying to see if there is an easy answer for this. I have done something similar using multiple pick dropdown parameters in SSRS but this appears to be different.

My scenario is this, so maybe there is an even better answer.

I have a production server that I do not want to make any changes to including temp tables or functions. The production server has a table of clients with about 1600 records. I have set up an SSIS package that will allow transfer of data from production to dev based on a clientid. So my sources would have a query similar to Select Field From Table Where ClientId = ?

This works fine. Now I want to load more than one client, based an data in the clients table. It may be Select ClientId From Clients where Field = A and returns multiple ClientIds.

I am able to populate a comma delimited list from an execute sql task to a SSIS variable, so it maybe 1,4,8.

If I change my source query to use ClientId in (?) I get a conversion error.

I have looked at many posts that advocate a temp table or a function which I want to avoid. Select IN using varchar string with comma delimited values

I have contemplated building the entire sql statement into a variable but this don't seem like the right path as I have many tables to query and transfer where using ClientId = ? works well without having to build each individual SQL statement to a variable.

Is there an easy fix I am missing? I will turn my research now to try to find out how I did this in SSRS but I thought that I should try a post here to see if someone has accomplished this before.

I appreciate any info on this, thank you.

EDIT: Key note is that the column on clients is on the dev server, so I cannot just use a select in the where clause as the column does not exist on the production server.

EDIT: I did not mention that I am specifically looking at OLEDB sources mapping a parameter to ? in the sql statement.

EDIT: Narrowing down on this but having trouble relating SSRS and SSIS functionality. In SSRS its called a multi-value parameter in the following link the key line is

WHERE Production.ProductInventory.ProductID IN (@ProductID)

https://msdn.microsoft.com/en-us/library/dn385719(v=sql.110).aspx

This one looks good as well https://sqlblogcasts.com/blogs/simons/archive/2007/11/22/RS-HowTo---Pass-a-multivalue-parameter-to-a-query-using-IN.aspx

I will keep researching and thank you for the help so far.

I think this sums it up best

This functionality is limited to strictly using embedded SQL.

What SSRS does is transform your SQL column IN (@value) to column IN (@selectedvalue1,@selectedvalue2) etc.

You need to forget anything you have about the other ways of passing lists to SQL i.e. building strings etc. and make sure you declare the data types are correct for the value of your parameter.

You do not need to use the Join(parameters!,",") trick UNLESS you are passing the list to a stored procedure.

In which case you then need to use some function to turn the delimited list into a rowset as you have done.

I hope that helps

The core question is if I can get the same functionality in SSIS as in SSRS. It reminds me of macro substitution..

Answer 1

If you dont want to create a function, you can use the following in your t-sql statement.

 Declare @ClientIds nvarchar(50) = '123,456'; --<-- Comma delimited list of Client Ids


 Select Field 
 From Table 
 Where ClientId IN (
                    SELECT  CAST(RTRIM(LTRIM(Split.a.value('.', 'VARCHAR(100)'))) AS INT) ClientIDs
                    FROM (
                            SELECT Cast ('<X>' 
                                        + Replace(@ClientIds, ',', '</X><X>') 
                                        + '</X>' AS XML) AS Data
                            ) AS t CROSS APPLY Data.nodes ('/X') AS Split(a) 
                    )