Failed to decrypt protected XML node "PassWord" with error 0x80070002 "The system cannot find the file specified.". 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

 

SSIS Package – SQL Agent Job Error:

Executed as user: PRODUCTION\SQLSrvr. ...te Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:08:22 PM Error: 2012-07-25 22:08:23.31 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "PassWord" with error 0x80070002 "The system cannot find the file specified.". 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: 2012-07-25 22:08:23.32 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "PassWord" with error 0x80070002 "The system cannot find the file specified.". 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 DTExec: The package execution returned DTSER_SUCCESS (0). Started: 10:08:22 PM Finished: 10:. The step succeeded.

Solution:

If you are using SSIS 2005 - you need to save the packages as Do Not save sensitive - which you are doing.

When you import the package into the SSIS system you need to make sure you alter the package protection level there to do not save sensitive too. In SQL Server 2008 and above this is the default.

Reference: http://support.microsoft.com/kb/918760

Oracle Linked Server issue

Today I got the error below on the SQL Server job it runs a SSIS Package.

Error:
Msg 7302, Level 16, State 1, Server USIVA007, Line 1
Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server
"SYS01_ORACLE01"

Resolution:
  Strange but recreating of the Linked Server connection, resolved the issue.

Reference:
Techniques to Debug Connectivity Issues to an Oracle Server Using the ODBC Driver and OLE DB Provider

Do we need SSIS to create Maintenance Plan

Before SQL Server 2005 SP2 was released Maintenance plans were integreated with SSIS but after SP2 Microsoft has changed that and we do not need SSIS to be installed to run maintenance jobs. And it carried over to SQL Server 2008 too.

Multiple SQL Server Integration Services (SSIS) database instances on one machine

The registry key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTS\ServiceConfigFile specifies
the location and name for the configuration file that Integration
Services service uses. The default value of the registry key is
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\ MsDtsSrvr.ini.xml.
You can update the value of the registry key to use a different name
and location for the configuration file.

Reference: Multiple SQL Server Integration Services (SSIS) database instances on one machine - Mike's Blog

Configuring SSIS to work on Named Instance

Configuring the Integration Services Service to work on named instance:

Modify the MsDtsSvrv.ini file - this is requires for SSIS for SQL2K5 named instance on SQL Server 2005.

File Location:
\90\DTS\Binn\MsDtsSrvr.ini.xml

Changes Requried on:
1) .
Instead of . that denotes default instance Named instance should be there.
Example:
ASQLBox\SQL2K5

2) ..\Packages
If we want to change the file storage location.
Example:
\\ASQLBox\SSISPackages

Reference: http://msdn2.microsoft.com/en-us/library/ms137789.aspx

Connetion to SSIS from client computer failed

Consider the following scenario. A server is running Microsoft SQL Server 2005 Integration Services (SSIS). From a client computer, you use SQL Server Management Studio to connect to Integration Services on the server. To connect to Integration Services on the server, you use a user account that is not a member of the Administrators group on the server. In this scenario, you receive the following error message:

Cannot connect to SSISServer
Additional information: Failed to retrieve data for this request (Microsoft.SqlServer.SmoEnum)
Connect to SSIS Service on machine "SSISServer" failed: Access is denied.

Refer solution at
http://support.microsoft.com/kb/940232/en-us