Packages error or hang in SQL Agent but not in BI IDE

Hi ,

I have an issues that worries me alot. I have SSIS packages scheduled in SQL Agent and they sometime error out or hang in Agent but never when i run them in BI IDE (Visual studio). There are two particular packages that always hang, alway in Agent but when i run them in BI IDE, they run just fine. Im was running them under in Agent under type= Sql server intergration services and also tried running under type = Operating System (CmdExec). Both types have the same problem. The packages hang. The log files dont say much, because the packages hang, no logging is doing so i dont get any infomation to determin what is going on. Can someone please help me ou.

Can you provide more information about the package:

What security settings are you using?

What tasks are in the package?

Where is the package hanging?

What log events do you have enabled?

Is it consistent? Happens everytime?

Does it happen if you run it under DTExec in the same credentials as under Agent?

Kirk Haselden
Author "SQL Server Integration Services"

|||The security settings of the package is EncryptSensitiveWithUserKey but no password
The task contained the the package are two data flow componenet ( in which there is a download from a remote source to local sql sever destination server ) , two execute sql task from which a sql statement is set to a varaible and assigned to the sql property of the datasources (using expression) in the data flow componenets and lastly , email task to send emails if the packaged failed or passed.
I have all the log even enable except for those in relation to the pipelines like OnPipelinePostEndOfRowset

It hangs everytime the packages runs in Sql Agent but not in VS (BI IDE)
I have only run it with dtexec under Agent and its still hangs.|||

This won't work because EncryptSensitiveWithUserKey uses the credentials of the user on the machine where the package is built.

Change the package protection level to "Rely on server storage for encryption".

Save the package to SQL Server and then schedule the package to run in Agent.

Should fix the issue.

Kirk Haselden
Author "SQL Server Integration Services"

|||is there any way i can do that without storing the packages in the db because i make changes to the pacakages very often and like it on the file system. If not, i can stomach storing it in the db.|||

Have you actually tried doing this on SQL Server 2005 SP1? BIDS will not let you save a package with 'Server Storage' ProtectionLevel. Therefore, I can't build a deployment manifest where I get the 'Rely on server storage for encryption' option. Just what do you mean by 'save the package to SQL Server'? I'm assuming that's using a deployment manifest file which opens the deployment wizard, is there a better way?

|||

DawnJ wrote:

Just what do you mean by 'save the package to SQL Server'? I'm assuming that's using a deployment manifest file which opens the deployment wizard, is there a better way?

this link describes how to save a package to sql server: http://msdn2.microsoft.com/en-us/library/ms137565.aspx