SSIS: enrich query and table with input file as base

Sofian 1 Reputation point

Hello everyone

I need to extract data from a DB2 database to a SQL Server. I need to create my query based on a excel file I have 176 records, which I need to create repeating queries & put in SQL server
So for example;

I have an excel with a number, From date, To date ,and a country
So the query should use these information from the records

FROM dbo.Test
WHERE Number = excel.Number1 AND Date BETWEEN excel.fromDate1 AND excel.toDate1 AND Country = excel.country1

And then another query with

FROM dbo.Test
WHERE Number = excel.Number2 AND Date BETWEEN excel.fromDate2 AND excel.toDate2 AND Country = excel.country2


How should I do something like this in SSIS?

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

1 answer

Sort by: Most helpful
  1. ZoeHui-MSFT 32,406 Reputation points

    Hi @Sofian ,

    Found a similar thread like your issue and someone provided two methods.

    1.Use an OLEDB Command component within the data flow. The downside is this calls the statement for each record, so if you have 1,000s of records it is very inefficient.

    2.Push the records to a table using an OLE DB Destination and then you can call your update using an Execute SQL Task.

    Details for your reference:

    You may also refer below blog to insert and update rows in a table based on the contents of a Excel file.



    If the answer is helpful, please click "Accept Answer" and upvote it.

    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.
    Hot issues October