Schedule SSIS job in SQL Agent fails

Question

Schedule SSIS job in SQL Agent fails

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Sunil Kumar P T-457024 SSC Veteran Points: 287 More actions · Answer 1

[font="Comic Sans MS"]Can you please tell me how to check whether SSIS services in running in my system

Under SQL Server Agent while creating a job step, in the Type drop-down box i am not finding "SQL Server Integration Services Package".

Is it because SSIS is not running or

is it because 2005 is not properly installed or anything is missing?

Please help me out, i am in very much need of it..[/font]

Megha Yadav SSCommitted Points: 1735 More actions · Answer 2

Go to Start>Run

Type in "Services.msc"

Check in that for SQL Server Integration services by right clicking it .... Check whether it is running or not

Hope that helps 🙂

Sunil Kumar P T-457024 SSC Veteran Points: 287 More actions · Answer 3

I have seen SQL Server Integration Services it shows me it is running..

what may be the problem why i am not able to Integration Services package while scheduling package..

Plz help me

Megha Yadav SSCommitted Points: 1735 More actions · Answer 4

I am not sure why is this happening ... and truly speaking i got no clue.

But do a small little check .. Like you connect to the database engine ...connect to the integration services of the same server (Just trying to check that integration services are on the same server instance on which you are trying to run the SQL job)

And my base assumption is that you are connecting to SQL Server 2005

Daniel Corona SSC Enthusiast Points: 197 More actions · Answer 5

when you save the package to the SSIS make sure you rely on the security of the SQL Server

to run the package .Then the SQL Agent account with rights will execute the package succefully

satish -476230 Grasshopper Points: 13 More actions · Answer 6

Thanks a lot Steve, this post helped me to resolve the issue. I had SSIS package with password and it trying to schedule it as a SQL job with command line option.

Now I scheduled it as regular SSIS job and specified the password in the command line.The job runs successfully now.

Satish

Sunil Kumar P T-457024 SSC Veteran Points: 287 More actions · Answer 7

[font="Comic Sans MS"]Hey thanks for your help...

Now i can able to execute the imported package..

The problem now is Packages are running fine when i ran from BIDS..

when i want to schedule package as job i am getting following error..

"Unable to cast oject of type 'Microsoft.Sqlserver.Management.Smo.SimpleObjectKey' to type

'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'.(Microsoft.SqlServer.Smo)"

Any thing i need to Configure in order to run package as Job????

Please help me where i am wrong.. if scheduling of package is completed almost my job is done... Plz any one who know regarding this error plz help me[/font]

ppatel-626875 Newbie Points: 9 More actions · Answer 8

I have a very similar issue with SSIS and scheduled jobs. 2 of my jobs work and 2 do not. The 2 that do not access a dbase database to import the tables. They are both old DTS packages. I can run them both in Visual Studio 2005 using the task 'Execute DTS 200 Package Task'. I can run them under MS Management Studio under Management > Legacy > Data Transformation Services. But when they are scheduled, I get the following error:

SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:00:00 AM Error: 2008-02-06 01:00:01.31 Code: 0x00000000 Source: Import Quickfill Tables Description: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user. at DTS.PackageClass.Execute() at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread() End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:00:00 AM Finished: 1:00:01 AM Elapsed: 0.625 seconds. The package execution failed. The step failed.

I am relying on the server storage and roles for Package Protection Level, so they do not have passwords.

James Collett SSC Enthusiast Points: 160 More actions · Answer 9

S.K. (3/8/2007)
All,
The standard security on an SSIS package is "EncryptSensistiveWithUserKey" which only allows the user that created it to be able to run in as long as there is sensitive information (passwords) stored within the package.
A workaround that I am using is that I changed the package security to "EncryptAllWithPassword" which protects the entire package with a password.
...
...
Steve

I have an apparently similar problem. I tried this but it does not solve my problem.

SQListic SSCertifiable Points: 6904 More actions · Answer 10

http://sqlism.blogspot.com/2012/08/ssis-package-in-sql-server-agent-job.html