执行脚本任务引发错误“程序包执行返回了DTSER_FAILURE（1）”

我用文件系统任务替换了脚本任务，它可以正常工作。我还创建了一个代理帐户，以使用我的系统凭据运行该程序包，以便它可以访问指定的文件夹。

采取步骤创建用于运行SQL作业的代理帐户，以便对寻找它的用户有所帮助。

在需要运行作业的SQL中创建一个用户帐户，为创建的用户帐户创建凭据。

    --Script #1 - Creating a credential to be used by proxy
USE MASTER
GO 
--Drop the credential if it is already existing 
IF EXISTS (SELECT 1 FROM sys.credentials WHERE name = N'SSISProxyCredentials') 
BEGIN 
DROP CREDENTIAL [SSISProxyCredentials] 
END 
GO 
CREATE CREDENTIAL [SSISProxyCredentials] 
WITH IDENTITY = N'<Insert the Username>', 
SECRET = N'abcd@0987' 
GO

创建代理帐户并关联创建的凭据

--Script #2 - Creating a proxy account 
USE msdb
GO 
--Drop the proxy if it is already existing 
IF EXISTS (SELECT 1 FROM msdb.dbo.sysproxies WHERE name = N'SSISProxyDemo') 
BEGIN 
EXEC dbo.sp_delete_proxy 
@proxy_name = N'SSISProxyDemo' 
END 
GO 
--Create a proxy and use the same credential as created above 
EXEC msdb.dbo.sp_add_proxy 
@proxy_name = N'SSISProxyDemo', 
@credential_name=N'SSISProxyCredentials', 
@enabled=1 
GO 
--To enable or disable you can use this command 
EXEC msdb.dbo.sp_update_proxy 
@proxy_name = N'SSISProxyDemo', 
@enabled = 1 --@enabled = 0 
GO

将代理帐户授予SQL Server代理子系统

USE msdb
GO 
--You can view all the sub systems of SQL Server Agent with this command
--You can notice for SSIS Subsystem id is 11 
EXEC sp_enum_sqlagent_subsystems 
GO

--Grant created proxy to SQL Agent subsystem 
--You can grant created proxy to as many as available subsystems 
EXEC msdb.dbo.sp_grant_proxy_to_subsystem 
@proxy_name=N'SSISProxyDemo', 
@subsystem_id=11 --subsystem 11 is for SSIS as you can see in the above image 
GO 
--View all the proxies granted to all the subsystems 
EXEC dbo.sp_enum_proxy_for_subsystem

授予对安全主体的代理访问权限

USE msdb
GO 
--Grant proxy account access to security principals that could be
--either login name or fixed server role or msdb role
--Please note, Members of sysadmin server role are allowed to use any proxy 
EXEC msdb.dbo.sp_grant_login_to_proxy 
@proxy_name=N'SSISProxyDemo' 
,@login_name=N'<Insert the Username>' 
--,@fixed_server_role=N'' 
--,@msdb_role=N'' 
GO 
--View logins provided access to proxies 
EXEC dbo.sp_enum_login_for_proxy 
GO

最后，将代理帐户关联到打包步骤。也可以通过作业向导来完成。

EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'SSISPackageCall', 
@step_id=1, 
@cmdexec_success_code=0, 
@on_success_action=1, 
@on_success_step_id=0, 
@on_fail_action=2, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'SSIS', 
@command=N'/FILE "C:\Package.dtsx" /CHECKPOINTING OFF /REPORTING E', 
@database_name=N'master', 
@flags=0, 
@proxy_name = N'SSISProxyDemo';

感谢您对我的帖子的宝贵答复。