Does anyone know why SSIS sometimes just sits in the Pre-Execute phase of a data flowand does nothing? It doesn't matter how elaborate the data flow isor the volume of data. It can sometimes take 80% of the task's run time.

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 runsinterminably (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,frankpostscript. 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 andit 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...

dimaj79 wrote: 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... MVPs are not employed by Microsoft and we're not paid for what we do - hence we're not required to be here. Neither are Microsoft employees as far as I am aware. I (and others undoubtedly) have not replied because I simply don't know what the problem may be - I really wish I did. Ihave never experienced these problems even though I HAVE accessed oracle from SSIS.My first avenue of investigation would be to try and replicate the problem outside of SSIS but using the same drivers. Scott Barrett has loads of material on accessing Oracle from SSIS: http://microsoftdw.blogspot.com/he may be able to help you. Scott heavily recommends using the Persistent drivers: http://www.persistentsys.com/products/ssisoracleconn/ssisoracleconn.htm (unfortunately the website seems to be down currently) In my experience problems like this are related to drivers rather than SSIS. That's not me trying to deflect attention away from SSIS - just a pointer as to where to explore. Its also true to saythat SSIS asks for more information from providers than DTS ever did so drivers that workedwith DTS may not work with SSIS. DTS just disguises the fact that the drivers are not up to the job. Nothing too much specific in there I'm afraid but hopefully something will trigger a light bulb for you! -Jamie

Sorry, didn't mean to offend anyone... My problem is obviously dynamic SQL execution from OLEDB Source packages against Oracle, and even from DataReader Source packages. I tried several solutions, none of them produced results. 1. I tried the expression-type string variable like you suggest in your blog. It hangs in pre-execute. When I open the session manager for Oracle I see the session as INACTIVE with correct sql statement. Same SQL statement when executed from SQL editor runs just fine. I tried this approach with both Microsoft and Oracle OLEDB drivers. 2. I tried using a similar approach to the first one except I construct the entire SQL query string from script component in VB.NET. Again, the session sits there as inactive, SQL query is the same as in 1. and should work fine, but SSIS package just sits there in pre-execute. 3. I tried DataReader Source task with and expression set on it's SqlCommand to be equal to a variable which I set from script task. Again, same behavior. No difference whatsoever. At first I started to suspect my SQL query. Since I am using date comparison in it, I figured something was wrong with it. But then I just copied it from session manager into a sql editor and ran it and it worked fine. So, it is definitely not a query problem. However, it can still be something closely related to the query. BTW, I have other packages with exactly same topology, only simpler queries executed against small tables, and they work fine. For instance, in one of them I have a script task that constructs the query substituting value from a variable for table name in sql query and then I run it in a foreach loop. Works like a charm. For it, I use the 2. approach. I can't really explain why the small query works fine and complex one hangs. I have another package in which I use 2. approach that I mentioned in one of my previous posts in which I construct a query similar to the one that works fine only slightly more complex to include a couple of joins. And it hangs like the one with dates. I am pretty much stuck on this and any help would be greatly appreciated. And if any of the Microsoft guys are reading this, we are seriously thinking of abandoning the SQL Server platform due to these issues with Oracle.

It's hard to believe, but I actually managed to resolve this issue. Here's what the problem was: It has nothing to do with dynamic sql from SSIS against Oracle. In fact, all three of the possible approaches should have worked fine, if it wasn't for a bug in SSIS. It seems to be a known issue, since I found messages on the internet in which other people state they had the same problem. I am not sure whether it is resolved yet or not by Microsoft, but it should be and soon, since its' next to impossible (pure luck) to resolve. The bug is that if you add a lookup transformation to a data flow task, SSIS will sometimes hang when executing it, unless you RECREATE (stupidifying, isn't it) the SQL Server Destination task (or whatever destination task you have). Simply deleting the destination task and creating a new one with exact same parameters resolves the issue and the package execution no longer hangs. Dynamic SQL execution works like a charm.

i have changed tack with lookups as they seem hard to debug. performance errors like you describe and matching errors when you think it should match (not found the correct way to debug these) in my source ive started joining in the lookup table and returning my lookup values as a normal column, seems to work faster than the lookup task caching rows! colin

For me it had nothing to do with lookups, since I had none in that task. In fact for debuggingI pared down the task to just the OLE DB Source and a Row Count. I looked at SQL Server Activity Monitor and it showed my process hung up doing a PAGEIOLATCH_SH for ~10 minutes -a query thatruns in query analyzer in under 10 seconds! I have no idea why this worked butmoving the SQL to a stored procedure and calling it in the OLE DB Sourcesolved the problem entirely. If anyone knows why this would make a 60X performancedifference I'd love to hear it. Here's the query. The Position table is several million rows but indexed on the relevant columns: SELECT evt.EventID, evt.EventTypeID, evt.VesselID, evt.POIID, evt.EventTime AS EventUTCDate_Raw, dbo.ufn_UTCToLocalDate(evt.EventTime, pos.Latitude, pos.Longitude) AS EventLocalDate_Raw, evt.LastChangedDate AS LastChangedUTCDate_Raw, dbo.ufn_UTCToLocalDate(evt.LastChangedDate, pos.Latitude, pos.Longitude) AS LastChangedLocalDate_Raw, pos.Latitude AS PositionLatitude, pos.Longitude AS PositionLongitude, pos.PortVisitID AS PositionPortVisitID FROM [Event] AS evt INNER JOIN [Position] AS pos WITH (NOLOCK) ON pos.PositionTime = evt.EventTime AND pos.VesselID = evt.VesselID WHERE (evt.LastChangedDate > CONVERT(datetime, ?)) AND (evt.LastChangedDate <= CONVERT(datetime, ?)) ORDER BY evt.VesselID, evt.EventTime Dave

This is a much bigger problem -- My situation is -- I dont have a LookUp Task -- yet it takes about 3 minutes to run the Pre-Execute phase, on a query that DOES NOT return any rows. Here is whole situation, I call this Data Flow Task -- which is inside a Loop. I loop though every single month from 1990-01-01 to 2015-12-31 and load data from the Stage Table into Production I take a wide range -- because I do not know what range of data will exist in the Stage Table, so I decided to be conservative and adopted a wide range. Now my Stage Table has data foe ONLY 1 Year (from 2006-01-01 to 2006-12-31)-- but the loop goes through starting from 1990-01-01 and just does not nothing in the Data Flow Task -- only problem even when the query is running for 1990-01-01 to 1990-02-01 the Pre-Execute Phase of the Data Flow Task -- takes about 2 minutes -- In essence I could be wasting 3 Hours in the Pre-Execute Phase of the Data Flow Task -- even if there is No Data to enter. I know, I know -- I can GET Around this problem -- by providing exact dates as in Stage Table -- but QUESTION is -- What is Wrong with the Pre-Execute of the Data Flow Task ???

JaguarRDA580842 wrote: This is a much bigger problem -- My situation is -- I dont have a LookUp Task -- yet it takes about 3 minutes to run the Pre-Execute phase, on a query that DOES NOT return any rows. I also have the same problem. What I found was that SSIS seems to be reading the WHOLE source table, even though the source query will return no rows. Now for my table which has 300,000 rows the pre-execute phase takes a few minutes, even if the query I specify (using SQL command) returns no rows. Now the interesting thing is if I delete all rows in the table, the pre-execute phase pretty much returns instantly! This seems to be consistent with what another user has posted about why their package would work with small tables but work very slowly with large tables.I'm running SP2. Unfortunately, having a look at the hotfixes for SP2 doesn't show any fixes for this problem either. The fix suggested by a previous user whereby if you recreate the database destination doesn't seem to work in my case. Any other suggestions greatly appreciated.

I'm also having the same problem. I have three different OLE DB datasources (I laso tried the MAXDOP option solution, but didn't help). And seems that the three queries are getting executed during the pre-execute phase. It's funny to see that if I simplify the queries, retrieving whole data table with select *, instead of the inner join I'm doing, then the pre-execution phase goes much quicker and the queries seem to be executed during the execution phase.Any clue? my queries last about 2 minutes each in management console, but seem to take more than 30 min to work in SSIS.

vrivero wrote: I'm also having the same problem. I have three different OLE DB datasources (I laso tried the MAXDOP option solution, but didn't help). And seems that the three queries are getting executed during the pre-execute phase. It's funny to see that if I simplify the queries, retrieving whole data table with select *, instead of the inner join I'm doing, then the pre-execution phase goes much quicker and the queries seem to be executed during the execution phase.Any clue? my queries last about 2 minutes each in management console, but seem to take more than 30 min to work in SSIS.Make sure that the connection managers for Oracle and the OLE DB sources are all set to DelayValidation ( = True).

Yes, I have also set that, but anyway I'm not dealing with any Oracle database, there are two oledb connections to sql server 2005 servers

vrivero wrote: Yes, I have also set that, but anyway I'm not dealing with any Oracle database, there are two oledb connections to sql server 2005 serversAh, now that I'm sitting in front of SSIS, what I meant to say was that for the OLE DB Source in the data flow, ensure that ValidateExternalMetadata is set to false in your case.

vrivero wrote: It's funny to see that if I simplify the queries, retrieving whole data table with select *, instead of the inner join I'm doing, then the pre-execution phase goes much quicker and the queries seem to be executed during the execution phase.Any clue? my queries last about 2 minutes each in management console, but seem to take more than 30 min to work in SSIS.When you are executing the query in SSMS, are you using the same user credentials that SSIS is using? I would not be surprised if SQL Server has stored a bad query plan that is being reused for the SSIS query, but not when you execute in SSMS. Try logging into SSMS using the same credentials that SSIS is using and executing the query. Or use the Profiler to get the query plan that SSIS executes and compare that to what you see in SSMS.

Well I discovered that it seems that it's related with the use of parameters in two of these queries (oledb source), since when I test the packake with values instead of the values, it works perfectly. This is my query: Code Snippet SELECT dbo.[tab1].* FROM dbo.[tab2] inner join dbo.[tab1] on dbo.[tab2].IDVTE = dbo.[tab1].IDVTEwhere dbo.[tab1].[DATEHEUREVTE] >? OPTION (MAXDOP 1) and this is what I saw in the profiler: Code Snippet declare @P1 intset @P1=1exec sp_prepare @P1 output, N'@P1 datetime', N'SELECT dbo.[tab1].* FROM dbo.[tab1] inner join dbo.[tab2] on dbo.[tab1].IDVTE = dbo.[tab2].IDVTEwhere dbo.[tab2].[DATEHEUREVTE] > @P1 OPTION (MAXDOP 1)', 1select @P1 I'm not an sql server expert, but I that output parameter sounds not very good for me Thank all you guys, you're really helping me in this issue

Well, finally I manged to solve it using the solution of storing the queries in a user variable as expressions. I can't believe how to solve such an easy thing, it has been so difficult. Well at least I have learnt for the next time. Thank you everybody.

I have the same issue and from the Activity monitor I found that there are few quieries which has occured in Page lock. Then I chnaged the queries so that page lock didn't occur and it is working fine now. Basically I introduced temporary tables.

I too had the same problem and was able to solve it by using variable in the SQL Source. I'm not sure why this happens, but it seems to me that SSIS is trying to run a query for the entire table to determine it's metadata. Thanks, Mario Campos

I had this scenario: Long SQL statement with 1 where statement: Code SnippetWhere ImportDt > ? With the ? parameter mapped to a date variable. That worked fine. I changed it to support snapshots of data to this: Code SnippetWhere ImportDt between ? and ? With ?1 = start date variable and ?2 = end date variable. The pre-execute on this statement took over 30 minutes. I changed the query to look like this: Code Snippet Where ImportDt Between ? and ? And ImportDt > ? With ?1 = start date variable and ?2 = end date variable and ?3 = the start date variable again. Pre-execute is down to < 1 second. I sure hope this is fixed in SSIS2008... peace BobP

OK, for me, this was NOT an SSIS issue. The Pre-execute status is when the query is actually executed. Once rows are returned, the status is changed to execute. What was happening in my instance was the sp_prepare and then the sp_execute were occuring, however, the sp_execute was not completing. I could run the query in sql manager, or do an sp_executesql and it would return in < 1 second. So what I did was update statistics, and then the sp_execute worked fine. Hope this helps someone. peace BobP

Ok... I've been through this thread a couple times and cannot seem to get my SSIS package any faster. I have 27 tables I am moving from one SQL Server database on one machine to another... very simple SELECT * type moves with a simple WHERE clause. The queries are in stored procedures and workvery fastwhen executed within SQL Server Mgmt Studio. I am attempting to pass about 2 million rows from one database to another (that is, 2 million rows x 27 tables). I have grouped these 27 tables in 3 Data Flows and put in FOR loops based on row sizes. I've seen a recommendation for turning off caching, since this is what is happening in Pre-execute phase, but I am not sure how to do this? I do have: Delay Validation set to True, I have balanced my DefaultBufferMaxRows and DefaultBufferSize to correspond to the table's row sizes (plus, I set the individual 'SELET top X *' in my stored procedures to be below the MaxRows setting), I have set the Ole Db Destination FastLoadMaxInsertCommitto match the Row sizes, set ValidateExternalMetadata off on both the Ole Db Source and Destination, the only otherData Flow element is Row Count Each FOR loop takes minutes to perform, each time getting stuck on the Pre-Execute part. I admit to being novice user to SSIS and cannot speed up the performance with anything I've tried so far. Is part of this limiting the number of tables in a Data Flow? Like only have 5 tables per Data Flow? (that seems odd)

Hi, I work in SSIS team. If you could send some package on my e-mail - I would have a look at that. My e-mail is evgenko at microsoft.com Thanks, Eugene Koblov SSIS team

I removed the FOR loops, made the DefaultBufferMaxRows=5000000 and kept DefaultBufferSize at 10Mb. The FOR loops were not necessary, as I'm learning, and causing Pre-Execute to run again and againto kill the timeliness of the package when it runs. I'm not sure what has really changed from before beyond this simple transition, but it is working much faster now. However, I am still having major issues with sys.sp_getschemalock issues on my other thread http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3636437&SiteID=1

The solution is in the query, not the settings.I was loading SQL Server data to Oracle using OLE DB connections. In the data flow section...When you specify your source and destination, supply a sql command as the data source. In that command ensure you hit an index. When hitting the index of the destination, your where clause doesn't need to point to an existing record, but it must hit the index. This is how the SSIS determines the column mapping. If you don't supply an index to hit in the where clause, SSIS does a full-table scan on the tables during the Pre-Execute phase. This is likely the cause most of the time users run into this issue.Example:Assume IdProduct is my index, SQL Server is my source, Oracle is the destination. ProductIDof 2008000000 doesn't exist in either table, and I am passing the data for IdProduct, which will fill in the paramter.SourceOLEDB- SQL Command:Select * from [SQL Server Table Name] where IdProduct = ?DestinationOLEDB- SQL Command:Select * From [Corresponding Oracle Table Name] where IdProduct = 2008000000

I had the same behavoir (the package did not continue in pre-execute fase 77/88% in ssis 2008). Inmy case was the fix to deselect labellocking in the destination. When the destination table was empty, the package runned fine, with records in the destination table it did not run at all.

I had the same behavoir, but what I found was that my server's memory was being pegged, until SSIS released the resource, in my case it was a 3 GB lookup. I only see this within the SSIS visual studio eviroment.

My experience has been that if huge amount of rows (in my case it was 300,000+) are being fetched by a single connection (in my case it was OLE DB), using a single query or SP, while executing in BIDS or even using DTExecUI, PreExecute takes a long time.But if you divide this into say 3 connections, which reads from of different ranges like (1-100,000)-(100,101 - 200,000)-(200,001-300,000) and then use UnionAll to join the same (would seem like an awkward soln) or instead of pulling a huge amount of data and then putting a conditional split to filter it in transformation, put such filters at the database side, it runs quite faster.In summary, if the data is too huge to fetch from a single connection, try the option of bringing this data in chunks thru different connections by splitting the data, and then merge it in transformation, and it should work considerably faster. This has been my experience or one can say an awkward workaround. But this has always worked for me.--Siddharth Mehta http://siddhumehta.blogspot.com

A few days ago I was also hit with the long duration pre-execute phase issue. It first showed in the validation part, but after following suggestions seen here and elsewhere to turn off the external metadata validation the issue then turned into a hanging pre-execute phase. I fixed it so I thought I'd put it up here just in case it might solve other people's validation/pre-execute phase hang troubles. It's actually quite silly, but easily overlooked. Firstly, my SSIS package: It's simple, a single Data Flow Task which looks like this: [ADO NET Source (mysql)] -> [Derived Column Transformation] -> [ADO NET Destination (SQLServer 2008)] I use an expression on the Data Flow Task in the Control Flow window to create the SQL statement for the ADO NET Source. The form is as follows: "select field1, field2, field3 from mysql.table where field1=... " Done multi-line for ease of reading. I suppose one would often copy this from another query window somewhere while testing the sql statement. Hitting "Evaluate Expression" in the expression editor also produces a properly formatted multi-line statement. Copying it out to a mysql query window sees the query run just fine. No suggestion I tried would fix the hanging pre-execute. But by some chance I spotted the sql statement on the properties window for the Data Flow Task. [ADO NET Source][SqlCommand] --> selectfield1,field2,field3... The multi-line statement is reduced to a single line by removing the newlines. Simply adding a space after the "select" and ensuring everything is spaced properly resolved the issue of the hanging pre-execute (or validation) phase. It did not resolve the issue of my 3 lost hours trying to figure it out. :( I also thought that this might also be one of the reasons that recreating some of the task items would fix the hang issue. Could it be that when re-entering the sql statement one "fixes" it? So that's one more thing worth checking when things hang. Just check that the sql statement reduces to a properly formatted single line statement. Or perhaps someone on the SSIS team could replace newlines with spaces instead. :) Also, in my mind that statement should have been an outright fail during the run. I suspect things are waiting for the mysql provider under the ADO .NET item to give back some useful error. Perhaps also the same story with other db drivers. I'm no big fan of mysql. Even using it as a linked server on my SQLServer2008 box will send it nuts if I have more than 1 linked mysql server. Things are just not compliant.

FWIW - I share this exact experience. I hate to drop my OLE DB Destination, recreate it, and everything worked fine. I suspect it gets corrupted if you add columns to the flow over time.....

Me too - Pre-Execute just hangs at 43% forever. Going from MS Access to SQL Server 2008. The source db is tiny, no table over 50,000 rows, yet no matter what I do it just hangs in pre-execute forever at 43% completion. I gave up and wrote some C# to do it in under a minute. Horrifying experience with SSIS all around. Its pretty to look at, but invariably you'll end up pulling your hair out.