Error loading package

Hi there all...

We've just started using SSIS packages, and everything seems to be going ok.

I've recently set up a couple of jobs which just run an SSIS package every 5 minutes to collect data.
These run fine 90% of the time, but intermittantly produce an error.

When using a SSIS task in the job it simply reported
"Could not load Package."

I have changed these to use DTExec as I heard it gave more verbose execution results, and now I get an error like
"Executed as user: NZ\SQLAdmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:25:00 a.m. Could not load package "\MSDB\RadiusBilling\Get_RADIUS_AuthLog" because of error 0x80040E37. Description: Table/view either does not exist or contains errors. Source: Microsoft SQL Native Client Started: 8:25:00 a.m. Finished: 8:25:00 a.m. Elapsed: 0.047 seconds. Process Exit Code 5. The step failed."

So far I have not been able to find anyone else with similar errors.
Whenever the package executes it seems to run fine, so this seems to be an error just getting the package to execute in the first place, and as I said it only happens internittantly.

The packages talk to a SQL2005 server using windows authentication (which I assume is inherited from the SQLAgent service user), as well as a MySQL server through an ODBC connection.
The package is saved in the MSDB SSIS Package Store and uses the "Rely on Server for Security" option

Does anyone know why this is happening and how I can fix it?

disenfranchised wrote:

Hi there all...

We've just started using SSIS packages, and everything seems to be going ok.

I've recently set up a couple of jobs which just run an SSIS package every 5 minutes to collect data.
These run fine 90% of the time, but intermittantly produce an error.

When using a SSIS task in the job it simply reported
"Could not load Package."

I have changed these to use DTExec as I heard it gave more verbose execution results, and now I get an error like
"Executed as user: NZ\SQLAdmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:25:00 a.m. Could not load package "\MSDB\RadiusBilling\Get_RADIUS_AuthLog" because of error 0x80040E37. Description: Table/view either does not exist or contains errors. Source: Microsoft SQL Native Client Started: 8:25:00 a.m. Finished: 8:25:00 a.m. Elapsed: 0.047 seconds. Process Exit Code 5. The step failed."

So far I have not been able to find anyone else with similar errors.
Whenever the package executes it seems to run fine, so this seems to be an error just getting the package to execute in the first place, and as I said it only happens internittantly.

The packages talk to a SQL2005 server using windows authentication (which I assume is inherited from the SQLAgent service user), as well as a MySQL server through an ODBC connection.
The package is saved in the MSDB SSIS Package Store and uses the "Rely on Server for Security" option

Does anyone know why this is happening and how I can fix it?

So you say it fails intermittently? Well that's weird.

Its failing because it cannot validate one of its external data sources. The fact that its intermittent leads me to suggest that there is some outside influence that is causing this. network connectivity perhaps? I'm just speculating here.

-Jamie

|||

I have suspected that it might be something to do with the permissions on the domain account that the SQLAgent runs as.

Something like the account was getting locked perhaps.

However if that was the case I'd expect to see 5-6 failures as the job runs every 5 minutes, and the domain keeps accounts locked for 30 minutes. We only ever seem to get isolated failures, but about 6 times per day (which out of 288 executions isn't to bad)

|||

Hi

I have the same problem with my SSIS packages.

I'm still developing the package so it isn't finished yet. But on each step I put it into production to see, how it behaves.

In adtition to you, I noticed, that this error occurs more often, when the package is stored in MSDB store. Previously I have had packages stored in SQL's FileSystem store and error occurs maybe twice or three times per week. Now it occurs several times per day.

I use the domain user and id in Administators group on the server.

I execute the package on the server where the inserting database is.

I execute the package every minute.

BR

ZigaP

|||Again, it sounds like an intermittent connectivity issue.|||

I think the network connectivity is not an issue.

It cannot be, because I run packages on the server where the database is.

what about this: if I replace the name of the server with "(local)"... can this solve the problem?

|||My company develops SSIS components.
No matter what you think or what you are told, I can tell you that most parts of SSIS only run about 90% of the time when you execute packages from the 'Business Intelligence Development Studio' (VS2005 really)
We actually have a problem right now because we do not have any test cases which will run 100% of the time if you let them run for a day.
Most things seem to work if you keep testing to less than an hour, but anymore and you will eventually hit a failure.

Oddly, the problem only occurs at the package level. We have not seen a package or flow that dies in the middle, or in different places, or anything variable.
It either runs to success, dies upon startup (by any means, scheduled, manual, debug), or just plain does the wrong thing. Once a given package shows a particular problem, the problem is the same every time that package fails, and there is usually only one problem.

It is known to us and some big customers that you need to shut down dev studio and reload your project to get things to work again.

The size and/or complexity of the work does not seem to matter either.
We have simple flows with one src and one dest that moves 10000 rows, all the way to massive, multi-pronged flows with multiple sources, lookups, and millions of records.
They all eventually fail. Once you get the first failure, all others on that package will be of the same nature.

Its odd.
|||

FarmerChet wrote:

My company develops SSIS components.
No matter what you think or what you are told, I can tell you that most parts of SSIS only run about 90% of the time when you execute packages from the 'Business Intelligence Development Studio' (VS2005 really)
We actually have a problem right now because we do not have any test cases which will run 100% of the time if you let them run for a day.
Most things seem to work if you keep testing to less than an hour, but anymore and you will eventually hit a failure.

Oddly, the problem only occurs at the package level. We have not seen a package or flow that dies in the middle, or in different places, or anything variable.
It either runs to success, dies upon startup (by any means, scheduled, manual, debug), or just plain does the wrong thing. Once a given package shows a particular problem, the problem is the same every time that package fails, and there is usually only one problem.

It is known to us and some big customers that you need to shut down dev studio and reload your project to get things to work again.

The size and/or complexity of the work does not seem to matter either.
We have simple flows with one src and one dest that moves 10000 rows, all the way to massive, multi-pronged flows with multiple sources, lookups, and millions of records.
They all eventually fail. Once you get the first failure, all others on that package will be of the same nature.

Its odd.

I've not observed this in my packages. So are you here with a question, or just posting your experience? There are so many things that influence package execution, and until you are sure you have a perfect environment, I wouldn't take your post with too much credit.|||

Hm... After my last post error occured only once.

I didn't do anything from my last post. Also admins on server didn't do anything...

Is server has to get used to these packages to run well?

BR
ZigaP

Error loading package

Hi there all...

We've just started using SSIS packages, and everything seems to be going ok.

I've recently set up a couple of jobs which just run an SSIS package every 5 minutes to collect data.
These run fine 90% of the time, but intermittantly produce an error.

When using a SSIS task in the job it simply reported
"Could not load Package."

I have changed these to use DTExec as I heard it gave more verbose execution results, and now I get an error like
"Executed as user: NZ\SQLAdmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:25:00 a.m. Could not load package "\MSDB\RadiusBilling\Get_RADIUS_AuthLog" because of error 0x80040E37. Description: Table/view either does not exist or contains errors. Source: Microsoft SQL Native Client Started: 8:25:00 a.m. Finished: 8:25:00 a.m. Elapsed: 0.047 seconds. Process Exit Code 5. The step failed."

So far I have not been able to find anyone else with similar errors.
Whenever the package executes it seems to run fine, so this seems to be an error just getting the package to execute in the first place, and as I said it only happens internittantly.

The packages talk to a SQL2005 server using windows authentication (which I assume is inherited from the SQLAgent service user), as well as a MySQL server through an ODBC connection.
The package is saved in the MSDB SSIS Package Store and uses the "Rely on Server for Security" option

Does anyone know why this is happening and how I can fix it?

disenfranchised wrote:

Hi there all...

We've just started using SSIS packages, and everything seems to be going ok.

I've recently set up a couple of jobs which just run an SSIS package every 5 minutes to collect data.
These run fine 90% of the time, but intermittantly produce an error.

When using a SSIS task in the job it simply reported
"Could not load Package."

I have changed these to use DTExec as I heard it gave more verbose execution results, and now I get an error like
"Executed as user: NZ\SQLAdmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:25:00 a.m. Could not load package "\MSDB\RadiusBilling\Get_RADIUS_AuthLog" because of error 0x80040E37. Description: Table/view either does not exist or contains errors. Source: Microsoft SQL Native Client Started: 8:25:00 a.m. Finished: 8:25:00 a.m. Elapsed: 0.047 seconds. Process Exit Code 5. The step failed."

So far I have not been able to find anyone else with similar errors.
Whenever the package executes it seems to run fine, so this seems to be an error just getting the package to execute in the first place, and as I said it only happens internittantly.

The packages talk to a SQL2005 server using windows authentication (which I assume is inherited from the SQLAgent service user), as well as a MySQL server through an ODBC connection.
The package is saved in the MSDB SSIS Package Store and uses the "Rely on Server for Security" option

Does anyone know why this is happening and how I can fix it?

So you say it fails intermittently? Well that's weird.

Its failing because it cannot validate one of its external data sources. The fact that its intermittent leads me to suggest that there is some outside influence that is causing this. network connectivity perhaps? I'm just speculating here.

-Jamie

|||

I have suspected that it might be something to do with the permissions on the domain account that the SQLAgent runs as.

Something like the account was getting locked perhaps.

However if that was the case I'd expect to see 5-6 failures as the job runs every 5 minutes, and the domain keeps accounts locked for 30 minutes. We only ever seem to get isolated failures, but about 6 times per day (which out of 288 executions isn't to bad)

|||

Hi

I have the same problem with my SSIS packages.

I'm still developing the package so it isn't finished yet. But on each step I put it into production to see, how it behaves.

In adtition to you, I noticed, that this error occurs more often, when the package is stored in MSDB store. Previously I have had packages stored in SQL's FileSystem store and error occurs maybe twice or three times per week. Now it occurs several times per day.

I use the domain user and id in Administators group on the server.

I execute the package on the server where the inserting database is.

I execute the package every minute.

BR

ZigaP

|||Again, it sounds like an intermittent connectivity issue.|||

I think the network connectivity is not an issue.

It cannot be, because I run packages on the server where the database is.

what about this: if I replace the name of the server with "(local)"... can this solve the problem?

|||My company develops SSIS components.
No matter what you think or what you are told, I can tell you that most parts of SSIS only run about 90% of the time when you execute packages from the 'Business Intelligence Development Studio' (VS2005 really)
We actually have a problem right now because we do not have any test cases which will run 100% of the time if you let them run for a day.
Most things seem to work if you keep testing to less than an hour, but anymore and you will eventually hit a failure.

Oddly, the problem only occurs at the package level. We have not seen a package or flow that dies in the middle, or in different places, or anything variable.
It either runs to success, dies upon startup (by any means, scheduled, manual, debug), or just plain does the wrong thing. Once a given package shows a particular problem, the problem is the same every time that package fails, and there is usually only one problem.

It is known to us and some big customers that you need to shut down dev studio and reload your project to get things to work again.

The size and/or complexity of the work does not seem to matter either.
We have simple flows with one src and one dest that moves 10000 rows, all the way to massive, multi-pronged flows with multiple sources, lookups, and millions of records.
They all eventually fail. Once you get the first failure, all others on that package will be of the same nature.

Its odd.|||

FarmerChet wrote:

My company develops SSIS components.
No matter what you think or what you are told, I can tell you that most parts of SSIS only run about 90% of the time when you execute packages from the 'Business Intelligence Development Studio' (VS2005 really)
We actually have a problem right now because we do not have any test cases which will run 100% of the time if you let them run for a day.
Most things seem to work if you keep testing to less than an hour, but anymore and you will eventually hit a failure.

Oddly, the problem only occurs at the package level. We have not seen a package or flow that dies in the middle, or in different places, or anything variable.
It either runs to success, dies upon startup (by any means, scheduled, manual, debug), or just plain does the wrong thing. Once a given package shows a particular problem, the problem is the same every time that package fails, and there is usually only one problem.

It is known to us and some big customers that you need to shut down dev studio and reload your project to get things to work again.

The size and/or complexity of the work does not seem to matter either.
We have simple flows with one src and one dest that moves 10000 rows, all the way to massive, multi-pronged flows with multiple sources, lookups, and millions of records.
They all eventually fail. Once you get the first failure, all others on that package will be of the same nature.

Its odd.

I've not observed this in my packages. So are you here with a question, or just posting your experience? There are so many things that influence package execution, and until you are sure you have a perfect environment, I wouldn't take your post with too much credit.|||

Hm... After my last post error occured only once.

I didn't do anything from my last post. Also admins on server didn't do anything...

Is server has to get used to these packages to run well?

BR
ZigaP

Error loading package

Hi there all...

We've just started using SSIS packages, and everything seems to be going ok.

I've recently set up a couple of jobs which just run an SSIS package every 5 minutes to collect data.
These run fine 90% of the time, but intermittantly produce an error.

When using a SSIS task in the job it simply reported
"Could not load Package."

I have changed these to use DTExec as I heard it gave more verbose execution results, and now I get an error like
"Executed as user: NZ\SQLAdmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:25:00 a.m. Could not load package "\MSDB\RadiusBilling\Get_RADIUS_AuthLog" because of error 0x80040E37. Description: Table/view either does not exist or contains errors. Source: Microsoft SQL Native Client Started: 8:25:00 a.m. Finished: 8:25:00 a.m. Elapsed: 0.047 seconds. Process Exit Code 5. The step failed."

So far I have not been able to find anyone else with similar errors.
Whenever the package executes it seems to run fine, so this seems to be an error just getting the package to execute in the first place, and as I said it only happens internittantly.

The packages talk to a SQL2005 server using windows authentication (which I assume is inherited from the SQLAgent service user), as well as a MySQL server through an ODBC connection.
The package is saved in the MSDB SSIS Package Store and uses the "Rely on Server for Security" option

Does anyone know why this is happening and how I can fix it?

disenfranchised wrote:

Hi there all...

We've just started using SSIS packages, and everything seems to be going ok.

I've recently set up a couple of jobs which just run an SSIS package every 5 minutes to collect data.
These run fine 90% of the time, but intermittantly produce an error.

When using a SSIS task in the job it simply reported
"Could not load Package."

I have changed these to use DTExec as I heard it gave more verbose execution results, and now I get an error like
"Executed as user: NZ\SQLAdmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:25:00 a.m. Could not load package "\MSDB\RadiusBilling\Get_RADIUS_AuthLog" because of error 0x80040E37. Description: Table/view either does not exist or contains errors. Source: Microsoft SQL Native Client Started: 8:25:00 a.m. Finished: 8:25:00 a.m. Elapsed: 0.047 seconds. Process Exit Code 5. The step failed."

So far I have not been able to find anyone else with similar errors.
Whenever the package executes it seems to run fine, so this seems to be an error just getting the package to execute in the first place, and as I said it only happens internittantly.

The packages talk to a SQL2005 server using windows authentication (which I assume is inherited from the SQLAgent service user), as well as a MySQL server through an ODBC connection.
The package is saved in the MSDB SSIS Package Store and uses the "Rely on Server for Security" option

Does anyone know why this is happening and how I can fix it?

So you say it fails intermittently? Well that's weird.

Its failing because it cannot validate one of its external data sources. The fact that its intermittent leads me to suggest that there is some outside influence that is causing this. network connectivity perhaps? I'm just speculating here.

-Jamie

|||

I have suspected that it might be something to do with the permissions on the domain account that the SQLAgent runs as.

Something like the account was getting locked perhaps.

However if that was the case I'd expect to see 5-6 failures as the job runs every 5 minutes, and the domain keeps accounts locked for 30 minutes. We only ever seem to get isolated failures, but about 6 times per day (which out of 288 executions isn't to bad)

|||

Hi

I have the same problem with my SSIS packages.

I'm still developing the package so it isn't finished yet. But on each step I put it into production to see, how it behaves.

In adtition to you, I noticed, that this error occurs more often, when the package is stored in MSDB store. Previously I have had packages stored in SQL's FileSystem store and error occurs maybe twice or three times per week. Now it occurs several times per day.

I use the domain user and id in Administators group on the server.

I execute the package on the server where the inserting database is.

I execute the package every minute.

BR

ZigaP

|||Again, it sounds like an intermittent connectivity issue.|||

I think the network connectivity is not an issue.

It cannot be, because I run packages on the server where the database is.

what about this: if I replace the name of the server with "(local)"... can this solve the problem?

|||My company develops SSIS components.
No matter what you think or what you are told, I can tell you that most parts of SSIS only run about 90% of the time when you execute packages from the 'Business Intelligence Development Studio' (VS2005 really)
We actually have a problem right now because we do not have any test cases which will run 100% of the time if you let them run for a day.
Most things seem to work if you keep testing to less than an hour, but anymore and you will eventually hit a failure.

Oddly, the problem only occurs at the package level. We have not seen a package or flow that dies in the middle, or in different places, or anything variable.
It either runs to success, dies upon startup (by any means, scheduled, manual, debug), or just plain does the wrong thing. Once a given package shows a particular problem, the problem is the same every time that package fails, and there is usually only one problem.

It is known to us and some big customers that you need to shut down dev studio and reload your project to get things to work again.

The size and/or complexity of the work does not seem to matter either.
We have simple flows with one src and one dest that moves 10000 rows, all the way to massive, multi-pronged flows with multiple sources, lookups, and millions of records.
They all eventually fail. Once you get the first failure, all others on that package will be of the same nature.

Its odd.
|||

FarmerChet wrote:

My company develops SSIS components.
No matter what you think or what you are told, I can tell you that most parts of SSIS only run about 90% of the time when you execute packages from the 'Business Intelligence Development Studio' (VS2005 really)
We actually have a problem right now because we do not have any test cases which will run 100% of the time if you let them run for a day.
Most things seem to work if you keep testing to less than an hour, but anymore and you will eventually hit a failure.

Oddly, the problem only occurs at the package level. We have not seen a package or flow that dies in the middle, or in different places, or anything variable.
It either runs to success, dies upon startup (by any means, scheduled, manual, debug), or just plain does the wrong thing. Once a given package shows a particular problem, the problem is the same every time that package fails, and there is usually only one problem.

It is known to us and some big customers that you need to shut down dev studio and reload your project to get things to work again.

The size and/or complexity of the work does not seem to matter either.
We have simple flows with one src and one dest that moves 10000 rows, all the way to massive, multi-pronged flows with multiple sources, lookups, and millions of records.
They all eventually fail. Once you get the first failure, all others on that package will be of the same nature.

Its odd.

I've not observed this in my packages. So are you here with a question, or just posting your experience? There are so many things that influence package execution, and until you are sure you have a perfect environment, I wouldn't take your post with too much credit.|||

Hm... After my last post error occured only once.

I didn't do anything from my last post. Also admins on server didn't do anything...

Is server has to get used to these packages to run well?

BR
ZigaP

sql

Error in SSIS package after upgrade to SP2 release

I upgraded to SQL Server 2005 SP2 release today. I had been working with 9.00.1399. I had developed few packages in the older version which were getting invoked through the asp web application locally and it was working fine.

Today I upgraded to SQL server 2005 3042 version.
Now the same packages are not working when invoked through the web application. But it works fine stand alone. All errors refer to connection problem.

I need to resolve this urgently. Can anyone help me please?

Errors -

"SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.\r\nAn OLE DB record is available. Source: \"Microsoft JET Database Engine\" Hresult: 0x80004005 Description: \"The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.\".\r\n"

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

I have seen that error from time to time. it indicates that the excel file where the connection manager is pointing to is open by other application/process. I read a Phil's suggestions to use a third party to monitor and detect who is actually holding the file, but I don't remember the name.

In the mean time, try closing all applications that may be 'holding the file' and run the package again.

|||Hi,

The error appears due to the upgrade to the SP2 version.

I restarted my system. No other process accessing the excel.
But still the same error.
This functionality used to work fine with the older version of SQL.

Error in Programming DTS using VB.net

Hi ,

i am using VS.NET 2003 for programming and SQL sever 2005 is my database.

while creating DTS packages,

i am using code as follows

Public Sub Task_Sub4(ByVal goPackage As Object)

Dim oTask As DTS.Task

Dim oLookup As DTS.Lookup

Dim oCustomTask4 As DTS.DataPumpTask2

oTask = CType(goPackage, DTS.Package).Tasks.New("DTSExecuteSQLTask")

oCustomTask4 = CType(oTask.CustomTask, DTS.DataPumpTask2)

oCustomTask4.Name = "Copy Data from contact_info to [content_shriya].[dbo].contact_info] Task"

oCustomTask4.Description = "Copy Data from contact_info to [content_shriya].[dbo].[contact_info] Task"

oCustomTask4.SourceConnectionID = 3

oCustomTask4.SourceSQLStatement = "select * from [Content_management].[dbo].[contact_info]"

oCustomTask4.DestinationConnectionID = 4

oCustomTask4.DestinationObjectName = "[content_shriya].[dbo].[contact_info]"

oCustomTask4.ProgressRowCount = 1000

oCustomTask4.MaximumErrorCount = 0

oCustomTask4.FetchBufferSize = 1

oCustomTask4.UseFastLoad = True

oCustomTask4.InsertCommitSize = 0

oCustomTask4.ExceptionFileColumnDelimiter = "|"

oCustomTask4.ExceptionFileRowDelimiter = vbCrLf

oCustomTask4.AllowIdentityInserts = False

oCustomTask4.FirstRow = 0

oCustomTask4.LastRow = 0

oCustomTask4.FastLoadOptions = 2

oCustomTask4.ExceptionFileOptions = 1

oCustomTask4.DataPumpOptions = 0

'Call oCustomTask4_Trans_Sub1(oCustomTask4)

goPackage.Tasks.Add(oTask)

oCustomTask4 = Nothing

oTask = Nothing

End Sub

But i am geeting error in line

oCustomTask4 = CType(oTask.CustomTask, DTS.DataPumpTask2)

giving error 'System.InvalidCastException'

Additional information: Specified cast is not valid.

i changed it with oCustomTask4 = oTask.CustomTask ,

then also similar error appear.

Please solve my problem, i need to create DTS Packages by using VS.NET 2003 and Database is SQL server 2005.

Thank you

You might have better success posting this in the DTS forum.

I'm not an expert in DTS programming, but since you created oTask as an ExecuteSQLTask, I don't think you can cast it to a DataPumpTask. You need to create it as a DataPumpTask.

|||

Thank you Jhon the code works.

|||Mark the response that was helpful as an answer, please. It helps with searching in the forum.

Error importing SSIS packages on SQL Management Studio

I encounter this error when importing SSIS packages using SQL Server Management Studio:

===================================

Unable to cast object of type 'Microsoft.SqlServer.Dts.ObjectExplorerUI.ImportPackageAsAction' to type 'Microsoft.SqlServer.Dts.ObjectExplorerUI.ISimpleAction'. (Microsoft.SqlServer.DtsObjectExplorerUI)

Program Location:

at Microsoft.SqlServer.Dts.ObjectExplorerUI.DtsServerMenuItem.Invoke(INodeInformation parent, String mode, String assembly, String type)

The Integration Services that I'm using has just been rebuilt due to problems after installing SP2. I'm using Integration Services 9.0.3042. I can't find anything having this same problem. Any help will be appreciated.

Thanks!

Hi ya,

Try these posts. If that doesn't help then best bet is to report it to MS.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=335181&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=406974&SiteID=1

Cheers

Rizwan

Error Handling with Execute Package Task

I have a main package that calls several other packages using Execute Package Tasks. I also have OnPreExecute, OnPostExecute, and OnError event handlers at the package level to audit the beginning and completion of each package. I want to prevent each task from bubbling up to the package event handlers as well as prevent each task from bubbling up to the main package event handlers. I've tried setting the Propagate variable for each of the event handlers to False and setting the DisableEventHandlers property of each task to True but neither solution seems to work. Is there a way to do this that I'm missing?

You have to set [System::Propogate]=false in the eenthandler of the task for which you don't want events to propogate.

Your post doesn't specify on which eventhandlers you are setting this.

-Jamie

|||

I found another thread that describes the problem I'm seeing. Here is the previous thread and, in following the thread, I'm not sure if the conclusion was that this behavior was as designed or that this is really a bug. Here is a link to the previous discussion:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=57523&SiteID=1

The problem in a nutshell is the following: There are two packages, a parent package which calls a child package using the Execute Package Task. Both packages have OnPostExecute event handlers defined at the package level to log progress with different ExecutionInstanceGUID. Even when the System::Propagate property is set to False for the child package OnPropagate event handler, the parent OnPostExecute event handler is still raised.

I guess the work around is to create an empty event handler for the Execute Package Task in the parent package and set the System::Propagate property for that empty event handler to False to prevent the main package OnPostExecute event from being raised. Since I have several Execute Package Tasks in the real solution, a better approach would be to wrap all the Execute Package Tasks in a Sequence container and create the do nothing event handler with the Propagate property set to false at the Sequence container rather than at each of the Execute Package tasks event handlers.

