我创建了一个 SSIS 包,它使用 C# 脚本从 HTTPS URL 下载 CSV 文件。从 Visual Studio 执行时一切正常。
但是,当我创建 SQL 代理作业时,包失败。如果我直接从 SQL Server 执行 .dtsx 文件,该包也会失败。
错误是
“下载失败:从服务器获取 SSL 证书响应
无效。无法处理请求”
我已经测试过从 SQL 服务器打开 URL 并且可以查看该文件。
我还在 C# 脚本中添加了代码以忽略认证错误,但这似乎没有任何影响。
我还在连接管理器中输入了托管 CSV 的服务器的登录凭据,但这也没有帮助。
CSV 通过 Magento 数据源生成。网站上的所有内容都重定向到 HTTPS。
我在这里迷失了所以希望有人能提供帮助。
下面是 C# 脚本的片段:
public void Main()
{
// TODO: Add your code here
try
{
// Ignore certificate warnings
ServicePointManager.ServerCertificateValidationCallback =
new RemoteCertificateValidationCallback(delegate { return true; });
// Disable server certificate validation.
//ServicePointManager
// .ServerCertificateValidationCallback +=
// (sender, cert, chain, sslPolicyErrors) => true;
//ServicePointManager.ServerCertificateValidationCallback = (sender, cert, chain, sslPolicyErrors) => true;
//System.Net.ServicePointManager.ServerCertificateValidationCallback =
//delegate (object sender, X509Certificate certificate, X509Chain chain, SslPolicyErrors sslPolicyErrors)
//{ return true; };
// Logging start of download
bool fireAgain = true;
Dts.Events.FireInformation(0, "Download File", "Start downloading " + Dts.Connections["TEST"].ConnectionString, string.Empty, 0, ref fireAgain);
// Get your newly added HTTP Connection Manager
Object mySSISConnection = Dts.Connections["TEST"].AcquireConnection(null);
// Create a new connection
HttpClientConnection myConnection = new HttpClientConnection(mySSISConnection);
// Download file and use the Flat File Connectionstring (D:\SourceFiles\Products.csv)
// to save the file (and replace the existing file)
String filename = Dts.Variables["varFullPathScript"].Value.ToString();
myConnection.DownloadFile(filename, true);
// Logging end of download
Dts.Events.FireInformation(0, "Download File", "Finished downloading" , string.Empty, 0, ref fireAgain);
// Quit Script Task succesful
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
// Logging why download failed
Dts.Events.FireError(0, "Download File", "Download failed: " + ex.Message, string.Empty, 0);
// Quit Script Task unsuccesful
Dts.TaskResult = (int)ScriptResults.Failure;
}
Dts.TaskResult = (int)ScriptResults.Success;
}