Reputation: 13
I've created a SSIS package that downloads a certain website and parses its HTML code for a table value. It works on my PC, but when run using DB server, it crashes due to
Error: 0xC001000E at Package: The connection "" is not found. This error is thrown by Connections collection when the specific connection element is not found. Error: 0xC02020EA at Package, Log provider "SSIS log provider for SQL Server": The connection manager "" is not found. A component failed to find the connection manager in the Connections collection
I've double checked all connections, went through XML code for any signs of an empty connection, but found nothing. I'm using a script component for scraping. Is there anything I missed?
EDIT: changing ProtectionLevel didn't help
EDIT: Added more info. No expressions are used in this project.
SQL task
Overview of Scraping data flow
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Net.Http;
using System.IO;
using Microsoft.SqlServer.Dts.Runtime;
using System.Threading.Tasks;
using HtmlAgilityPack;
#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()
{
string url = "https://vdb.czso.cz/vdbvo2/faces/index.jsf?page=vystup-objekt&f=TABULKA&z=T&skupId=6352&katalog=31779&pvo=CENY-PHM&pvo=CENY-PHM";
float dieselPrice = 0.0f;
try
{
Task.Run(async () =>
{
using (HttpClient client = new HttpClient())
{
HttpResponseMessage response = await client.GetAsync(url);
response.EnsureSuccessStatusCode();
string htmlContent = await response.Content.ReadAsStringAsync();
HtmlDocument doc = new HtmlDocument();
doc.LoadHtml(htmlContent);
var cell = doc.DocumentNode.SelectSingleNode("//table[@id='tabData']/tbody/tr[1]/td[3]/span");
if (cell != null)
{
string priceText = cell.InnerText.Trim();
if (priceText == ".")
{
var nextCell = doc.DocumentNode.SelectSingleNode("//table[@id='tabData']/tbody/tr[2]/td[3]/span");
if (nextCell != null)
{
priceText = nextCell.InnerText.Trim();
}
}
priceText = priceText.Replace(',', '.');
if (float.TryParse(priceText, out float price))
{
dieselPrice = price;
}
}
}
}).GetAwaiter().GetResult();
}
catch (Exception ex)
{
ComponentMetaData.FireError(0, "Script Task", ex.Message, string.Empty, 0, out bool cancel);
}
Output0Buffer.AddRow();
Output0Buffer.HTMLscrape = dieselPrice;
}
}
Upvotes: 0
Views: 39