Does this make sense? Or is there possibly a better solution that I'm missing? Have to confess, I'm getting a bit overwhelmed by the number of possible appoaches one can use to log and audit package execution.

|||

Martin,

I cannot replicate your problem. If I set System::Propogate=FALSE on an eventhandler scoped to the child package then any events raised by the package container of the child package do not propogate to the parent package.

Note that this does NOT stop events raised by containers in the child package from propogating to the parent package. If you want to stop this from happening then set System::Propogate=FALSE on all of those containers as well.

I've also re-read your first post where you say "I want to prevent each task from bubbling up to the package event handlers ". If you only want to capture events fired by the package then you can compare System::PackageID to System::SourceID. If they are different then the event has not been raised by the package.

-Jamie

|||

Jamie,

I guess I was looking at the architecture wrong. I thought the child package was the container for all other containers in the child package and therefore, by setting Propagate to false for all child package event handlers, no events would be raised by any containers in the child package to the parent package. That doesn't seem to be the case. By creating empty event handlers for the Execute Package Task in the parent package and setting Propagate to false for the empty event handlers, events raised by child package containers are not propagated to the parent package event handlers.

We are logging to two tables. Package events in one table and tasks in another table and have created a package template to handle the plumbing. This is great stuff.

|||

Martin Mason wrote:

Jamie,

I guess I was looking at the architecture wrong. I thought the child package was the container for all other containers in the child package and therefore, by setting Propagate to false for all child package event handlers, no events would be raised by any containers in the child package to the parent package. That doesn't seem to be the case.

Yeah, System::Propogate can often be misunderstood - mainly due to the complexity of the container hierarchy. System::Propogate only stops events fired by the container that it is scoped to from propogating. It does not stop events fired by descendant containers from propogating.

Martin Mason wrote:

By creating empty event handlers for the Execute Package Task in the parent package and setting Propagate to false for the empty event handlers, events raised by child package containers are not propagated to the parent package event handlers.

I'm surprised that that is the case though. The package container and the TaskHost container on the Execute Package Task are no different from any other container and should behave the same way.

Martin Mason wrote:

We are logging to two tables. Package events in one table and tasks in another table and have created a package template to handle the plumbing. This is great stuff.

I agree. I'm a big fan of using eventhandlers for custom logging and have advocated it strongly here: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx

-Jamie

|||

Is there an easy way to identify which "System::Propogate" variable goes with what container object? The variable names and scope are all the same? How can you tell which variable goes with which container event. When I define an OnPreExecute event for multiple containers/tasks I can't tell which "System::Propogate" variable goes with what object?

Maybe I am missing something obvious but I do not see the connection?

Thanks!

|||

Each eventhandler has a [System::Propogate] variable scoped to it. Variables called [System::Propogate] are ONLY scoped to eventhandlers, nothing else. Therefore, when you use one there can be no ambiguity about which one you are using, it is the one scoped to the eventhandler you are currently editing.

And remember that an eventhandler is, in turn, scoped to a container in the control-flow.

Does that answer the question?

-Jamie

|||

Um Kind of.

I guess I should have noted that I was referring to the variables list, located under SSIS on the toolbar? Not just when I am editing the event. I understand that when I am editing the event ssis will only show me variables that are in scope to the event/container/task but I was referring to the "ALL" Variable list, where you can view all System and user variables and set their values etc. or add new variables or to view there properties in the properties pane?

Do I make sense?

Thanks!

Error Handling with Execute Package Task

I have a main package that calls several other packages using Execute Package Tasks. I also have OnPreExecute, OnPostExecute, and OnError event handlers at the package level to audit the beginning and completion of each package. I want to prevent each task from bubbling up to the package event handlers as well as prevent each task from bubbling up to the main package event handlers. I've tried setting the Propagate variable for each of the event handlers to False and setting the DisableEventHandlers property of each task to True but neither solution seems to work. Is there a way to do this that I'm missing?

You have to set [System::Propogate]=false in the eenthandler of the task for which you don't want events to propogate.

Your post doesn't specify on which eventhandlers you are setting this.

-Jamie

|||

I found another thread that describes the problem I'm seeing. Here is the previous thread and, in following the thread, I'm not sure if the conclusion was that this behavior was as designed or that this is really a bug. Here is a link to the previous discussion:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=57523&SiteID=1

