Long time SSIS package loading

I load SSIS package using following code:

Application app = new Application();
Package pac = app.LoadFromSqlServer(packageName, serverName, null, null, null);

For simple package containing 2 tasks this code executes about 20 seconds.
If I load old version (SQL2K) package from SQL2000 then it takes 5 seconds.

Is any way to increase loading speed for SSIS packages?

Do you have SSIS service running? The service caches SSIS component information; in the absense of the service it may take long time (although it is usually 2-3 seconds on reasonable hardware) to enumerate them when loading the package.|||

Thank you Michael.
I carry out additional experiments with realworld package (contains 11 tasks) at another computer.
When I start this experiment I have to wait 1.5 minutes to load package every time. Then I examine if SSIS running. I discover SSIS don't running after SP1 was applied (widely known SP1 bug). I installed post SP1 cumulative hotfixes and continue investigation. Now package loading first time during 30 seconds. When I start it second time it takes about 2 seconds. This is good result. But is there ability to increase loading speed at first time? Will be loading perfomance improved if such delay caused internal implementation of SSIS?

|||Much of the time on the initial load of the package is due to validation of the metadata of each of your tasks. In order to speed of the initial load, you could change the DelayValidation property of your package and all tasks to true. However, that's not really best practices.|||

Thank you Martin
This helps.

BTW, BOL says:
"Validating the package before it runs is a way of finding some errors before execution starts. However, it processes the package to find errors, and if no errors are found, the package runs. Because this goes through the package two times, validating a package increases the amount of processing for the package, so should be used only when necessary."

|||

Alexey Rokhin wrote:

... I discover SSIS don't running after SP1 was applied (widely known SP1 bug). I installed post SP1 cumulative hotfixes and continue investigation. Now package loading first time during 30 seconds...

The bug you quote is described in http://support.microsoft.com/kb/918644. While the hotfix fixes the service startup failure, it can't fix the cause of the problem - long time to start the service due to network configuration that timeouts requests to CRL. So the service still takes 30+ seconds to start (which caused to fail before the hotfix, this later part is now fixed).

To avoid this 30+ delay you should either change the SSIS service to autostart (thus moving the delay to the computer boot time), or better fix the network configuration as described in the KB (although it might be very specific to your environment) to either allow access to CRL, or to quickly fail and return failure status to an application accessing CRL.

|||

Thank you, Michael.
In production environment SSIS service will autostart.

|||

Alexey,

IMHO, a good practice is defining a Events class and so, debugging the code and so you'll find issues faster, i.e with F11...

pkg = app.LoadFromSqlServer(ObjSSIS.sRutaDts & "\" & ObjSSIS.sSSISName, ObjSSIS.sServer, "usrSSIS", "ninot", EventsSSIS)

Public Class EventsSSIS

OnError

OnPreExecute

..

..

sql

Long duration Pre-Execute phase

Does anyone know why SSIS sometimes just sits in the Pre-Execute phase of a data flow and does nothing? It doesn't matter how elaborate the data flow is or 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 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...

Long duration Pre-Execute phase

Does anyone know why SSIS sometimes just sits in the Pre-Execute phase of a data flow and does nothing? It doesn't matter how elaborate the data flow is or 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 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...

sql

Long duration Pre-Execute phase

Does anyone know why SSIS sometimes just sits in the Pre-Execute phase of a data flow and does nothing? It doesn't matter how elaborate the data flow is or 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 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...

Long duration Pre-Execute phase

Does anyone know why SSIS sometimes just sits in the Pre-Execute phase of a data flow and does nothing? It doesn't matter how elaborate the data flow is or 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 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...

Long duration Pre-Execute phase

Does anyone know why SSIS sometimes just sits in the Pre-Execute phase of a data flow and does nothing? It doesn't matter how elaborate the data flow is or 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 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...

Long duration Pre-Execute phase

Does anyone know why SSIS sometimes just sits in the Pre-Execute phase of a data flow and does nothing? It doesn't matter how elaborate the data flow is or 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 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...

Long duration Pre-Execute phase

Does anyone know why SSIS sometimes just sits in the Pre-Execute phase of a data flow and does nothing? It doesn't matter how elaborate the data flow is or 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 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...

sql

Logs with timestamp

Hi,

I'm using the SSIS log feature. However, I want a timestamp on the file everytime the IS package runs so as to keep track also that this particular log file is for this specific execution of IS.

How do i do that?

thanks a lot

cherriesh

Are you logging to SQL Server? If so, you can add a column to the dbo.sysdtslog90 table with a datetime datatype and set its default to GetDate().

I recommend that you read Jamie Thomson's blog about custom logging: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx

|||

Here's a post from Jamie that shows how to set the log file name dynamically through an expression:

http://blogs.conchango.com/jamiethomson/archive/2006/10/05/SSIS-Nugget_3A00_-Dynamically-set-a-logfile-name.aspx