August 13, 2013 at 2:03 am
Hi,
SSIS hangs when I try to select data from STG to F table based on modified info in the same F table. That is:
Neither of the selects below work - the GUI shows that 49970 rows are fetched and 9994 rows are inserted into target and then the load hangs...
SELECT ...
FROM STG_Table
Where
Modified_Date > (Select IsNull(MAX(Modified_Date), '1900-01-01') From F_Table);
With F_Modified AS
(
Select IsNull(MAX(Modified_Date), '1900-01-01') Modified_Date From F_Table
)
SELECT ...
FROM STG_Table JOIN F_Modified ON STG_Table.Modified_Date > F_Modified.Modified_Date;
There is no problem when run in MS Query window and why should there be, it is normal sql with no locking issues...
In SSIS it only works, if I first insert the max(modified) into another table and use that in the Where clause instead.
To make case more confusing, another similar load in the same DB works OK (same amount of rows etc...). Only difference is that it contains no other objects than Source and Target whereas the failing one contains three lookups to other tables.
What kind of "feature" is this?
Ville
August 13, 2013 at 2:18 am
... why should there be, it is normal sql with no locking issues...
Except for the fact that you are using the F table in the source as well as in the destination in your SSIS package.
You can use the NO_LOCK query hint to avoid locking, but this can cause dirty reads so I'd rather avoid it.
The "clean" way in my opinion would be to put an Execute SQL Task before the data flow, use it to find the maximum data and store it in a variable. Then use this variable as a parameter in your OLE DB Source.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 13, 2013 at 2:23 am
"Except for the fact that you are using the F table in the source as well as in the destination in your SSIS package."
But isn't the Sql in the Where sub select only run once before the actual select starts?
And why it works perfectly OK in another package and always in Query window...?
But the solution you suggested is fine but I'm still confused
Ville
August 13, 2013 at 2:26 am
Ville Lucander (8/13/2013)
But isn't the Sql in the Where sub select only run once before the actual select starts?
It should be, but you can only know for sure by looking at the actual execution plan.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply