关于SQLSERVER创建代理job的一些问题

最近在部署服务器数据库脚本自动化的时候，摸索出了一些小问题，主要是跟在SQLserver中运行SSIS包，运行sql语句，运行cmd和powershell的一些问题和解决方案，用这篇笔记来做备忘

执行SSIS包

提示密码错误

我这里遇到的问题是，在VS里面跑SSIS包没有任何问题，能够跑通，但是到了SQLserver中创建代理跑的时候老是报下面的错误。这里找到了网上的解决方案。参考这篇博文：点击跳转

主要思想是在VS里面设置包的属性，降低安全级别

日期 2019/6/3 11:24:33
日志 作业历史记录 (Bigdata_DB_Update)

步骤 ID 2
服务器 WIN-JEUK1JNQDPO
作业名称 Bigdata_DB_Update
步骤名称 SO_RAW_ALL_EMEA
持续时间 00:00:00
SQL 严重性 0
SQL 消息 ID 0
已通过电子邮件通知的操作员
已通过网络发送通知的操作员
已通过寻呼通知的操作员
重试次数 0

消息
Executed as user: NT Service\SQLSERVERAGENT. Microsoft ® SQL Server Execute Package Utility Version 11.0.6020.0 for 64-bit Copyright © Microsoft Corporation. All rights reserved. Started: 11:24:33 AM Error: 2019-06-03 11:24:33.47 Code: 0xC0016016 Source: SO_RAW_ALL_EMEA 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: 2019-06-03 11:24:33.60 Code: 0xC0016016 Source: SO_RAW_ALL_EMEA 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: 2019-06-03 11:24:33.71 Code: 0xC0202009 Source: SO_RAW_ALL_EMEA Connection manager “10.103.66.45, 1444.Fundamental_Integration_Test.sa” Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80040E4D Description: “Login failed for user ‘sa’.”. End Error Error: 2019-06-03 11:24:33.71 Code: 0xC020801C Source: SO_RAW_ALL_EMEA Log provider “{AC88BCFA-7DD7-465E-B7B4-BE831908E104}” Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager “10.103.66.45, 1444.Fundamental_Integration_Test.sa” failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. COM error object information is available. Source: “SO_RAW_ALL_EMEA” error code: 0xC0202009 Description: "SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80040E4D Description: “Login failed for user ‘sa’.”. ". End Error Error: 2019-06-03 11:24:33.72 Code: 0xC0202009 Source: SO_RAW_ALL_EMEA Connection manager “10.103.66.45, 1444.Fundamental_Integration_Test.sa” Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80040E4D Description: “Login failed for user ‘sa’.”. End Error Error: 2019-06-03 11:24:33.72 Code: 0xC020801C Source: Data Flow Task OLE DB Destination 1 [2] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager “10.103.66.45, 1444.Fundamental_Integration_Test.sa” 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: 2019-06-03 11:24:33.72 Code: 0xC0047017 Source: Data Flow Task SSIS.Pipeline Description: OLE DB Destination 1 failed validation and returned error code 0xC020801C. End Error Error: 2019-06-03 11:24:33.72 Code: 0xC004700C Source: Data Flow Task SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2019-06-03 11:24:33.72 Code: 0xC0024107 Source: Data Flow Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:24:33 AM Finished: 11:24:33 AM Elapsed: 0.563 seconds. The package execution failed. The step failed.

提示无权限

这可能是代理账户的权限不够，可以在SQLserver配置管理器中设置，具体参见这篇博文：点击跳转

SSIS报 An OLE DB error has occurred. Error code: 0x80004005

如果你的账户配置没用错误的话（没用报xml什么错误），那么很大的可能是因为数据源和目标数据库的数据编码不一致，强制使用一致的编码就好了：

1. 使用数据转换功能
2. 修改数据源和目标源的属性，使其编码一致

总结

折腾了整整了一下午，我总算是找出原因了。对于SSIS包报错但是在VS中运行正常，权限不够，无法跑cmd和powershell脚本。有以下几点原因：

1. SSIS包无法跑，极有可能是因为sqlserver agent账户并不是SSIS包的创建者，因此需要在SQLserver配置管理器中对账户进行设置，设置为电脑管理员或者服务器管理员
2. cmd和powershell无法跑，有可能是SQLagent账户无权限；改变权限还是失败的话，可以参考上一条改变SQLagent账户信息