question

Sofian-3711 avatar image
0 Votes"
Sofian-3711 asked ZoeHui-MSFT commented

SSIS: enrich query and table with input file as base

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered ZoeHui-MSFT commented

Hi @Sofian-3711,

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

May I know how's the issue going on now? If you have any updates, don't forget to tell us.

0 Votes 0 ·