Failed to run SSIS Package

Question

Failed to run SSIS Package

rajiv.iyer

SSC Veteran

Points: 209
More actions
July 5, 2017 at 9:08 am

#327037
I have a package which has a Data Flow Task. The package takes data from Excel and pumps it into a test DB table. I have configured 2 connections in the connection manager. 1. Excel Source with file name and version and 2. SQL Server Destination DB. Steps I followed:
1. I selected Build -> Build
2. In SSMS, Created a SQL Server Agent Job with Step 1 as Execute a SSIS Package with File System.
3. Mapped it to a dtsx file in Project Solution folder.
4. Set the execution mode of the Job step to 32 bit runtime
When I run the job, it fails. Below is the error log:
Message Executed as user: ESMADODB01\sqlservice. Microsoft (R) SQL Server Execute Package Utility Version 11.0.5058.0 for 32-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 8:13:05 PM Error: 2017-07-05 20:13:06.17 Code: 0xC0016016 Source: AuditScore 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: 2017-07-05 20:13:06.40 Code: 0xC0202009 Source: AuditScore Connection manager "Excel Connection Manager" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.". End Error Error: 2017-07-05 20:13:06.40 Code: 0xC020801C Source: Push Audit Score AudioCall Import [2] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" 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: 2017-07-05 20:13:06.40 Code: 0xC0047017 Source: Push Audit Score SSIS.Pipeline Description: AudioCall Import failed validation and returned error code 0xC020801C. End Error Error: 2017-07-05 20:13:06.40 Code: 0xC004700C Source: Push Audit Score SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2017-07-05 20:13:06.40 Code: 0xC0024107 Source: Push Audit Score Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:13:05 PM Finished: 8:13:06 PM Elapsed: 0.89 seconds. The package execution failed. The step failed.

Viewing 5 posts - 1 through 5 (of 5 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

Try changing the package ProtectionLevel property - try using DontSaveSensitive.
I would guess you have it at the default of EncryptSensitiveWithUserKey

Sue

sgmunson SSC Guru Points: 110651 More actions · Answer 2

Looks like an issue with some kind of password. Have you Googled the error?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Phil Parkin SSC Guru Points: 247180 More actions · Answer 3

Sue_H - Wednesday, July 5, 2017 10:04 AM
Try changing the package ProtectionLevel property - try using DontSaveSensitive.
I would guess you have it at the default of EncryptSensitiveWithUserKey
Sue

+1

rajiv.iyer SSC Veteran Points: 209 More actions · Answer 4

Hello,

The issue has been resolved.

I set 64bit Runtime to False and enabled Windows Authentication to the connection manager.