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

38 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. Every user are certain to get 24/7 support services with our online technical experts using QuickBooks Support Phone Number. When you’re stuck in times that you can’t discover ways to eradicate an issue, all that's necessary would be to dial QuickBooks Support. Have patience; they're going to inevitably and instantly solve your queries.

    ReplyDelete
  7. 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
  8. 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
  9. 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
  10. 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
  11. 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
  12. 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
  13. 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
  14. 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
  15. 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
  16. 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
  17. 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
  18. 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
  19. Due to the enterprise version, you obtain the chance to scale from 1 to 30 users to operate simultaneously.In addition to this, you can easily keep access, control & client permissions connected with your company requirement.Additionally, to avail data protection and recovery services, just dial QuickBooks Enterprise Support.

    ReplyDelete
  20. The difference that we make amongst our competitors is that our services can be obtained 24*7. But we now have made certain that our services are there not only for namesake. We actually deliver upper end, quality QuickBooks Enterprise Support Phone Number team services to all our customers.

    ReplyDelete
  21. 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
  22. 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
  23. 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
  24. 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
  25. If the problem persists, contact Intuit Technical Support and offer all of them with the next error codes: (QuickBooks Error 6000-301). Click on the Details button to find out more to produce Intuit Technical Support to greatly help diagnose the error.

    ReplyDelete
  26. 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
  27. 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
  28. 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
  29. 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
  30. For the rectification for the issue call QuickBook Support is can help the Quickbooks users are right people to pin point and fix the issue completely. They assure resolution within the minimum wait time that saves you time.

    ReplyDelete
  31. 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
  32. 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
  33. 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
  34. 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
  35. 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