Why is my SSIS package taking so long?
I have been creating SSIS packages quite recently. I have the following SQL Server 2008 table named BanqueDetailHistoryRef containing 10,922,583 rows.
I want to extract rows that were inserted on a specific date (or dates) and insert them into a table on another server. I am trying to achieve this via SSIS which looks like this:
OLEDB Source (the table with the 10Million+ records) --> Lookup --> OLEDB Destination
In my search, I installed:
Now the query (specified in the Lookup transformation):
SELECT * FROM BanqueDetailHistoryRef WHERE ValueDate = '2014-01-06';
Takes about 1 second to run through SQL Server Management Studio, but the described SSIS package takes a very long time (for example, an hour).
Why is this causing? Is this the correct way to achieve the desired results?
source to share
You didn't mention how your OLEDB source component was configured, but looking at the table names, I would assume that you load a whole 10 million rows in the OLEDB source and then use Lookup to filter only the ones you need. It's uselessly slow.
You can completely remove Lookup and filter rows in OLEDB source using the same query as Lookup.
source to share