Scheduled SSIS failing all the time - Help!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Scheduled SSIS failing all the time - Help!

Author

Topic

pafriedl
Starting Member

3 Posts

Posted - 2008-06-11 : 09:00:56

Hi all. We recently migrated to SQL Server '05 from 2000 and I'm trying to learn SSIS by crash course.

I just wrote an SSIS for one of our nightly processes in Studio 2005. It built with no errors, and the solution and resulting package are saved on the server housing SQL Server.

I ran the package in debug and it completed 100% with no errors. However, when I scheduled the package in SQL Server, it starts and fails.

Now, I'm very new to SQL '05 and I'm trying to figure out how to log the SSIS jobs, but is there any way to find out why the scheduled packages fail?

I'm desperately needing help on this, and any help would be greatly appreciated!

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-06-11 : 09:20:24

Have a look in your log directory on the server, usually <drive>:\MSSQL.1\MSSQL\LOG

If you are logging errors, they should show up in there.

Forgot to say: Its probably a permissions error with the SQL Agent account if it ran ok when you ran it locally. Your Agent probably doesn't have access to a folder you are referencing.

pafriedl
Starting Member

3 Posts

Posted - 2008-06-11 : 09:21:24

Update:
I ran one of our smaller packages and got this log file. Any way yo get this sucker to run?

************************************
Begin Log File
************************************

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 8:03:16 AM
Error: 2008-06-11 08:03:16.57
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
End Error
Error: 2008-06-11 08:03:16.89
Code: 0xC0202009
Source: Package Connection manager "RTSSQL05.RTS_Production.sa"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'sa'.".
End Error
Error: 2008-06-11 08:03:16.90
Code: 0xC00291EC
Source: Update Existing Scores Execute SQL Task
Description: Failed to acquire connection "RTSSQL05.RTS_Production.sa". Connection may not be configured correctly or you may not have the right permissions on this connection.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 8:03:16 AM
Finished: 8:03:16 AM
Elapsed: 0.5 seconds

************************************
Edn Log File
************************************

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-06-11 : 11:13:03

Try: ProtectionLevel = DontSaveSensitive when saving the package.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources