Packages not executing right on production server?

Hi,

I have developed about 20 to 30 packages that are executed on a specific order by a parent package that iterates a foreach cycle...

Has in development server all packages run just fine... when deployed to the production server and executed the first child package to run gives error and says that the logging text file was unable to find the file? It was supposed for the log to create and send information to the file right? So i think this is a little bit weird...

Any idea of what is going on here?

Regards,

Does the lggoing file folder exists, and does the execution security context (account) used have permissions to write to that folder? Different machines, so maybe the drives and folders are different, and your may well be using a different security context.|||

Where should i see the security context?

Anyway i have done the directory full previledges to "everyone" and nothing... one of the tasks creates the log just fine... but the other don't... the directory exists and i can't find a reason for it not create the file......

|||

The security context is the user who executes the package. Packages still execute on the machine which hosts the calling process, so were you are running DTEXEC or Visual studio, and they run under the security context of the user running the host program.

For scheduled jobs this is the SQL Server Agent service account or proxy account depending on your setup.

|||

I have already checked and all machines are setup the same...

I'm using visual studio 2005 in the production machine now with the project open and i can't really figure out what this error comes from... It seems that the variables are sent to the child package after the ssis logging so when ssis logging wants to start beeing used it can't...

Imagine i pass a variable called SOLUTION_DIRECTORY and that variable setups many things the log file path is one of them... os if this variable comes after the start of logging then we have problems....

Does this happens? Regards

|||

It depends how you're setting it. If its via a configuration then it should occur before you start logging.

If you pass it via the command-line, I'm not so sure.

Try outputting the the connection string of the log file to a different log provider, perhaps the event viewer. Output the connection string using this technique: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

-Jamie

|||

It really seems that the logging is starting first then the package configuration that receives the variables from the parent package... this is really stupid... damn!

