SSIS Script Component - will only run in debug mode
I have a frustrating problem.
I have an SSIS Script component (the one in the data stream to avoid confusion) that calls a GET REST API. Now I'm an SSIS person, but cobbled together a .net script, with some help, that works, but only with breakpoints, even if disabled.
As soon as I remove the breakpoints, I get the following error.
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables100.get_Item (object index) at Variables.get_AddressBookIdDownload () at ScriptMain.CreateNewOutputRows () at UserComponent.PrimeOutput (Outputs Int32, Int32, OutputMuffer [] OutputapID, PipelineBuffers ) to Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput (outputs Int32, Int32 [] outputID, PipelineBuffer [] buffers)
Someone (intelligently) asked what the .net code is to troubleshoot ... So I pasted it below ... Including all field mappings in case this is a problem ...
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Net;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Web.Script.Serialization;
#endregion
#region
[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()
{
//Get SSIS Variables
string apiUserName = Variables.APIUsername;
string apiPassword = Variables.APIPassword;
int campaignId = (int) Variables.Campaign;
//Set Webservice URL
string wUrl = "https://api.aaaaaaaa.com/" + String.Concat(campaignId, "/summary");
String base64 = Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(apiUserName + ":" + apiPassword));
try
{
//Call getWebServiceResult to return our WorkGroupMetric array
RootObject[] outPutMetrics = GetWebServiceResult(wUrl, base64 );
//For each group of metrics output records
//these are the column names defined in output columns in object
foreach (var metric in outPutMetrics)
{
Output0Buffer.AddRow();
Output0Buffer.dateSent = (metric.dateSent);
Output0Buffer.numUniqueOpens = metric.numUniqueOpens;
Output0Buffer.numUniqueTextOpens = metric.numUniqueTextOpens;
Output0Buffer.numTotalUniqueOpens = metric.numTotalUniqueOpens;
Output0Buffer.numOpens = metric.numOpens;
Output0Buffer.numTextOpens = metric.numTextOpens;
Output0Buffer.numTotalOpens = metric.numTotalOpens;
Output0Buffer.numClicks = metric.numClicks;
Output0Buffer.numTextClicks = metric.numTextClicks;
Output0Buffer.numTotalClicks = metric.numTotalClicks;
Output0Buffer.numPageViews = metric.numPageViews;
Output0Buffer.numTotalPageViews = metric.numTotalPageViews;
Output0Buffer.numTextPageViews = metric.numTextPageViews;
Output0Buffer.numForwards = metric.numForwards;
Output0Buffer.numTextForwards = metric.numTextForwards;
Output0Buffer.numEstimatedForwards = metric.numEstimatedForwards;
Output0Buffer.numTextEstimatedForwards = metric.numTextEstimatedForwards;
Output0Buffer.numTotalEstimatedForwards = metric.numTotalEstimatedForwards;
Output0Buffer.numReplies = metric.numReplies;
Output0Buffer.numTextReplies = metric.numTextReplies;
Output0Buffer.numTotalReplies = metric.numTotalReplies;
Output0Buffer.numHardBounces = metric.numHardBounces;
Output0Buffer.numTextHardBounces = metric.numTextHardBounces;
Output0Buffer.numTotalHardBounces = metric.numTotalHardBounces;
Output0Buffer.numSoftBounces = metric.numSoftBounces;
Output0Buffer.numTextSoftBounces = metric.numTextSoftBounces;
Output0Buffer.numTotalSoftBounces = metric.numTotalSoftBounces;
Output0Buffer.numUnsubscribes = metric.numUnsubscribes;
Output0Buffer.numTextUnsubscribes = metric.numTextUnsubscribes;
Output0Buffer.numTotalUnsubscribes = metric.numTotalUnsubscribes;
Output0Buffer.numIspComplaints = metric.numIspComplaints;
Output0Buffer.numTextIspComplaints = metric.numTextIspComplaints;
Output0Buffer.numTotalIspComplaints = metric.numTotalIspComplaints;
Output0Buffer.numMailBlocks = metric.numMailBlocks;
Output0Buffer.numTextMailBlocks = metric.numTextMailBlocks;
Output0Buffer.numTotalMailBlocks = metric.numTotalMailBlocks;
Output0Buffer.numSent = metric.numSent;
Output0Buffer.numTextSent = metric.numTextSent;
Output0Buffer.numTotalSent = metric.numTotalSent;
Output0Buffer.numRecipientsClicked = metric.numRecipientsClicked;
Output0Buffer.numDelivered = metric.numDelivered;
Output0Buffer.numTextDelivered = metric.numTextDelivered;
Output0Buffer.numTotalDelivered = metric.numTotalDelivered;
Output0Buffer.percentageDelivered = metric.percentageDelivered;
Output0Buffer.percentageUniqueOpens = metric.percentageUniqueOpens;
Output0Buffer.percentageOpens = metric.percentageOpens;
Output0Buffer.percentageUnsubscribes = metric.percentageUnsubscribes;
Output0Buffer.percentageReplies = metric.percentageReplies;
Output0Buffer.percentageHardBounces = metric.percentageHardBounces;
Output0Buffer.percentageSoftBounces = metric.percentageSoftBounces;
Output0Buffer.percentageUsersClicked = metric.percentageUsersClicked;
Output0Buffer.percentageClicksToOpens = metric.percentageClicksToOpens ;
}
}
catch (Exception e)
{
//FailComponent(e.ToString());
if (e.Message != null)
{
string ExceptionMessage = e.Message;
}
}
}
/// <returns>An array of WorkGroupMetric composed of the de-serialized JSON</returns>
private RootObject[] GetWebServiceResult(string wUrl, string base64)
{
HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
httpWReq.Headers.Add("Authorization", "Basic " + base64);
HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();
RootObject[] jsonResponse = null;
try
{
//Test the connection
if (httpWResp.StatusCode == HttpStatusCode.OK)
{
Stream responseStream = httpWResp.GetResponseStream();
dataLength = httpWResp.ContentLength;
string jsonString = null;
//Set jsonString using a stream reader
using (StreamReader reader = new StreamReader(responseStream))
{
jsonString = reader.ReadToEnd().Replace("\\", "");
reader.Close();
}
//Deserialize our JSON
JavaScriptSerializer sr = new JavaScriptSerializer();
jsonResponse = sr.Deserialize<RootObject[]>(jsonString);
}
//Output connection error message
else
{
FailComponent(httpWResp.StatusCode.ToString());
}
}
//Output JSON parsing error
catch (Exception ex)
{
if (ex.Message != null)
{
string ExceptionMessage = ex.Message;
}
}
return jsonResponse;
}
/// <summary>
/// Outputs error message
/// </summary>
/// <param name="errorMsg">Full error text</param>
private void FailComponent(string errorMsg)
{
bool fail = false;
IDTSComponentMetaData100 compMetadata = this.ComponentMetaData;
compMetadata.FireError(1, "Error Getting Data From Webservice!", errorMsg, "", 0, out fail);
}
}
#endregion
#region JSON Class
//Class to hold our work group metrics
public class RootObject
{
public DateTime dateSent { get; set; }
public int numUniqueOpens { get; set; }
public int numUniqueTextOpens { get; set; }
public int numTotalUniqueOpens { get; set; }
public int numOpens { get; set; }
public int numTextOpens { get; set; }
public int numTotalOpens { get; set; }
public int numClicks { get; set; }
public int numTextClicks { get; set; }
public int numTotalClicks { get; set; }
public int numPageViews { get; set; }
public int numTotalPageViews { get; set; }
public int numTextPageViews { get; set; }
public int numForwards { get; set; }
public int numTextForwards { get; set; }
public int numEstimatedForwards { get; set; }
public int numTextEstimatedForwards { get; set; }
public int numTotalEstimatedForwards { get; set; }
public int numReplies { get; set; }
public int numTextReplies { get; set; }
public int numTotalReplies { get; set; }
public int numHardBounces { get; set; }
public int numTextHardBounces { get; set; }
public int numTotalHardBounces { get; set; }
public int numSoftBounces { get; set; }
public int numTextSoftBounces { get; set; }
public int numTotalSoftBounces { get; set; }
public int numUnsubscribes { get; set; }
public int numTextUnsubscribes { get; set; }
public int numTotalUnsubscribes { get; set; }
public int numIspComplaints { get; set; }
public int numTextIspComplaints { get; set; }
public int numTotalIspComplaints { get; set; }
public int numMailBlocks { get; set; }
public int numTextMailBlocks { get; set; }
public int numTotalMailBlocks { get; set; }
public int numSent { get; set; }
public int numTextSent { get; set; }
public int numTotalSent { get; set; }
public int numRecipientsClicked { get; set; }
public int numDelivered { get; set; }
public int numTextDelivered { get; set; }
public int numTotalDelivered { get; set; }
public double percentageDelivered { get; set; }
public double percentageUniqueOpens { get; set; }
public double percentageOpens { get; set; }
public double percentageUnsubscribes { get; set; }
public double percentageReplies { get; set; }
public double percentageHardBounces { get; set; }
public double percentageSoftBounces { get; set; }
public double percentageUsersClicked { get; set; }
public double percentageClicksToOpens { get; set; }
}
#endregion
Any thoughts?
+3
source to share
1 answer
An annoying colleague looked at the script and found nothing wrong with it that he could see, so on a sideways thought he created a new script component and pasted the C # script in, and after creating the interfaces, it works!
Not sure, but maybe there were some artifacts left over from the development of the script that were causing problems?
+3
source to share