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

Jaiswal, Manali 1 Reputation point
2022-03-03T11:11:11.573+00:00

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.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,466 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Yitzhak Khabinsky 25,201 Reputation points
    2022-03-03T14:23:31.697+00:00

    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

    1 person found this answer helpful.
    0 comments No comments

  2. ZoeHui-MSFT 33,386 Reputation points
    2022-03-04T01:57:52.393+00:00

    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.

    0 comments No comments

  3. Jaiswal, Manali 1 Reputation point
    2022-03-04T09:11:06.737+00:00

    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

    0 comments No comments