Not able to use comma separated values as parameter inside Sql command editor in SSIS

Question

Hi Team,

I have two databases on different servers. I need to fetch comma separated list from one database and use that list in the query inside IN clause for other database query.

I tried below approach but it didn't worked-

1. Create Execute sql task to fetch the comma separated list and mapped it to a variable.
2. Create another variable to store dynamic query.
3. Used the variable created in step 1 inside dynamic query.
4. Inside dataflow task, created ole db source.
5. Under Data Access Mode category, selected Sql command from variable and then selected variable created in Step 2.
6. But its giving error as Statement could not be prepared because the variable storing data from database will only have value at runtime else it will be blank.

Please suggest any other approach.

Answer 1

Hi @Jaiswal, Manali ,

SSIS has Merge Join Transformation for that. No need to deal with comma separated lists.
It is a transformation that allows joining data from two sorted datasets using a FULL, LEFT, or INNER join.

So, your Data Flow Task will need the following:

• two OLEDB sources
• Merge Join transformation
• OLEDB destination

Useful link: ssis-basics-using-the-merge-join-transformation

Answer 2

Hi @Jaiswal, Manali ,

As YitzhakKhabinsky-0887 said, the Merge Join transformation provides an output that is generated by joining two sorted datasets using a FULL, LEFT, or INNER join.

Which is more easier and will avoid a lot of complicated steps to meet your needs.

merge-join-transformation

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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 3

Hi @Yitzhak Khabinsky , @ZoeHui-MSFT

Thank you for your response.

I have also followed the above steps before but the issue is that first query will bring all data from the database which is not required. The data is large in number and it will affect the performance of job. We only want to fetch the filtered data.

Thanks and Regards,
Manali