Cannot Schedule SSIS Package 1

[TJunior]

Hello There,

I am trying to schedule an SSIS package using a SQL Server Agent (SSA) but it fails with the following error in my log file:

Microsoft (R) SQL Server Execute Package Utility
Version 10.50.1600.1 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.

Started: 10:09:11
Error: 2011-01-10 10:09:12.04
Code: 0xC00220E0
Source: Registrations Insert
Description: The connection is not specified.
End Error
Error: 2011-01-10 10:09:12.04
Code: 0xC0024107
Source: Registrations Insert
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 10:09:11
Finished: 10:09:12
Elapsed: 0.093 seconds

I am confused as to why the job fails to gain a connection as it works when I run it in Visual Studio but fails manually or automated from SQL Server.

I am running Win Svr 2008 R2 64bit, SQL Svr 2008 64bit and the package I want to run is on the file system. My SSA is running as SA but I have tried it as another user and get the same error. I have also tried changing the package protection level to 'DontSaveSensitive' and this didn't help.

I am at a loss for what to try next, any assistance would be great.

Thank you in advanced,

Tyrone

 

[dhulbert]

Have a look at permissions, when it runs under the job scheduler, it is run under th eid of the SQL server Agent isedid, You need to ensure that ID has access to what ever resources the package is trying to access, be they Disk, Server, Database etc.



I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[TJunior]

Hi,

Thanks for the suggestion. I have tried running the job as with a proxy which uses a user profile that has sysadmin permissions and it still doesn't work.

I also added a configuration file which now gives me a different error:

Microsoft (R) SQL Server Execute Package Utility
Version 10.50.1600.1 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.

Started: 08:09:30
Error: 2011-01-11 08:09:34.48
Code: 0xC0202009
Source: BULK_INSERT_1 Connection manager "zeus02"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-01005: null password given; logon denied".
End Error
Error: 2011-01-11 08:09:34.49
Code: 0xC020801C
Source: Application Details Data Transfer App Details on zeus02 [1]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "zeus02" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
End Error
Error: 2011-01-11 08:09:34.49
Code: 0xC0047017
Source: Application Details Data Transfer SSIS.Pipeline
Description: component "App Details on zeus02" (1) failed validation and returned error code 0xC020801C.
End Error
Error: 2011-01-11 08:09:34.50
Code: 0xC004700C
Source: Application Details Data Transfer SSIS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2011-01-11 08:09:34.50
Code: 0xC0024107
Source: Application Details Data Transfer
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 08:09:30
Finished: 08:09:34
Elapsed: 4.141 seconds

Any ideas?

 

[plevey]

Have a look at Package Protection again. It's likely that the password is not being saved. Set it to Encrypt sensitive with password and set the password. Then edit the step in SQL Server Agent job and apply the same password

If fishing was easy it would be called catching

 

[TJunior]

Sorry for the delay in updating this thread.

plevey that was exactly the issue. I changed the protection level to DontSaveSensitive, used a config file only for the connection string properties (with username and password included) and it worked a treat.

Thanks for the lead.

Have a great weekend. I will, now ;-)