Why is my SQL Server job using an SSIS package failing but that same SSIS package is running successfully in Visual Studio?

1
votes

I have created an SSIS package in Visual Studio using the SSIS Import and Export Wizard and when I run it there, it runs successfully. Basically, the SSIS package runs a sql query on my SQL Server database and exports the results into an Excel file.

I have wrapped that package into a SQL Server job and scheduled it to run at a specific time. I noticed that it failed and the log file shows the following error messages:

Microsoft (R) SQL Server Execute Package Utility Version 12.0.5000.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 4:44:00 PM Error: 2017-10-12 16:44:00.59 Code: 0xC0016016 Source: ExpExcel 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-10-12 16:44:00.68 Code: 0xC0209303 Source: ExpExcel Connection manager "DestinationConnectionExcel" Description: The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed run the package in 32-bit mode. Error code: 0x00000000. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2017-10-12 16:44:00.68 Code: 0xC00291EC Source: Drop table(s) SQL Task 1 Execute SQL Task Description: Failed to acquire connection "DestinationConnectionExcel". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Error: 2017-10-12 16:44:00.68 Code: 0xC0209303 Source: ExpExcel Connection manager "DestinationConnectionExcel" Description: The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed run the package in 32-bit mode. Error code: 0x00000000. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2017-10-12 16:44:00.68 Code: 0xC00291EC Source: Preparation SQL Task 1 Execute SQL Task Description: Failed to acquire connection "DestinationConnectionExcel". 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: 4:44:00 PM Finished: 4:44:00 PM Elapsed: 0.219 seconds. The package execution failed. The step failed.,00:00:00,0,0,,,,0

I am having a hard time trying to figure out how to debug these errors and finding the solution. If my SSIS package is running fine in Visual Studio (installed on that same machine as SQL Server), then why is it failing as a SQL Server job?

I must also add that Microsoft Office is not installed on that machine. Could that possibly be the cause?

2
sql-serverssissql-job
This is a common issue. What is the ProtectionLevel of the package? What user is SQL Agent running under? What kind of databases are you connecting to? if it's SQL Server, are you able to use windows authentication? mssqltips.com/sqlservertip/2091/… – Nick.McDermaid
The user running SQL Agent is the same user of other SQL jobs which are running just fine. I'm using SQL Server Authentication and the database is simply a SQL Server database. – user3115933
Compare the protection level on the package. It's probably user key. – Nick.McDermaid
Ok thanks. I will have a look into that too and report back. – user3115933

2 Answers

1
votes

You need to enable 32bit on your SQL Job agent step - And if that doesnt work you need to install OLDB ACE Driver for EXCEL

Driver link:

Microsoft ACE Driver

And as others point out. Your project protection level needs to be the same as the package protection level. You can change that under properties of the package and project.

How to set 32bit on step in SQL Job Agent on step enter image description here

0
votes

When you created the package it probably got set up with a User Key protection level. Meaning that any sensitive information (passwords) get encrypted with your user key. By extension, this means only you can really open/run the package. Your best bet (requiring the least amount of effort in my opinion) is to encrypt the package with a password and then you can specific that password as you are creating your SQL Agent job.