DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • What Are SpeedUp and ScaleUp in DBMS?
  • Comparing SQL and SPL: Order-Based Computations
  • Apache Spark for the Impatient
  • Data Privacy and Security: A Developer's Guide to Handling Sensitive Data With DuckDB

Trending

  • Performing and Managing Incremental Backups Using pg_basebackup in PostgreSQL 17
  • MySQL to PostgreSQL Database Migration: A Practical Case Study
  • Beyond ChatGPT, AI Reasoning 2.0: Engineering AI Models With Human-Like Reasoning
  • Why High-Performance AI/ML Is Essential in Modern Cybersecurity
  1. DZone
  2. Data Engineering
  3. Databases
  4. SSIS Data Flow Error Handling With Description and Column Name

SSIS Data Flow Error Handling With Description and Column Name

Without SQL 2016's modified API, it's really difficult to find the error column name using just a script task and script component. Here's a simple solution.

By 
Joydeep Das user avatar
Joydeep Das
·
Jan. 02, 18 · Tutorial
Likes (2)
Comment
Save
Tweet
Share
21.5K Views

Join the DZone community and get the full member experience.

Join For Free

Finding an error description is a simple task involving one line of code — but finding the error column name is not so simple. Microsoft made it easy with SQL 2016's modified API. But with prior versions, it's a really difficult job to find the error column name using just a script task and script component.

This article provides a simple step-by-step solution to find the error description and error column name.

Scenario

We are trying to insert records from a flat file to our SQL table. The flat file has two columns: roll and name. Sometimes, the name column contains huge text and isn't supported by our table object (the size of the name column the in table object is Varchar(50)). This generates an error. We are trying to display the error description and the column name that generated the error.

Control/Data Flow Details

Image title

Control Flow: Get Lineage ID Task

This script task is used to grade the lineage ID at runtime and save it as a text file.

  1. Create a text file: C:\temp\write.txt.

  2. Create two package-level variables named User::execsObj and User::lineageIds as the object types. Pass them as the Read-Write variables of the script task.

  3. In the script task, add an assembly named Microsoft.SqlServer.DTSPipelineWrap: 

    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#:

    {
     // 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 a Script Component to the Data Flow Task

Now, add a script component to the data flow task.

  1. Pass the variable named User::lineageIds as the Read variable.

  2. Script the component input columns as ErrorCode and ErrorColumn.

  3. Script the component output column as ErrorDescription and ErrorColumnDescription with a DT_STR data type.

    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
    using Microsoft.SqlServer.Dts.Runtime.Wrapper;
    using System.Collections.Generic;
    Script task 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);
         }

I hope you found this article informative!

Database sql Flow (web browser) Data (computing) Task (computing)

Published at DZone with permission of Joydeep Das, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • What Are SpeedUp and ScaleUp in DBMS?
  • Comparing SQL and SPL: Order-Based Computations
  • Apache Spark for the Impatient
  • Data Privacy and Security: A Developer's Guide to Handling Sensitive Data With DuckDB

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!