The problem in a nutshell is the following: There are two packages, a parent package which calls a child package using the Execute Package Task. Both packages have OnPostExecute event handlers defined at the package level to log progress with different ExecutionInstanceGUID. Even when the System::Propagate property is set to False for the child package OnPropagate event handler, the parent OnPostExecute event handler is still raised.

I guess the work around is to create an empty event handler for the Execute Package Task in the parent package and set the System::Propagate property for that empty event handler to False to prevent the main package OnPostExecute event from being raised. Since I have several Execute Package Tasks in the real solution, a better approach would be to wrap all the Execute Package Tasks in a Sequence container and create the do nothing event handler with the Propagate property set to false at the Sequence container rather than at each of the Execute Package tasks event handlers.

Does this make sense? Or is there possibly a better solution that I'm missing? Have to confess, I'm getting a bit overwhelmed by the number of possible appoaches one can use to log and audit package execution.

|||

Martin,

I cannot replicate your problem. If I set System::Propogate=FALSE on an eventhandler scoped to the child package then any events raised by the package container of the child package do not propogate to the parent package.

Note that this does NOT stop events raised by containers in the child package from propogating to the parent package. If you want to stop this from happening then set System::Propogate=FALSE on all of those containers as well.

I've also re-read your first post where you say "I want to prevent each task from bubbling up to the package event handlers ". If you only want to capture events fired by the package then you can compare System::PackageID to System::SourceID. If they are different then the event has not been raised by the package.

-Jamie

|||

Jamie,

I guess I was looking at the architecture wrong. I thought the child package was the container for all other containers in the child package and therefore, by setting Propagate to false for all child package event handlers, no events would be raised by any containers in the child package to the parent package. That doesn't seem to be the case. By creating empty event handlers for the Execute Package Task in the parent package and setting Propagate to false for the empty event handlers, events raised by child package containers are not propagated to the parent package event handlers.

We are logging to two tables. Package events in one table and tasks in another table and have created a package template to handle the plumbing. This is great stuff.

|||

Martin Mason wrote:

Jamie,

I guess I was looking at the architecture wrong. I thought the child package was the container for all other containers in the child package and therefore, by setting Propagate to false for all child package event handlers, no events would be raised by any containers in the child package to the parent package. That doesn't seem to be the case.

Yeah, System::Propogate can often be misunderstood - mainly due to the complexity of the container hierarchy. System::Propogate only stops events fired by the container that it is scoped to from propogating. It does not stop events fired by descendant containers from propogating.

Martin Mason wrote:

By creating empty event handlers for the Execute Package Task in the parent package and setting Propagate to false for the empty event handlers, events raised by child package containers are not propagated to the parent package event handlers.

I'm surprised that that is the case though. The package container and the TaskHost container on the Execute Package Task are no different from any other container and should behave the same way.

Martin Mason wrote:

We are logging to two tables. Package events in one table and tasks in another table and have created a package template to handle the plumbing. This is great stuff.

I agree. I'm a big fan of using eventhandlers for custom logging and have advocated it strongly here: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx

-Jamie

|||

Is there an easy way to identify which "System::Propogate" variable goes with what container object? The variable names and scope are all the same? How can you tell which variable goes with which container event. When I define an OnPreExecute event for multiple containers/tasks I can't tell which "System::Propogate" variable goes with what object?

Maybe I am missing something obvious but I do not see the connection?

Thanks!

|||

Each eventhandler has a [System::Propogate] variable scoped to it. Variables called [System::Propogate] are ONLY scoped to eventhandlers, nothing else. Therefore, when you use one there can be no ambiguity about which one you are using, it is the one scoped to the eventhandler you are currently editing.

And remember that an eventhandler is, in turn, scoped to a container in the control-flow.

Does that answer the question?

-Jamie

|||

Um Kind of.

I guess I should have noted that I was referring to the variables list, located under SSIS on the toolbar? Not just when I am editing the event. I understand that when I am editing the event ssis will only show me variables that are in scope to the event/container/task but I was referring to the "ALL" Variable list, where you can view all System and user variables and set their values etc. or add new variables or to view there properties in the properties pane?

Do I make sense?

Thanks!