Friday 29 December 2017

SSIS Data Flow Error Handling with Description and Column Name

Introduction
Finding the Error Description is a simple one line code but finding Error Column name is not so much simple. Microsoft made it easy from SQL 2016, when modified API comes. But before 2016 it’s really a difficult job to find the Error Column Name by using Script Task and Script Component.
This article is trying to provide a simple step by step solution to find the Error Description and Error column name.

Scenario
We are trying to insert records from Flat file to our SQL Table. The flat file has two columns named “roll” and “name”. Sometimes name contains huge text and don’t support by our Table object (Size of the name column in table object is Varchar(50)) and hence generate Error. We are trying to display Error description and the column name that generate the Error.


Control/Data Flow Details



Control Flow – Get Linage ID Task
This Script Task is used to grade the Linage id at run time and save it in a text file.

What to do:
1.    Create a text file C:\temp\write.txt
2.    Create two package level variable named User::execsObj and User::lineageIds as Object type and pass them as Read Write variable of script task.
3.    In Script Task add assembly named Microsoft.SqlServer.DTSPipelineWrap

Namespace needed
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using System.Windows.Forms;
using System.Collections.Generic;
using System.IO;


Script Task Code in C#

public void Main()
{
 // Grab the executables so we have to something to iterate over, and initialize our lineageIDs list
// Why the executables?  Well, SSIS won't let us store a reference to the Package itself...
Dts.Variables["User::execsObj"].Value =(Package)Dts.Variables["User::execsObj"].Parent).Executables;

Dts.Variables["User::lineageIds"].Value = new Dictionary<int, string>();
lineageIds = (Dictionary<int, string>)Dts.Variables["User::lineageIds"].Value;
Executables execs = (Executables)Dts.Variables["User::execsObj"].Value;

ReadExecutables(execs);

// Just proof of concept to see the results before you dedicate your time to the solution
// Delete this code in your actual implementation
using (StreamWriter writetext = new StreamWriter(@"C:\temp\write.txt", true))
        {
                foreach (var kvp in lineageIds)
                writetext.WriteLine(kvp.Key + " : " + kvp.Value);
        }
            Dts.TaskResult = (int)ScriptResults.Success;
}

        

private void ReadExecutables(Executables executables)
{
  foreach (Executable pkgExecutable in executables)
    {
     if (object.ReferenceEquals(pkgExecutable.GetType(),            
                 typeof(Microsoft.SqlServer.Dts.Runtime.TaskHost)))
                {
                    TaskHost pkgExecTaskHost = (TaskHost)pkgExecutable;
                    if (pkgExecTaskHost.CreationName.StartsWith("SSIS.Pipeline"))
                    {
                        ProcessDataFlowTask(pkgExecTaskHost);
                    }
                }
     else if (object.ReferenceEquals(pkgExecutable.GetType(), 
                 typeof(Microsoft.SqlServer.Dts.Runtime.ForEachLoop)))
                {
                    // Recurse into FELCs
                    ReadExecutables(((ForEachLoop)pkgExecutable).Executables);
                }
      }
 }

private void ProcessDataFlowTask(TaskHost currentDataFlowTask)
{
   MainPipe currentDataFlow = (MainPipe)currentDataFlowTask.InnerObject;

   foreach (IDTSComponentMetaData100 currentComponent in 
                  currentDataFlow.ComponentMetaDataCollection)
            {
              // Get the inputs in the component.
              foreach (IDTSInput100 currentInput in currentComponent.InputCollection)
                  foreach (IDTSInputColumn100 currentInputColumn in 
                          currentInput.InputColumnCollection)
                        lineageIds.Add(currentInputColumn.ID, currentInputColumn.Name);

               // Get the outputs in the component.
               foreach (IDTSOutput100 currentOutput in currentComponent.OutputCollection)
                    foreach (IDTSOutputColumn100 currentoutputColumn in 
                                currentOutput.OutputColumnCollection)
                        lineageIds.Add(currentoutputColumn.ID, currentoutputColumn.Name);
            }
}


Adding Script Component in Data Flow Task
Now add script component in data flow task.


What to do:
1.    Just pass the variable named User::lineageIds as Read variable
2.    Script Component input columns as ErrorCode and ErrorColumn
3.    Script Component output column as ErrorDescription and ErrorColumnDescription with DT_STR data type.

Name space
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Collections.Generic;

Script Component Code in C#
public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        Dictionary<int, string> lineageIds = (Dictionary<int, string>)Variables.lineageIds;

        int? colNum = Row.ErrorColumn;
        if (colNum.HasValue && (lineageIds != null))
        {
            if (lineageIds.ContainsKey(colNum.Value))
                Row.ErrorColumnDescription = lineageIds[colNum.Value];

            else
                Row.ErrorColumnDescription = "Row error";
        }
        Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);
     }









Hope you find it informative.

Posted by: MR. JOYDEEP DAS

54 comments:

  1. I really enjoyed while reading your article, the information you have mentioned in this post was damn good.
    SQL Database Training | MS SQL Corporate Training

    ReplyDelete
  2. I have seen your info on ssis data flow error handling.. Especially on Adding Script Component in Data Flow Task. The info you provided is very useful for any interested on ssis. I am very much satisfied with your info. I wish I can get furthered info from you.I am very glad to say thanks for this info.
    The Script Component provides another area where programming logic can be applied in an SSIS package. This component, which can be used only in the Data Flow portion of an SSIS package, allows programmatic tasks to occur in the data stream.

    ReplyDelete
  3. has made payroll management quite definitely easier for accounting professionals. There are so many QuickBooks Payroll Support Phone Number who are giving positive feedback when they process

    ReplyDelete
  4. This practice helps them produce you the specified wind up in the given time window. QuickBooks Support Phone Number is there to assist you 24*7 as we try not to disassociate ourselves together with your troubles even through the wee hours.

    ReplyDelete
  5. Quickbooks Payroll Support Phone Number provides 24/7 make it possible to our customer. Only you must do is make a person call at our toll-free QuickBooks Payroll tech support number . You could get resolve most of the major issues include installations problem, data access issue, printing related issue, software setup, server not responding error etc with this QuickBooks payroll support team.

    ReplyDelete
  6. QuickBooks Enterprise Support Phone Number has almost eliminated the typical accounting process. Along with a wide range of tools and automations, it provides a wide range of industry verticals

    ReplyDelete
  7. Quickbooks Support And Decision Making Will you be facing the matter with decision making? The actual QuickBooks Support Phone Number is it possible to earn in per month

    ReplyDelete
  8. We offers you QuickBooks Support Phone Number Team. Our technicians make sure to the security of the vital business documents. We now have a propensity to never compromise using the safety of one's customers.

    ReplyDelete
  9. QuickBooks is available for users around the world whilst the best tool to provide creative and innovative features for business account management to small and medium-sized business organizations. If you’re encountering any kind of QuickBooks’ related problem, you could get all that problems solved just by making use of the Quickbooks Support Phone Number.

    ReplyDelete
  10. The QuickBooks Enterprise support Number data files are incredibly responsive to unexpected disconnections as well as other interruptions. In result they get corrupted easily and hamper your workflow easily.

    ReplyDelete
  11. At Support for QuickBooks 2019, you will discover solution each and every issue that bothers your projects and creates hindrance in running your company smoothly. Our team is oftentimes willing to allow you to when using the best support services you could feasibly ever experience.

    ReplyDelete
  12. Therefore we have designed a especially dedicated team of certified professionals at QuickBooks Customer Support Number which are in a position to understanding your issues and errors in minimum time as well as in one of the most convenient way.

    ReplyDelete
  13. In addition to it, our QuickBooks Customer Support Number will bring in dedicated and diligent back-end helps for your needs for if you find any inconveniences in operating any of these versions.

    ReplyDelete
  14. There are so many individuals who are giving positive feedback when they process payroll either QB desktop and online options. In this web site, we are going to enable you to experience to make and place up the checklist for employee payment. To get more enhanced results and optimized benefits, you are able to take the assistance of experts making a call at Quickbooks Support Number.

    ReplyDelete
  15. It is possible to stick to the above mentioned steps carefully to get rid of this login issue. However, this is the wisest choice to call at 247 toll-free quantity of QuickBooks to get in touch with one of our technical experts at QuickBooks Tech Support Number for a fast resolution of every issues in QBO.

    ReplyDelete
  16. Quickbooks Support For Business All of the above has a particular use. People working with accounts, transaction, banking transaction need our service. Some people are employing excel sheets for a few calculations. QuickBooks Technical Support Number calculate accurately the figures.

    ReplyDelete
  17. There are lots of payroll options made available because of the online type of QuickBooks varying upon the necessity of accounting professionals and subscription plans. Quickbooks Support Number as well provides all possible help with the users to utilize it optimally. A person who keeps experience of experts has the capacity to realize in regards to the latest updates.

    ReplyDelete
  18. QuickBooks Enterprise Support channel available on a call at .You can quickly avail our other beneficial technical support services easily once we are merely a single call QucikBooks Enterprise Support Phone Number

    ReplyDelete
  19. QuickBooks Payroll is an application which includes made payroll a simple snap-of-fingers task. You'll be able to quite easily and automatically calculate the tax for your employees. It is an absolute software that fits your organization completely. We provide Quickbooks Payroll tech support team in terms of customers who find QuickBooks Payroll hard to use. As Quickbooks Payroll customer care we make use of the responsibility of resolving all of the issues that hinder the performance regarding the exuberant software. There is certainly sometimes a number of errors which may bother your projects flow, nothing should be taken as burden that being said because the support team of QuickBooks Payroll Technical Support Number resolves every issue in minimal time and commendable expertise.

    ReplyDelete
  20. Our customer service executives have significant amounts of experience and so are sharp along with smart in finding out the particular cause and optimal solution each and every error that you could face. Contact us anytime for our QuickBooks Enterprise Tech Support Number even as we are available 24*7for your help.

    ReplyDelete
  21. Our Intuit QuickBooks Support Number provide you with remote assistance to solve your concern regarding QuickBooks at home. We offer hassle-free remote services to Intuit QuickBooks users and provide these with a far better experience. We also give attention to quality QuickBooks Tech Support for QuickBooks users. Go ahead and join us on our QuickBooks chat support or call to resolve your issues.

    ReplyDelete
  22. Our QuickBooks enhanced payroll support will help you to stand in an aggressive market. Intuit Payroll on the internet is among the advanced competitive accounting software. Being occupied with several advanced functions, in the event that you will contact QuickBooks support to run this impressive software, you can smoothly increase your business with no technical issue. To obtain the QuickBooks support, it is possible to contact our QuickBooks Payroll Tech Support Number.

    ReplyDelete
  23. QuickBooks Payroll Support Phone Number and Payroll for Desktop may be the two major versions and they're further bifurcated into sub versions. Enhanced Payroll and Full-service payroll are encompassed in Online Payroll whereas Basic, Enhanced and Assisted Payroll come under Payroll for Desktop.

    ReplyDelete
  24. How to contact QuickBooks Payroll support?
    Different styles of queries or QuickBooks related issue, then you're way in the right direction. You simply give single ring at our toll-free intuit Phone Number for QuickBooks Payroll Supportt . we are going to help you right solution according to your issue. We work on the internet and can get rid of the technical problems via remote access not only is it soon seeing that problem occurs we shall fix the same.

    ReplyDelete
  25. Our QuickBooks Tech Support Number experts listen to queries of the customers or entrepreneurs very carefully or analyze that problem and fix them with a real or relevant solution.

    ReplyDelete
  26. There might be occasions once you might face some type of delay in reaching us, let’s say at that time of filing taxes since there is lots of hush-hush then. We assure you that individuals will revert to your account in less time and work out us available to you at QuickBooks Tech Support Number.

    ReplyDelete
  27. In case this product is acting oddly on the framework, when this occurs you should call a standout amongst the best QuickBooks Tech Support Number services in USA.

    ReplyDelete
  28. QuickBooks Support Number is assisted by our customer support specialists who answr fully your call instantly and resolve all of your issues at that moment. It is a backing portal that authenticates the users of QuickBooks to perform its services in a user-friendly manner.

    ReplyDelete
  29. And so, it is important for you yourself to take some quick assistance from the professionals. However, if you want are seeking HP Printer troubleshooting, don’t forget to dial the HP Printer Support Number.

    ReplyDelete
  30. Yes, HP Inkjet Printer Support Number are durable and strong, but there are occasions once the user faces an HP printer, not printing problems. The printer produces printouts, which are either blank or not aligned.

    ReplyDelete
  31. For such type of information, be always in contact with us through our blogs. To find the reliable supply of help to create customer checklist in QB desktop, QuickBooks online and intuit online payroll? Our QuickBooks Payroll Tech Support Number service might help you better.

    ReplyDelete
  32. Very often client faces some typically common issues like he/she isn’t willing to open QuickBooks package, it is playing terribly slow, struggling to install and re-install, a challenge in printing checks or client reports. We intend to supply you with the immediate support by our well- masterly technicians. A group of QuickBooks Technical Support Phone Number professionals is invariably accessible for you personally so as to arranged every one of your problems in an effort that you’ll be able to do your work while not hampering the productivity.

    ReplyDelete
  33. You need to use QuickBooks to generate any selection of reports you would like, keeping entries for many sales, banking transactions and a lot of additional. QuickBooks Tech Support provides a myriad of options and support services for an equivalent. it is commonplace to face any errors on your QuickBooks if you should be doing not stick to the syntax, if the code isn’t put in properly or if you’re having any corruption within the information of the QuickBooks.

    ReplyDelete
  34. All of us offers a total financial solution for your account. So just call us on our QuickBooks error support telephone number and sharing your all style of accounting worries with us, we are going to definitely assist you to with our best QuickBooks Support Number updates in addition to solve your all QuickBooks errors.

    ReplyDelete
  35. In certain updates and new introductions, QuickBooks keeps enhancing the buyer experience by offering them more facilities than before. Payroll is amongst the important the various components of accounting, therefore the QuickBooks leaves no stone unturned in making it more & more easier for users. There are numerous payroll options made available due to the online kind of QuickBooks varying upon the need of accounting professionals and subscription plans. QuickBooks Payroll Technical Support as well provides all possible help with the users to utilize it optimally. An individual who keeps connection with experts is able to realize about the latest updates.

    ReplyDelete

  36. QuickBooks Tech Support Phone Number Premier is an accounting software that includes helped you grow your business smoothly. It provides some luring features which make this software most desirable.

    ReplyDelete
  37. Causing errors in the books of records of small businesses is a costly affair and can even end up the whole business the END. So, ensure that the online accounting software that you plan to buy has good experience in handling books of records of businesses. Contact their past clients and check their size of businesses the service provider has managed till now. Easily dial our QuickBooks Support Phone Number and get connected with our wonderful technical team. In such a way, you can get a clear idea of the amount of experience and expertise the bookkeeper should have to meet your requirements.

    ReplyDelete
  38. Most support teams provide you with tech teams online who have studied the QuickBooks Enterprise Tech Support Phone Number software. With our support circle, we go the extra mile. We get you in touch with experts who have not only used the software for a prolonged period of time. But, have also gained extensive knowledge about all the practical uses and figured out the nuances. Nuances that an average user might not possess the intel about.

    ReplyDelete
  39. Are you looking for help regarding depositing the fund in Binance? If your answer is yes, then why to take a chance, itself, just dial our Binance phone number to have the required conversation for your issues that you are facing. We have a well-organized team member who works round the clock. You never need to wait anymore to call us, as we are present here all the time to provide you an efficient solution after detection of the issues Binance Support Number

    ReplyDelete
  40. If you one of those who is constantly searching the solution to issues associated with withdrawing from Blockchain then you may immediately contact us calling our Blockchain support number which is available 24*7 hours. You may contact us anytime on the spot whenever you the help for withdrawing the fund. Science withdrawal process encompasses certain steps and that need to be followed in a systematized way. Therefore they might have o face problem while carrying out this process as all users are not competent. In that case, it is better for them to ask for help consulting the support team. Blockchain Support Number

    ReplyDelete
  41. Do you want to have an open support ticket for Gemini? Are you literally searching for it? If you say yes then call our Gemini support number where you will be able to talk to our expert team without any problem. They are good at providing the solution and also they provide the guideline that actually requires for sorting out the problems. Hence we simply advise our viewers that don’t make delay in contacting us when they need any help for Gemini exchange. Gemini Support Number

    ReplyDelete
  42. Do you want to overcome the price manipulation issue for Ripple? If yes, take the stepwise assistance from the experts who have been working in Ripple since years and know now all the solutions and inventive technological oriented remedies to overcome from any error. Just ping on Ripple support number and get instant and ultimate solutions from the experts. The customer care are available 24/7 to assist the users irrespective of time and feel delighted to help them at any point of time. Ripple Support Number

    ReplyDelete
  43. Are you willing to generate new BTC address in Binance exchange account but having issues then you may quickly contact our support team calling Binance support number that has been made available especially for those users who keep searching for the solution that needs to be applied as soon as possible? Issues related to such activities are quite common but it may easily be resolved to take the help of connected techie. They can fix your issue in short-span. Binance Customer Support

    ReplyDelete
  44. Is two-factor authentication creating trouble and you are looking for ways to get rid of this error as soon as possible? Blockchain 2fa protects the account but it has stopped working, then, it is a matter of a big issue and need immediate solutions from the experienced to deal. If you are having trouble in dealing with this error and looking for solution to handle such problems, you can always ask for help from the team of elite professionals who are there to guide you. You can call on Blockchain toll-free number which is working and users from anywhere across the world can contact and discuss their queries with the professionals in no time. Blockchain customer service +1 (833) 993-0690
    Blockchain Support Number
    Blockchain Support Phone Number
    Blockchain Phone Number

    ReplyDelete
  45. Error technically means an estimated difference between the calculated value of a quantity and its true value. The numeric value, here, 9999, is the value to identify the error. It holds information, cause, as well as the action causing the error. QuickBooks Error 9999 may encounter the user while searching online. It hangs, responds slower or even stops working. When trying updating the information, the users get entangled in error.

    ReplyDelete
  46. Quickbooks - QuickBooks is popular small to medium-sized business accounting software created by Intuit, Inc QuickBooks ProPremierEnterprise for Windows all include support for programmatic access to QuickBooks via Intuit's SDKXML-based APIs. If you would like to learn how to Fix Quickbooks Error 9999 yourself, you can continue reading this blog.

    ReplyDelete
  47. Thank you so much for providing information about SSIS and data flow tasks that really contribute the best.

    SSIS Postgresql Write

    ReplyDelete
  48. This post is really very helpful article to resolve:
    Fix QuickBooks Error with the use of QuickBooks Tool Hub
    Contact Us Email: info@mamfishsaves.com Phone No: 1-844-734-9204

    ReplyDelete
  49. Very nice post .SSIS Data Flow Error Handling with Description and Column Name, i also read how to Control Flow & Data Flow in SSIS?

    ReplyDelete
  50. I'm a Salesforce Trailhead enthusiast, and I can vouch for the value of the online training and resources Salesforce provides. Their modules and trails are an excellent complement to formal training. I encourage everyone to explore Trailhead and earn badges on the platform while pursuing their certifications. It's a fun way to learn! Please visit our website:- online salesforce training

    ReplyDelete