Packages run faster on BIDS and slower on SQL Server

Hi,
I am surprised to see that in Business Inteligence Studio debugging mode, my packages take shorter. And when I run it through Agent on the SQL Server where data actually resides, it take around 70% longer time.

The package is now very closed to data and database engine itself, in BIDS it wasnt.

Anybody knows why this happen ? Do I need to tune up something ?

Any input in this will sincerely be appreciated.When you're using SQL Agent, are you using the SSIS subsystem or the command subsystem (DTExec)? Do you have the SSIS service started ?

Packages run faster on BIDS and slower on SQL Server

Hi,
I am surprised to see that in Business Inteligence Studio debugging mode, my packages take shorter. And when I run it through Agent on the SQL Server where data actually resides, it take around 70% longer time.

The package is now very closed to data and database engine itself, in BIDS it wasnt.

Anybody knows why this happen ? Do I need to tune up something ?

Any input in this will sincerely be appreciated.When you're using SQL Agent, are you using the SSIS subsystem or the command subsystem (DTExec)? Do you have the SSIS service started ?

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

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

Package variables "disappear" from job properties

Creating a SQL Agent job to run a package works just fine. On the Step Properties, Set values tab, I can add variables and the values I want for them, no problem. The job runs just fine. If I want to go edit the values however, they're always gone.

Is there some security setting that hides the variables and their values which is on by default?

GregsListAcct wrote:

Creating a SQL Agent job to run a package works just fine. On the Step Properties, Set values tab, I can add variables and the values I want for them, no problem. The job runs just fine. If I want to go edit the values however, they're always gone.

Is there some security setting that hides the variables and their values which is on by default?

Greg,

I don't know the answer to this but regardless, you should stop using the SSIS sub-system and just call teh package usinng the command-line sub-system instead.

There is an article at wiki.sqlis.com that explains the rationale behind this but the site is down so i can't link to it. Basically you don't get any diagnostic info out of the SSIS sub-system.

-Jamie

Package fails when run under SQL Server Agent

Hi,

I have a SSIS package that I developed using Business Intelligence Development Studio. I imported it into our SQL Server database and it runs fine (when I connect to Integration Services within SQL Server Management Studio, and run the package). But when I created a SQL Server Agent job comprising 1 step, to run this package, it fails. The failing step attempts to read data from a MS Access 2000 DB into SQL Server 2005.

I added logging to the package, and got the following error logged to the Event Viewer:
"The AcquireConnection method call to the connection manager "CurrentYearPCStats" failed with error code 0xC0202009."

I then created a new Operating System (CmdExec) step, calling DTEXEC with the command line copied from that generated by the SSIS step. The command line is:
"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTEXEC.EXE" /SQL "\AFAReduction\AFA Reduction Integration" /SERVER <ourservername> /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

This failed with the same error in the Event Viewer, but for some reason also produced a meaningful error in the SQL Server Agent job's View History:
Description: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "The Microsoft Jet database engine cannot open the file '\\<anotherservername>\PCStatslive\CurrData.mdb'. It is already opened exclusively by another user, or you need permission to view its data."

Please note that the SQL Server Agent runs using user id sqlservice, by default, and I have amended this user's Windows account to make it a Domain Admin.

Please let me know why I might get this error, when the package runs OK on its own.

Many thanks,
Keith.

The jobs running with the sql agent usually run with the agent's service account identity (local system or Network service).

This user must have permissions to open your Access DB files (NTFS security etc included).

When you run it from SQL mgmt studio it is running using your own credentials...

Hope this helps.

|||In addition to Zoran's advice, take a look at this KB article which describes common problems with running in Agent, and how to troubleshoot them:
http://support.microsoft.com/kb/918760|||

i have a similar kind of situation As a inidividual package or executing through the command line the package runs fine but when i try to execute it through the Agent it fails.

Executed as user: SEMASTER\Administrator. The package execution failed. The step failed.

Any help

Thanks,

Jasmine

|||

Hi again,

Thanks for your replies. You could've knocked me down with a feather - I came in this morning and the SQL Agent job that runs at 6 AM had succeeded! Tried manually and its all working fine! I've changed nothing, I have the Job History to prove that the last test I did yesterday failed, and everything since 6 AM today has succeeded.

The only thing I can guess is that there was some problem with the Access database itself, which cleared overnight. I'll keep an eye on this.

Thanks again,

Keith.

Package fails when I use ODBC connection (Fails on SQL Server Agent, OK in Visual Studio)

I did a small package with only one ODBC connection (Merant 3.70 32-Bit Progess). This package runs well in Visual Studio and fails when runs by SQL Server Agent.

Configuration:

SQL Server Agent on a 32Bit server.

The ODBC connection configuration in available on System DSN on this server.

The user of Server Agent have full access (Admin).

Connect Manager Provider: ".Net Providers\Odbc Data Provider"

SQL Server version: 9.0.3042

Error Message:

Executed as user: TEKCON\tcadmin. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 16:50:33 Error: 2007-06-11 16:50:33.62 Code: 0xC0047062 Source: Data Flow Task DataReader Source [1] Description: System.Data.Odbc.OdbcException: ERROR [HYC00] [MERANT][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS ERROR [IM006] [MERANT][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed. ERROR [HYC00] [MERANT][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS ERROR [IM006] [MERANT][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed. at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcCon... The package execution fa... The step failed.

I created a .bat file with this instruction and It's run well:

dtexec /f "C:\Program Files\Microsoft SQL Server\90\DTS\Packages\Integration Services Project\testcom.dtsx"
pause

Why it's not running with SQL Server Agent?

Hi Daniel,

I've not seen this with ODBC sources before but I don't use a lot of ODBC sources. Based on your description of the problem I suspect the privileges on the SQL Agent account. If possible, log into an SSIS development workstation with the SQL Agent account credentials and try the package in Visual Studio. If it executes, then my suspicion is incorrect.

Hope This Helps,
Andy

|||

Thanks Andy,

I tried your suggestion: I logged into the SSIS development workstation with the same user than the SQL Server Agent. It's running with Visual Studio but fail in the SQL Server Agent.

Bye!

Daniel|||What type of step are you using in SQl Server gant job? Did you try using CmdExec type with the same command line you indicated in your previous post?|||

Hi Daniel,

You may want to try the following test: Change the package ProtectionLevel property to something containing the word "Password" (EncryptAllWithPassword, EncryptSensitiveWithPassword) and supply a nice strong password in the PackagePassword property. Save the changes.

Execute the package using the DTExecUI utility. You will have to supply the password to do so. If this succeeds, schedule the package execution using a SQL Server Integration Services Package job step type. Again, you wll need to supply the package password.

If this does not work, please copy the error and paste it in your response.

Hope this helps,
Andy

Package Execution with SQL Server Agent or DTEXEC

Hello,

I want execute package with SQL Server Agent. I create a job and I try all the solutions : my job doesn't want to work.

I success to launch the package but the execution failed.

I try the same job launching from the server : it doesn't work either.

So, I copy the command line (/FILE, /DTS or /SQL) and with DTEXEC utility it works when this command is executed on the server (on a client computer, the problem "the product level is insufficient" appears).

Why is it working with this utility and not with a job ?

I try to change every secured option (credential --> proxy --> Run as ; change the owner of the job ; etc.)

What options must be checked and modified ?

Thanks for your help !

Somewhere there will be an error, and without that I have no idea. Try using DTEXEC through a job.

Package Execution with SQL Server Agent or DTEXEC

Hello,

I want execute package with SQL Server Agent. I create a job and I try all the solutions : my job doesn't want to work.

I success to launch the package but the execution failed.

I try the same job launching from the server : it doesn't work either.

So, I copy the command line (/FILE, /DTS or /SQL) and with DTEXEC utility it works when this command is executed on the server (on a client computer, the problem "the product level is insufficient" appears).

Why is it working with this utility and not with a job ?

I try to change every secured option (credential --> proxy --> Run as ; change the owner of the job ; etc.)

What options must be checked and modified ?

Thanks for your help !

I need HELP please :)|||

We created a package which read a flat file from another server and imported it into a table. Our package would run, but the job would not.

We found that we had to change two options - one was the location of the flat file. Our package was looking at a specific directory like "J:\Import Files\file.txt". We replaced the specific drive letter with the shared directory alias \\ServerName\Alias\Import Files\file.txt.

The second change was to alter the package to "Rely on Server ..." instead of "Encrypt Sensitive With User Key".

These two changes allowed Sql Agent to run the job.

|||

Hello,

thank you for answering, I tried your solution but no effect appears.

Actually your solution enable to execute the package in a step with a job. It seems to not fail.

The package executes through DTEXEC works fine and my destination table is filled.

When I use the same command line with a job, it works but my table is still empty. No new lines from the package !

How is it possible considering I use the same command line ?

sql

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

Package "MyPackage" has been cancelled.

Hi,

I have scheduled a package "MyPackage" in a SQL Server Agent Job. It runs periodically with success.
Sometimes the package status is shown as "Cancelled" what led me to assume some coincidences.
So I took a look at the eventlog which contains the following message:
Event Type: Information
Event Source: SQLISPackage
Event Category: None
Event ID: 12290
Date: 30.01.2006
Time: 08:00:42
User: NT AUTHORITY\SYSTEM
Computer: MYSERVER
Description: Package "MyPackage" has been cancelled.

I assume that the problem occurs when a user locally logs off from the Server where the SSIS-service is running.

Why does this happen? The Service runs under the local system account and so does the SSIS Job.

Can anybody help here?
THX
FridtjofIf you think it is a user logging off the system, can you reproduce this in a controlled manner? I do not see why this should be the cause. Unfortunately I don't know why this happens either. Have you looked in the SQL Server Agent job history? have you tried adding some logging to the package and see what that shows? The CmdExec job step may give different output to the SSIS job step type, but make sure you set the job step output file.|||I have the same issue. Does anyone have a resolution?|||This is a bug in SSIS, and you've correctly identified the cause - a user logoff may cause this to the packages being executed.

We've recently got a bug report and currently testing a fix for this issue. Expect the fix in SP2 release.

Package "MyPackage" has been cancelled.

Hi,

I have scheduled a package "MyPackage" in a SQL Server Agent Job. It runs periodically with success.
Sometimes the package status is shown as "Cancelled" what led me to assume some coincidences.
So I took a look at the eventlog which contains the following message:
Event Type: Information
Event Source: SQLISPackage
Event Category: None
Event ID: 12290
Date: 30.01.2006
Time: 08:00:42
User: NT AUTHORITY\SYSTEM
Computer: MYSERVER
Description: Package "MyPackage" has been cancelled.

I assume that the problem occurs when a user locally logs off from the Server where the SSIS-service is running.

Why does this happen? The Service runs under the local system account and so does the SSIS Job.

Can anybody help here?
THX
FridtjofIf you think it is a user logging off the system, can you reproduce this in a controlled manner? I do not see why this should be the cause. Unfortunately I don't know why this happens either. Have you looked in the SQL Server Agent job history? have you tried adding some logging to the package and see what that shows? The CmdExec job step may give different output to the SSIS job step type, but make sure you set the job step output file.|||I have the same issue. Does anyone have a resolution?|||This is a bug in SSIS, and you've correctly identified the cause - a user logoff may cause this to the packages being executed.

We've recently got a bug report and currently testing a fix for this issue. Expect the fix in SP2 release.