UPDATE! SOLVED.
After many hours of trouble shooting we found out that for some reason the API required TLS 1.2 from one server, while normal SSL was fine for another. How it can be like that is beyond me, but this was the problem. Once we implemented TLS 1.2 for the other server (within the script component) it started working.
If anyone else needs to add TLS 1.2 in a script component this is the line we used;
System.Net.ServicePointManager.SecurityProtocol = System.Net.SecurityProtocolType.Tls12 | System.Net.SecurityProtocolType.Ssl3;
Original question
I understand that this must be virtually impossible to solve based on how little it is to go on, but I would appreciate any ideas or pointers regarding where to start poking.
The SSIS-package in question runs flawless within Microsoft SQL Server Data Tools for Visual Studio 2017 (SSDT). When I try to run it from the agent in SQL Server 2019, however, it fails whatever I try.
The package is configured with: TargetServerVersion = SQL Server 2019. The package is of "Package Deployment Model" with a separate config file. The PackageFormatVersion is 8, which should correspond well to the installed "SQL Integration Services 15" on the server.
The package layout is very simple. I has a small script component, which calls an API and once it has the response it simply saves it to a text file. That's it.
4 parameters (fed from the config file) are input for the script.
User::md_user_domain
User::md_user_name
User::md_user_password
User::municipality
One "using" parameter has been added and it is a method belonging to the endpoint I am communicating with; SC_fcb07b9b587a4b0c8d400529a3ce9b96.Masterdata
The script code for this is shown below.
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using SC_fcb07b9b587a4b0c8d400529a3ce9b96.Masterdata;
#endregion
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
public override void PreExecute()
{
base.PreExecute();
}
public override void PostExecute()
{
base.PostExecute();
}
public override void CreateNewOutputRows()
{
var remoteAddress = new System.ServiceModel.EndpointAddress("https://sub.domain.com/Service.svc");
var binding = new System.ServiceModel.BasicHttpBinding();
binding.Security.Mode = System.ServiceModel.BasicHttpSecurityMode.Transport;
binding.Security.Transport.ClientCredentialType = System.ServiceModel.HttpClientCredentialType.Basic;
binding.MaxReceivedMessageSize = 600000000;
using (var client = new MasterdataClient(binding, remoteAddress))
{
client.ClientCredentials.UserName.UserName = Variables.mduserdomain + "\\" + Variables.mdusername;
client.ClientCredentials.UserName.Password = Variables.mduserpassword;
MasterDataBuffer.AddRow();
var xmlIn = @"<Get Objekt=""Befolkning"" Ver=""2.0""><Query Kommun=""" + Variables.municipality + @"""></Query></Get>";
var response = client.XMLCall(xmlIn);
MasterDataBuffer.xmlresidents.AddBlobData(System.Text.Encoding.GetEncoding(1252).GetBytes(response));
}
}
}
In order to remove as much doubt regarding file access rights as possible I run the package with a proxy of my user that has access. In an act of desperation I even shared the target folder with everyone, but that didn't change the outcome at all.
I also tried saving the package with different forms of protections (again out of desperation), rebuilding it from scratch to see if there is some unknown element that has snuck in,
The only output of the script component is a text stream [DT_TEXT] that is then saved via a "Flat File Destination".
Executed as user: domain\myuser
Microsoft (R) SQL Server Execute Package Utility Version 15.0.4198.2 for 64-bit Copyright (C) 2019 Microsoft. All rights reserved.
Started: 01:28:54
Error: 2022-05-11 01:28:54.43
Code: 0xC0047038
Source: Get data SSIS.Pipeline
Description:
SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.
The PrimeOutput method on Script Component returned error code 0x80131501.
The component returned a failure code when the pipeline engine called PrimeOutput().
The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
There may be error messages posted before this with more information about the failure.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 01:28:54
Finished: 01:28:54
Elapsed: 0.281 seconds.
The package execution failed.
The step failed.
I have tried to find anything I can about the error 0x80131501, but since this error is tied not only to the script component, but the method PrimeOutput within that, it is slime pickings. I am totally out of ideas and have already spent way longer just trying to get the agent to run the package than actually developing the package itself.
If I try to run the package from directly from the command line I get the following result;
Microsoft (R) SQL Server Execute Package Utility
Version 15.0.4198.2 for 64-bit
Copyright (C) 2019 Microsoft. All rights reserved.
Started: 08:16:05
Info: 2022-05-11 08:16:05.77
Code: 0x40016041
Source: md_kir_to_lime
Description: The package is attempting to configure from the XML file "..\Program Configs\md_kir_config.dtsConfig".
End Info
Info: 2022-05-11 08:16:05.79
Code: 0x40016041
Source: md_kir_to_Program
Description: The package is attempting to configure from the XML file "c:\Program Configs\md_kir_config.dtsConfig".
End Info
Info: 2022-05-11 08:16:05.79
Code: 0x40016041
Source: md_kir_to_Program
Description: The package is attempting to configure from the XML file "..\Program Configs\md_kir_config.dtsConfig".
End Info
Info: 2022-05-11 08:16:05.79
Code: 0x4004300A
Source: Get data SSIS.Pipeline
Description: Validation phase is beginning.
End Info
Info: 2022-05-11 08:16:05.88
Code: 0x4004300A
Source: Get data SSIS.Pipeline
Description: Validation phase is beginning.
End Info
Info: 2022-05-11 08:16:05.90
Code: 0x40043006
Source: Get data SSIS.Pipeline
Description: Prepare for Execute phase is beginning.
End Info
Info: 2022-05-11 08:16:05.92
Code: 0x40043007
Source: Get data SSIS.Pipeline
Description: Pre-Execute phase is beginning.
End Info
Info: 2022-05-11 08:16:05.94
Code: 0x402090DC
Source: Get data Flat File Destination [2]
Description: The processing of file "\\server\Program\commune.txt" has started.
End Info
Info: 2022-05-11 08:16:05.97
Code: 0x4004300C
Source: Get data SSIS.Pipeline
Description: Execute phase is beginning.
End Info
Error: 2022-05-11 08:16:06.13
Code: 0xC0047038
Source: Get data SSIS.Pipeline
Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.
The PrimeOutput method on Script Component returned error code 0x80131501.
The component returned a failure code when the pipeline engine called PrimeOutput().
The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
There may be error messages posted before this with more information about the failure.
End Error
Info: 2022-05-11 08:16:06.13
Code: 0x40043008
Source: Get data SSIS.Pipeline
Description: Post Execute phase is beginning.
End Info
Info: 2022-05-11 08:16:06.13
Code: 0x402090DD
Source: Get data Flat File Destination [2]
Description: The processing of file "\\server\Program\commune.txt" has ended.
End Info
Info: 2022-05-11 08:16:06.13
Code: 0x4004300B
Source: Get data SSIS.Pipeline
Description: "Flat File Destination" wrote 0 rows.
End Info
Info: 2022-05-11 08:16:06.13
Code: 0x40043009
Source: Get data SSIS.Pipeline
Description: Cleanup phase is beginning.
End Info
Warning: 2022-05-11 08:16:06.13
Code: 0x80019002
Source: md_kir_to_Program
Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.
The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1);
resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount.
Change the MaximumErrorCount or fix the errors.
End Warning
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 08:16:05
Finished: 08:16:06
Elapsed: 0.469 seconds
Any help or suggestions would be greatly appreciated! If you need me to clarify