I tried to setup the variable with a default value correct and it worked... so how will i overcome this problem? I really need to log onto files and the file destination is dynamic... :( !!!

|||

I do a very similar thing. I use an XML config to set a variable to contain a path to my rootfolder and then dynamically build the logfile connection string from that. Exactly as you are doing.

This works fine. I'd be very very surprised if parent configurations occur at a different time.

-Jamie

|||

Has for what it seems it really is this way...

How can you explain the fact that if i set the variable in the child package to the right path it works and if not and pass the right path by the parent package it doens't? At least it can be very weird indead...

Another thing... is there anyway to validate if a directory already exists before creating it without using a script?

OUTPUT:

Error: 0xC001404B at CCCTT000D, Log provider "SSIS log provider for Text files": The SSIS logging provider has failed to open the log. Error code: 0x80070003.

The system cannot find the path specified.

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::SOLUTION_DIR".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_SERVER".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_USERID".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_PASSWORD".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_DATABASE".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "IN::MODULO".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "OUT::COD_EMPRESA".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "TEMP::PERIODO_CURRENTE".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "TEMP::STEP".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::SOLUTION_DIR".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_SERVER".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_USERID".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_PASSWORD".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_DATABASE".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "IN::MODULO".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "OUT::COD_EMPRESA".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "TEMP::PERIODO_CURRENTE".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "TEMP::STEP".

Information: 0x0 at Script Task: ligacao: CCCTT000D_<EMPRESA> UNL connectionstring: D:\CMSINTRABI\CG\DADOS\200401\CCCTT000D_CMS.UNL

Information: 0x0 at Script Task: ligacao: CONNECTION connectionstring: Data Source=CANARIO;Initial Catalog=CMSINTRACG;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;

Information: 0x0 at Script Task: ligacao: FICHEIRO_LOG connectionstring: D:\CMSINTRABI\CG\LOGS\200401\CCCTT000D_CMS_CT.LOG

Has you can see the log gives error first then the variables are caught... :(

Regards,

|||

Yes its confirmed... it really seems that the logging starts first then the package configuration...

My text file for the ssis logging has this path configured in expressions:

@.[IN::SOLUTION_DIR] + "\\"+ @.[IN::MODULO]+"\\LOGS\\" + (DT_WSTR,6)@.[IN::PERIODO_CURRENTE] + "\\" + @.[System::PackageName] + "_" + @.[IN::COD_EMPRESA] +"_"+ @.[IN::STEP] +".LOG"

Well, all these variables come straight from the parent package.... if i don't set them with a default value the package will blow... but it i set them with an existing path it works just fine...

So this is it... Now WHY the hell don't the package configuration run in first place?

How will i make the logging this way? I really need help here! :(

Regards,

|||Can you not use a single configuration file and set it on all packages. It can supply the values you require to each and every package, rather than using the parent package variables. Configurations do happen before validation and execution. What is the problem with configurations you mention?|||

Well imagine that the parent package runs childs packages and send paremeters to each package...

Imagine for instance i run all packages by year month and i want the child packages to log the file to a directory somewhere in the corresponding year month... Ex: C:\logs\200604

but if that directory doenst exists at first then the error occurs... if the directory already exists then it works just fine :)

Basically i must be shure that the first directory setup must exist...

sql

Package.Execute/IDTSEvents vs DTExec

I am executing a package via vb.net with package.execute using the IDTSEvents interface inheriting DefaultEvents. I am trying to emulate DTExec functionality. The package runs fine and I am catching events, but I don't get nearly as many info and progress events as are put out by DTExec. From my understanding DTExec also used the managedDTS application object, is that not the case? If it uses this does anyone have an ideas on how it is grabbing more events? For example I don't get these events that DTExec shows ..

Info: 2006-09-26 14:22:27.97
Code: 0x40016041
Source: DWB02130
Description: The package is attempting to configure from the XML file "D:\SSIS\ConfigurationsDatabase.dtsConfig".
End Info
Info: 2006-09-26 14:22:28.05
Code: 0x40016040
Source: DWB02130
Description: The package is attempting to configure from SQL Server using the configuration string ""Configurations Database";"[dbo].[SSISConfigurations]";"DWB02130";".
End Info

I also don't get as many progress updates during validation, I basically get 0 and 100% while DTExec shows several 1%, 3%, 10%, etc.

Any help is greatly appriciated.

Thanks!
Harry

Do you set fireAgain parameter to true for events like OnInformation, OnProgress? If not, the event might not be fired again to avoid overhead associated with sending an event (that presumably nobody listens too).

You can also implement IDTSLogging interface and pass it to Execute method in addition to IDTSEvents interface.sql

Package.Execute/IDTSEvents vs DTExec

I am executing a package via vb.net with package.execute using the IDTSEvents interface inheriting DefaultEvents. I am trying to emulate DTExec functionality. The package runs fine and I am catching events, but I don't get nearly as many info and progress events as are put out by DTExec. From my understanding DTExec also used the managedDTS application object, is that not the case? If it uses this does anyone have an ideas on how it is grabbing more events? For example I don't get these events that DTExec shows ..

Info: 2006-09-26 14:22:27.97
Code: 0x40016041
Source: DWB02130
Description: The package is attempting to configure from the XML file "D:\SSIS\ConfigurationsDatabase.dtsConfig".
End Info
Info: 2006-09-26 14:22:28.05
Code: 0x40016040
Source: DWB02130
Description: The package is attempting to configure from SQL Server using the configuration string ""Configurations Database";"[dbo].[SSISConfigurations]";"DWB02130";".
End Info

I also don't get as many progress updates during validation, I basically get 0 and 100% while DTExec shows several 1%, 3%, 10%, etc.

Any help is greatly appriciated.

Thanks!
Harry

Do you set fireAgain parameter to true for events like OnInformation, OnProgress? If not, the event might not be fired again to avoid overhead associated with sending an event (that presumably nobody listens too).

You can also implement IDTSLogging interface and pass it to Execute method in addition to IDTSEvents interface.

Package Failure (The session was canceled)

Anyone seen this error or know what this could be? I got this when executing a package. It works on my dev machine, however failed with this error on another test machine/environment.

The session was canceled. (Exception from HRESULT: 0x800700F0) (Microsoft Visual Studio)

Program Location:
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
at Microsoft.DataWarehouse.VsIntegration.Interop.NativeMethods.ThrowOnFailure(Int32 hr, Int32[] expectedHRFailure)
at Microsoft.DataWarehouse.VsIn

any idea anyone?

Package executing error

I wrote package using Integration Services and tried execute it on Visual Studio 2003 (Visual Basic). Package was loaded, but execute method has failed without any error messages. When I tried to run it on Visual Studio 2005, execution was successful.

Codes:

Dim App As DTSLib.IDTSApplication90 = New DTSLib.Application

Dim p As DTSLib.IDTSPackage90 = App.LoadPackage("d:\temp\Package.dtsx", True, Nothing)

p.Execute()

What can be the reason of the trouble?

Thanks,

Alexander

Only one .NET runtime can live in one process. Visual Studio 2003 uses .NET 1.1, and SSIS requires .NET 2.0 - so SSIS fails if the process has already loaded .NET 1.1.

You can use config file to change the .NET version used by applications developed with .NET 1.1 and force them to use .NET 2.0, but it will break Visual Studio debugging.

So it is highly recommended to use Visual Studio 2005. In Visual Studio 2005 you can also use managed API for SSIS - in Microsoft.SqlServer.ManagedDTS assembly, which is more convinient than using interop from DTSLib.

Package could not be loaded - SQL Server Agent

I'm having an issue with executing an ssis package using SQL Server Agent. I have 7 packages that I've got 7 separate jobs for. I also have 7 separate stored procedures that each call the appropriate job using sp_start_job. We are using BizTalk to execute these 7 stored procedures, which in turn start a job, which in turn will call an SSIS Package to run. When we execute the 7 jobs spaced apart, i.e. 2 or 3 seconds apart, they all run fine. When we BizTalk executes them all at the same time, within milliseconds of each other, sometimes they all run fine, sometimes a few of the SSIS Packages end up with the error "The Package could not be loaded" in the SQL Server Agent history log.
Is there an issue when trying to execute multiple Packages all at the same time? It's not like I'm calling the same package 2 times, they are all different packages. Can anyone shed any light on this?
Thanks,
AndyI tried running 7 jobs each running differerent package, and everytime 1 failed with message package execution failed. With a second delay they passed always. I have opened a tracking bug for this.

There is workaround for this.

Set retry_attempts(also retry_interval using sp_update_jobstep) to any value other than default which is 0 .

Thanks for reporting this.

Thanks,
Gops Dwarak|||Thanks Gops. I was trying to avoid using the re-try attempts, but I guess for now I'll have to. Hopefully this will be fixed in SP1!

-Andy

Package could not be loaded

Hello,

I'm having an issue with executing a package using SQL Server Agent. I have 7 packages that I've got 7 separate jobs for. I also have 7 separate stored procedures that each call the appropriate job using sp_start_job. We are using BizTalk to execute these 7 stored procedures, which in turn start a job, which in turn will call an SSIS Package to run. When we execute the 7 jobs spaced apart, i.e. 2 or 3 seconds apart, they all run fine. When we BizTalk executes them all at the same time, within milliseconds of each other, sometimes they all run fine, sometimes a few of the SSIS Packages end up with the error "The Package could not be loaded" in the SQL Server Agent history log.
Is there an issue when trying to execute multiple Packages all at the same time? It's not like I'm calling the same package 2 times, they are all different packages. Can anyone shed any light on this?
Thanks,
Andy

There is no SSIS issue with doing this (as far as I am aware) so you might want to post this in the agent forum as well.

Thanks,
Matt