debugging 如何对SSIS任务脚本使用FireError？

zfciruhq  于 2022-11-14  发布在  其他
关注(0)|答案(1)|浏览(109)

下面的SSIS任务脚本应该将数据插入到三个表中，但是该脚本只能在本地工作，而不能在服务器上工作。我希望使用FireError（）/ FireInformation以便进行调试并找出原因。我的问题是我不知道如何为该脚本编写FireError。您能否提供一个小示例/提示或任何您想要的名称，以便将其应用到代码中。

#region Namespaces
using System;
using System.Data;
using System.Text;
using System.Net.Http;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Security.Cryptography;
using System.IO;
using System.IO.Compression;
using System.Web.Script.Serialization;
using SC_2e3723d7849249a59fd8f421bff5cab1;
using System.Collections.Generic;

using System.Linq;
using System.Threading.Tasks;
using System.Net;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    public override void PreExecute()
    {
        base.PreExecute();
        /*
         * Add your code here
         */
    }

    /// <summary>
    /// This method is called after all the rows have passed through this component.
    ///
    /// You can delete this method if you don't need to do anything here.
    /// </summary>
    public override void PostExecute()
    {
        base.PostExecute();
        /*
         * Add your code here
         */
    }

    public override void CreateNewOutputRows()
    {
        /*
          Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
          For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
        */

        ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;
        string path = "/jobs-sdk/jobs/job";
        string props = "customerAddrCity%2CcustomerAddrCountry%2CcustomerAddrLine1%2CcustomerAddrLine2%2CcustomerAddrPostalCode%2CcustomerAddrState%2CcustomerContact%2CcustomerName%2CcustomerPhoneNumber%2CdeviceId%2Cduplex%2CframeSizeX%2CframeSizeY%2ChpTrackingId%2Cimpressions%2Cimpressions1Color%2Cimpressions2Colors%2CimpressionsType%2Cinks%2CinkUnits%2CjdfJobId%2CjdfJobPartId%2CjobCompleteTime%2CjobCondition%2CjobCopies%2CjobElapseTime%2CjobId%2CjobLastEventTime%2CjobName%2CjobPriority%2CjobPriorityEnum%2CjobProgress%2CjobSubmitTime%2CjobSubstrate%2CjobType%2CjobWorkTimeEstimate%2ClastPrintedTime%2Clocation%2ClocationType%2Cmarker%2CparentDevId%2CparentJobId%2CqueueOrderIndex%2Cresolution%2Csubstrates%2CsubstrateUnits%2CticketTemplate";
        string baseurl = Convert.ToString(Variables.strHPBaseUrl);
        string startMarker = Convert.ToString(Variables.intMaxMarker);
        string secret = Convert.ToString(Variables.strHPSecret);
        string key = Convert.ToString(Variables.strHPKey);
        string limit = Convert.ToString(Variables.intLimit);
        int maxLoopIterations = Variables.intMaxLoopIterations;
        Boolean fireAgain = true;

        int counter;
        int LoopIteration = 0;
        string fullurl;
        string CurrentMaxMarker = startMarker;
        Boolean IsEndReached = false;

        do
        {
            counter = 0;
            LoopIteration++;
            #region InnerDo
            fullurl = baseurl + path + "?limit=" + limit + "&properties=" + props + "&startMarker=" + CurrentMaxMarker + "&direction=forward";
            ComponentMetaData.FireInformation(10, "CallwebApi", "Processing has started with Url: " + fullurl, "", 0, fireAgain);
            ComponentMetaData.FireInformation(10, "CallwebApi", "StartDoLoop with Iteration: " + LoopIteration, "", 0, fireAgain);

            using (var client = new HttpClient())
            {
                CreateHmacHeaders("GET", path, client, secret, key);

                try
                {
                    HttpResponseMessage response = client.GetAsync(fullurl).Result;
                    if (response.IsSuccessStatusCode)
                    {
                        Stream a = response.Content.ReadAsStreamAsync().Result;
                        //a = new GZipStream(a, CompressionMode.Decompress);
                        StreamReader Reader = new StreamReader(a, Encoding.Default);
                        string Html = Reader.ReadToEnd();
                        a.Close();
                        JavaScriptSerializer js = new JavaScriptSerializer();
                        List<JobContext> Jobs = new List<JobContext>();
                        Jobs = js.Deserialize<List<JobContext>>(Html);

                        //loops trough foreach (var j in Jobs) and set Attributes to ContextBuffer.AddRow();
                        foreach (var j in Jobs)
                        {
                            counter++;
                            #region Job
                            ContextBuffer.AddRow();
                            string JobId = "";
                            try
                            {
                                ContextBuffer.JobName = Convert.ToString(j.JobName);
                            }
                            catch (NullReferenceException)
                            {
                                ContextBuffer.JobName_IsNull = true;
                            }

                            try
                            {
                                ContextBuffer.DeviceId = Convert.ToString(j.DeviceId);
                            }
                            catch (NullReferenceException)
                            {
                                ContextBuffer.DeviceId_IsNull = true;
                            }

                            try
                            {
                                ContextBuffer.Duplex = Convert.ToString(j.Duplex);
                            }
                            catch (NullReferenceException)
                            {
                                ContextBuffer.Duplex_IsNull = true;
                            }

                            try
                            {
                                ContextBuffer.Impressions = Convert.ToInt32(j.Impressions);

                            }
                            catch (NullReferenceException)
                            {
                                ContextBuffer.Impressions_IsNull = true;
                            }

                            try
                            {
                                ContextBuffer.Impressions1Color = Convert.ToInt32(j.Impressions1Color);
                            }
                            catch (NullReferenceException)
                            {
                                ContextBuffer.Impressions1Color_IsNull = true;
                            }

                            try
                            {
                                ContextBuffer.Impressions2Colors = Convert.ToInt32(j.Impressions2Colors);
                            }
                            catch (NullReferenceException)
                            {
                                ContextBuffer.Impressions2Colors_IsNull = true;
                            }

                            try
                            {
                                ContextBuffer.ImpressionsType = Convert.ToString(j.ImpressionsType);
                            }
                            catch (NullReferenceException)
                            {
                                ContextBuffer.ImpressionsType_IsNull = true;
                            }

                            try
                            {
                                ContextBuffer.InkUnits = Convert.ToString(j.InkUnits);
                            }
                            catch (NullReferenceException)
                            {
                                ContextBuffer.InkUnits_IsNull = true;
                            }

                            try
                            {
                                ContextBuffer.JobCompleteTime = Convert.ToString(j.JobCompleteTime);
                            }
                            catch (NullReferenceException)
                            {
                                ContextBuffer.JobCompleteTime_IsNull = true;
                            }

                            try
                            {
                                ContextBuffer.JobCopies = Convert.ToInt32(j.JobCopies);
                            }
                            catch (NullReferenceException)
                            {
                                ContextBuffer.JobCopies_IsNull = true;
                            }

                            try
                            {
                                ContextBuffer.JobElapseTime = Convert.ToInt64(j.JobElapseTime);
                            }
                            catch (NullReferenceException)
                            {
                                ContextBuffer.JobElapseTime_IsNull = true;
                            }

                            try
                            {
                                ContextBuffer.JobId = Convert.ToString(j.JobId);
                                JobId = Convert.ToString(j.JobId);
                            }
                            catch (NullReferenceException)
                            {
                                ContextBuffer.JobId_IsNull = true;
                            }

                            try
                            {
                                ContextBuffer.JobLastEventTime = Convert.ToString(j.JobLastEventTime);
                            }
                            catch (NullReferenceException)
                            {
                                ContextBuffer.JobLastEventTime_IsNull = true;
                            }

                            try
                            {
                                ContextBuffer.JobProgress = Convert.ToString(j.JobProgress);
                            }
                            catch (NullReferenceException)
                            {
                                ContextBuffer.JobProgress_IsNull = true;
                            }

                            try
                            {
                                ContextBuffer.JobSubmitTime = Convert.ToString(j.JobSubmitTime);
                            }
                            catch (NullReferenceException)
                            {
                                ContextBuffer.JobSubmitTime_IsNull = true;
                            }


                            try
                            {
                                ContextBuffer.JobType = Convert.ToString(j.JobType);
                            }
                            catch (NullReferenceException)
                            {
                                ContextBuffer.JobType_IsNull = true;
                            }

                            try
                            {
                                ContextBuffer.Marker = Convert.ToInt64(j.Marker);
                                CurrentMaxMarker = Convert.ToString(j.Marker);
                            }
                            catch (NullReferenceException)
                            {
                                ContextBuffer.Marker_IsNull = true;
                            }

                            try
                            {
                                ContextBuffer.ParentJobId = Convert.ToString(j.ParentJobId);
                            }
                            catch (NullReferenceException)
                            {
                                ContextBuffer.ParentJobId_IsNull = true;
                            }


                            try
                            {
                                ContextBuffer.SubstrateUnits = Convert.ToString(j.SubstrateUnits);
                            }
                            catch (NullReferenceException)
                            {
                                ContextBuffer.SubstrateUnits_IsNull = true;
                            }

                            #endregion

                            #region Substrates
                            if (j.Substrates != null)
                            {
                                foreach (var i in j.Substrates.Counts)
                                {
                                    SubstratesBuffer.AddRow();
                                    try
                                    {
                                        SubstratesBuffer.Name = Convert.ToString(i.Name);
                                    }
                                    catch (NullReferenceException)
                                    {
                                        SubstratesBuffer.Name_IsNull = true;
                                    }

                                    try
                                    {
                                        SubstratesBuffer.AmountUsed = Convert.ToInt32(i.AmountUsed);
                                    }
                                    catch (NullReferenceException)
                                    {
                                        SubstratesBuffer.AmountUsed_IsNull = true;
                                    }

                                    try
                                    {
                                        SubstratesBuffer.JobId = JobId;
                                    }
                                    catch (NullReferenceException)
                                    {
                                        SubstratesBuffer.JobId_IsNull = true;
                                    }

                                }
                            }
                            #endregion

                            #region inks
                            if (j.Inks != null)
                            {
                                foreach (var i in j.Inks.Counts)
                                {
                                    InksBuffer.AddRow();
                                    try
                                    {
                                        InksBuffer.Name = Convert.ToString(i.Name);
                                    }
                                    catch (NullReferenceException)
                                    {
                                        InksBuffer.Name_IsNull = true;
                                    }

                                    try
                                    {
                                        InksBuffer.AmountUsed = Convert.ToInt32(i.AmountUsed);
                                    }
                                    catch (NullReferenceException)
                                    {
                                        InksBuffer.AmountUsed_IsNull = true;
                                    }

                                    try
                                    {
                                        InksBuffer.JobId = JobId;
                                    }
                                    catch (NullReferenceException)
                                    {
                                        InksBuffer.JobId_IsNull = true;
                                    }

                                }
                            }

                            #endregion

                        }

                    }
                    else //response.IsSuccessStatusCode
                    {
                        ErrorBuffer.AddRow();
                        ErrorBuffer.ErrorMessage = "Status code is unsuccessful";
                        ErrorBuffer.ErrorMessageStacktrace = SubstringStringByLength(response.ReasonPhrase, 4000);
                    }
                }
                catch (Exception e) // From make call to parse Objkect
                {
                    ErrorBuffer.AddRow();
                    ErrorBuffer.ErrorMessage = SubstringStringByLength(e.Message.ToString(), 950);
                    ErrorBuffer.ErrorMessageStacktrace = SubstringStringByLength(e.StackTrace.ToString(), 4000);
                }

            }
            #endregion

            if (LoopIteration >= maxLoopIterations)
            {
                IsEndReached = true;
            }

            if (counter <= 0)
            {
                IsEndReached = true;
            }
        } while (IsEndReached == false);

    }

    private static void CreateHmacHeaders(string method, string path, HttpClient client, string secret, string key)
    {
        string timeStamp = DateTime.UtcNow.ToString("yyyy-MM-ddTHH:mm:ssZ");
        string stringToSign = method + " " + path + timeStamp;
        HMACSHA256 hmac = new HMACSHA256(Encoding.UTF8.GetBytes(secret));
        byte[] bytes = hmac.ComputeHash(Encoding.UTF8.GetBytes(stringToSign));
        string signature = BitConverter.ToString(bytes).Replace("-", string.Empty).ToLower();
        string auth = key + ":" + signature;

        client.DefaultRequestHeaders.Add("x-hp-hmac-authentication", auth);
        client.DefaultRequestHeaders.Add("x-hp-hmac-date", timeStamp);
        client.DefaultRequestHeaders.Add("x-hp-hmac-algorithm", "SHA256");
    }

    
    private string SubstringStringByLength(string LongString, int maxLength)
    {
        int maxLengthInoutString = (LongString.Length > maxLength ? maxLength : LongString.Length);
        String OutputString = (LongString != null)
                 ? LongString.Substring(0, maxLengthInoutString)
                 : "";

        return OutputString;
    }



}
debugging

1条答案

按热度按时间
mkshixfv

mkshixfv1#

联机丛书提供

https://stackoverflow.com/a/28907522/181965的范例程式码，示范指令码工作与指令码元件（您所使用的）方法签章之间的差异

bool fireAgain = false;
    this.Dts.Events.FireInformation(0, "my sub", "info", string.Empty, 0, ref fireAgain);
    // Note, no cancel available
    this.Dts.Events.FireError(0, "my sub", "error", string.Empty, 0);

至于在现有代码中的什么位置插入这个代码，我不知道您的设计目标是什么，但我认为它适合您的ErrorBuffer调用--尽管FireError会使处理速度变快，所以也许不会？也许您希望将所有坏行累积到一个全局变量中，并在PostEvent部分中枚举它们，以便您可以看到所有坏行。/shrug

赞(0）分享  回复(0）举报  2022-11-14
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com