December 6, 2018 at 3:39 pm
I have a dataflow. It has
2 sets of
OLEDB Source, Data Conversion transformation, and Excel Destination
in it
(each source-destination writes to a different spreadsheet within same workbook inside For Each Loop).
Everything works. Takes about 60 minutes to complete altogether, queries are quite heavy.
However, when I disable one source-destination and run it, it takes 10 minutes. 15 minutes max.
2 SQL queries in each OLEDB Source are different but they do access several same tables among other objects
(I do set transaction isolation level read uncommitted and/or set deadlock_priority 10 (for testing purposes...) at the beginning of my SQL statements.
If I disable another source-destination and enable the first one, it runs also 10 to 15 minutes. No more than 30 minutes altogether. Same when I run both queries on SQL Server side at the same time, they take no more than 30 minutes both.
If both source-destinations are enabled, the package takes close to an hour to complete.
Where does my additional half an hour overhead coming from when i run the full enable data flow with 2 subflows? its only a few hundred rows written to each spreadsheet (2 spreadsheets per workbook), package produces between 300 and 400 hundred output XL files altogether.
Is there any setting at SSIS level that i could change to help shorten the length of runtime in my scenario?
Likes to play Chess
January 21, 2019 at 11:57 am
What about just running these in series or separate into separate Data flow tasks or even separate packages and launch them using Exec Package tasks.
I know at some point i read something regarding resources available within single executables/packages. Separating these may allocate more resources.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply