SSIS: enrich query and table with input file as base

Sofian 1 Reputation point
2021-07-07T16:29:43.213+00:00

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

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

And then another query with

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

Etc...

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
    2021-07-08T07:10:41.037+00:00

    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:https://stackoverflow.com/questions/4475508/ssis-want-to-update-a-sql-table-based-on-a-flat-file

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

    https://itssmee.wordpress.com/2010/10/03/ssis-insert-and-update-rows-in-a-table-based-on-the-contents-of-a-excel-file/

    Regards,

    Zoe


    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