Scheduled ssis insert row

0

I am trying to use SSIS to simply insert 3 rows into a table daily. The package executes fine in VS. When I try to schedule the package it errors out with a privilege error. The package owner has full rights on the table. The SQL code has been working in a DTS package for years.

insert into tasks values(
        'Daily Change','Noel',convert(varchar(10),getdate(),101)+ ' 12:00',0,
            null,null,convert(varchar(10),getdate(),101)+ ' 11:30', convert(varchar(10),getdate(),101)+ ' 12:00', null, null, null, null);

insert into tasks values(
        'Daily Change','Noel', convert(varchar(10),getdate(),101)+ ' 17:00',0,
            null,null,convert(varchar(10),getdate(),101)+ ' 16:30', convert(varchar(10),getdate(),101)+ ' 17:00', null, null, null, null);

insert into tasks values(
        'Daily Change','Noel', convert(varchar(10),getdate(),101)+ ' 10:00',0,
            null,null,convert(varchar(10),getdate(),101)+ ' 09:00', convert(varchar(10),getdate(),101)+ ' 10:00', null, null, null, null);

Any ideas??

Here is the entire error message:

Executed as user: domain\user. Microsoft (R) SQL Server Execute Package Utility Version 10.0.5500.0 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 7:08:03 AM
Error: 2016-02-26 07:08:04.13 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: 2016-02-26 07:08:04.45 Code: 0xC0202009
Source: Package_name Connection manager "Microsoft OLE DB Provider for SQL Server"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E4D Description: "Login failed for user 'sa'.". End Error Error: 2016-02-26 07:08:04.46 Code: 0xC00291EC
Source: Execute SQL Task undefined Execute SQL Task
Description: Failed to acquire connection "Microsoft OLE DB Provider for SQL Server". 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: 7:08:03 AM Finished: 7:08:04 AM
Elapsed: 0.78 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0

ssis
asked on Stack Overflow Feb 25, 2016 by user1879417 • edited Feb 26, 2016 by user1879417

1 Answer

0

If you scheduled the package with SQL Agent, the package is running under the login of the SQL Agent, and not the package owner. You need to give the SQL Agent's login the necessary permissions.

answered on Stack Overflow Feb 25, 2016 by Tab Alleman

User contributions licensed under CC BY-SA 3.0