Are you able to monitor activity on the source system? Perhaps the problem is there.
A shot in the dark - perhaps the problem is the one documented here: http://blogs.conchango.com/jamiethomson/archive/2006/02/21/2930.aspx
Just a thought!!!
-Jamie
|||Another possibility is that the package has lookup with a large reference table and the lookup is charging its cache for all this time (charging the cache is done in PreExecute).
Thanks,
Matt
|||I'm having the same trouble, but no lookups. First task in my package is a data flow, which consists of an OLE DB Source, Row Count transformation and a Flat File Destination. Source is a SELECT <fields> FROM <tables and views>, and should return about 2.1m rows. When I debug in BIDS, the task turns yellow on the "Control Flow" tab, but none of the components on the "Data Flow" tab turn yellow. Is it even executing? It must be doing something, because sp_lock and sp_who2 reveal locks being granted to that SPID. This query runs in about 35 mins in SQL Server Management Studio, but runs interminably (I killed it after 3 hours) in SSIS. Why? Help!
thanks,
Matt
|||the same problem here... packages that repitively get stuck in the pre-execution phase.right after the status level doesn't rise any more, the server seems to take a deep nap: no activity shown in the system's performance monitor any longer!
and these are packages that have run until last week!
i haven't seen anything like that during my last 10 months of ssis-development. despair takes place, i just don't know what to do... any suggestions?
cheers,
frank
postscript. yes, there are lookups involved -- yet, kind of modest ones: six lookups à 16 rows...
|||got it. it's an issue of performance:
i've split up my puzzling task into two smaller tasks... and now, the package decently works again.
unfortunately, i've got the same problem in another package as well. there, it might be harder to find some solution.
i guess, the server's not properly configured.
|||
Read the thread in this forum titled "SSIS Performance 32 Bit V 64 Bit". This solved the problem for me.
To summarize, I'm developing on a 32-bit machine, hence using the 32-bit OLE DB data source driver. When that driver runs in the context of the 64-bit DTExec, I have these inexplicable performance problems with large and complex queries. When I shell out to call the 32-bit version of DTExec, it works like a champ.
Prefered fix of course is to develop on a 64-bit machine.
HTH,
Matt
I had the same issue and it turned out to be parallelism. I set the maxdop 1 and havent had a problem since.
BobP
|||I have the same issue. I have a lookup component that is bound to a table in sql server database. The table has about 20*10^6 rows so it's pretty big, but I am not sure it is even executing the query since it doesn't display caching progress, just sits there in pre-execute. In my ohter packages I have other lookup components on the same table that work fine which confuses me even more. This package has a couple of more OLE DB sources connected to Oracle. Anyone have any idea what SSIS is doing while sitting in pre-execute?|||
dimaj79 wrote:
I have the same issue. I have a lookup component that is bound to a table in sql server database. The table has about 20*10^6 rows so it's pretty big, but I am not sure it is even executing the query since it doesn't display caching progress, just sits there in pre-execute. In my ohter packages I have other lookup components on the same table that work fine which confuses me even more. This package has a couple of more OLE DB sources connected to Oracle. Anyone have any idea what SSIS is doing while sitting in pre-execute?
You can check the source system to see if the query is being executed. If the source is SQL Server then use SQL Server Profiler. If its Oracle then I dare say they have something similar.
If I remember correctly you get information in your log provider when the cache is getting charged. So make sure you are logging OnInformation events.
-Jamie
|||Ok, I have turned off caching in all of my lookups in that data flow task, and it still gets stuck in Pre-Execute phase. I am sure it's not executing data source queries since the debugger doesn't show them in yellow. When I said I wasn't seeing any caching progress I meant in the output window. I've turned off caching now so it shouldn't make a difference anyway.
I noticed someone mentioned performance issues and that breaking the task into multiple tasks solved it. Could you please elaborate?
If someone from Microsoft knows about issues with SSIS data flow tasks locking up due to some root problem deep inside SSIS or SQL Server please respond. Obviously people are finding different ways to get around it so there must be a root problem causing all of this. At least in future we'll know how to avoid it if not completely solve it. I would at least be happy to find out what it's doing in Pre-Execute phase that is taking it so long... That way we might think of a way to avoid it.
Like I said, the reference table is big, about 20mil rows, but lookup tasks have no caching set and there's no reason for the long pre-execute phase, unless it's doing something in the background we (developers) don't know about. Someone should respond explaining exactly what's taking place in the Pre-Execute phase.
|||The task does not turn yellow when the query is running, just when rows start returning. I was burned by this also... The pre execute actually executes the query.
Look at your data source and see what queries are being run (Use profiler).
Then you can go from there.
BobP
|||Ok, here's some more info on my long pre-execute phase:
I'have verified that it does in fact run the sql query against Oracle during the pre-execute phase. However, the SSIS task hangs in pre-execute at 80% even though the sql query has finished. I have verified that in Oracle session. When running the same SQL query manually it takes about 2 minutes, but the pre-execute phase in SSIS hangs and I've let it run for a lot longer than 2 minutes. Oracle session status for the session used to execute the SQL query from SSIS pre-execute phase states INACTIVE. My interpretation of this is that the query finished (as it should have, in about 2 minutes) but for some reason SSIS task never closes the connection.
My SSIS package consists of a foreach loop on a previously filled recordset variable, that contains a script task that prepares the sql query strings into variables and a data flow task that executes those sql queries against Oracle, transforms the data and loads it into a Sql Server database. I have two data source tasks in that data flow task. Both run in parallel executing a sql query against Oracle. I have verified that Oracle sessions remain open for both tasks, and their status is INACTIVE, even though the queries executed finish in about 2 minutes. Package hangs in pre-execute at 80%. The data source I use is Native OLEDB Oracle Provider from Oracle.
If anyone at Microsoft knows of a problem with using this package setup, or data extraction problems with Oracle, please respond.
I will try switching to Microsoft OLEDB Provider for Oracle to see if it makes any difference, and post the result here.
|||No change when using Microsoft OLEDB Provider for Oracle|||Anyone?
Where are all the MVPs gone? Any clues? Or should I just write it off as another one of Microsoft's "bug/feature" things...
No comments:
Post a Comment