none
sysssislog nice OnError message column name and row number RRS feed

  • Question

  • Hi,

    I am currently logging error from my flat file source to standard sysssislog table.

    The logging is very nice. It includes the column name and row number on which the error occurred, for example An error occurred while processing file "c:\test1.csv" on data row 3. 

    Am I able to log the same information when using row redirect?

    Thanks,

    Ben


    Mr Shaw... One day I might know a thing or two about SQL Server!

    Tuesday, November 8, 2016 4:39 PM

Answers

  • Hi Shaw,

    Not exactly know how sissislog does that, however we can get the same information within the script component as well. Check the source flat file, in SSIS component output,  the column length of name and address are 5 and 8, obvious in this flat file, the name22 and address33 exceeding the length limitation.


    #region Help:  Introduction to the Script Component
    /* The Script Component allows you to perform virtually any operation that can be accomplished in
     * a .Net application within the context of an Integration Services data flow.
     *
     * Expand the other regions which have "Help" prefixes for examples of specific ways to use
     * Integration Services features within this script component. */
    #endregion
    
    #region Namespaces
    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
    using Microsoft.SqlServer.Dts.Runtime.Wrapper;
    using System.IO;
    using Microsoft.SqlServer.Dts.Pipeline;
    using System.Reflection;
    #endregion
    
    /// <summary>
    /// This is the class to which to add your code.  Do not change the name, attributes, or parent
    /// of this class.
    /// </summary>
    [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
    public class ScriptMain : UserComponent
    {
        #region Help:  Using Integration Services variables and parameters
        /* To use a variable in this script, first ensure that the variable has been added to
         * either the list contained in the ReadOnlyVariables property or the list contained in
         * the ReadWriteVariables property of this script component, according to whether or not your
         * code needs to write into the variable.  To do so, save this script, close this instance of
         * Visual Studio, and update the ReadOnlyVariables and ReadWriteVariables properties in the
         * Script Transformation Editor window.
         * To use a parameter in this script, follow the same steps. Parameters are always read-only.
         *
         * Example of reading from a variable or parameter:
         *  DateTime startTime = Variables.MyStartTime;
         *
         * Example of writing to a variable:
         *  Variables.myStringVariable = "new value";
         */
        #endregion
    
        #region Help:  Using Integration Services Connnection Managers
        /* Some types of connection managers can be used in this script component.  See the help topic
         * "Working with Connection Managers Programatically" for details.
         *
         * To use a connection manager in this script, first ensure that the connection manager has
         * been added to either the list of connection managers on the Connection Managers page of the
         * script component editor.  To add the connection manager, save this script, close this instance of
         * Visual Studio, and add the Connection Manager to the list.
         *
         * If the component needs to hold a connection open while processing rows, override the
         * AcquireConnections and ReleaseConnections methods.
         * 
         * Example of using an ADO.Net connection manager to acquire a SqlConnection:
         *  object rawConnection = Connections.SalesDB.AcquireConnection(transaction);
         *  SqlConnection salesDBConn = (SqlConnection)rawConnection;
         *
         * Example of using a File connection manager to acquire a file path:
         *  object rawConnection = Connections.Prices_zip.AcquireConnection(transaction);
         *  string filePath = (string)rawConnection;
         *
         * Example of releasing a connection manager:
         *  Connections.SalesDB.ReleaseConnection(rawConnection);
         */
        #endregion
    
        #region Help:  Firing Integration Services Events
        /* This script component can fire events.
         *
         * Example of firing an error event:
         *  ComponentMetaData.FireError(10, "Process Values", "Bad value", "", 0, out cancel);
         *
         * Example of firing an information event:
         *  ComponentMetaData.FireInformation(10, "Process Values", "Processing has started", "", 0, fireAgain);
         *
         * Example of firing a warning event:
         *  ComponentMetaData.FireWarning(10, "Process Values", "No rows were received", "", 0);
         */
        #endregion
    
        /// <summary>
        /// This method is called once, before rows begin to be processed in the data flow.
        ///
        /// You can remove this method if you don't need to do anything here.
        /// </summary>
        /// 
    
        private StreamReader textReader;
        private string exportedAddressFile;
        private int rowNumber = 0;
    
    
    
        public override void AcquireConnections(object Transaction)
        {
    
            IDTSConnectionManager100 connMgr = this.Connections.MyFlatFileConn;
            exportedAddressFile = (string)connMgr.AcquireConnection(null);
    
        }
        public override void PreExecute()
        {
            base.PreExecute();
            textReader = new StreamReader(exportedAddressFile);
            /*
             * 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();
            textReader.Close();
            /*
             * 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".
            */
            string nextLine;
            string[] columns;
    
            char[] delimiters;
            delimiters = ",".ToCharArray();
             
    
            nextLine = textReader.ReadLine();
            while (nextLine != null)
            {
                rowNumber = rowNumber+1;
    
                string columnName = "";
    
                columns = nextLine.Split(delimiters);
    
                try
                {
                    Output0Buffer.AddRow();
                    columnName = "name";
                    Output0Buffer.name = columns[0];
                    columnName = "address";
                    Output0Buffer.address = columns[1];
                }
    
                catch (Exception e) {
                    RemoveRow(Output0Buffer); 
    
                    ErrorOutputBuffer.AddRow();
                    ErrorOutputBuffer.errmsg = String.Format("truncations happens in row {0},on column {1}", rowNumber, columnName);              
    
                }
    
    
    
                nextLine = textReader.ReadLine();
            }
        }
    
        public void RemoveRow(ScriptBuffer scriptBuffer)
        {
            BindingFlags bindFlags = BindingFlags.Instance | BindingFlags.NonPublic;
            FieldInfo field = scriptBuffer.GetType().GetField("Buffer", bindFlags);
            ((PipelineBuffer)field.GetValue(scriptBuffer)).RemoveRow();
        }
    
    }
    

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Mr Shaw Saturday, November 19, 2016 7:07 PM
    Thursday, November 10, 2016 2:48 AM
    Moderator

All replies

  • If you re-direct, it's no long considered an error with the expectation your code will deal with whatever caused it to be invalid
    Tuesday, November 8, 2016 7:20 PM
  • Hi Ben,

    The logged row No. helps to locate the problematic rows in the flat file source while the row redirect already separates the ones. so I'm not quite getting your idea.

    Maybe some trick to get the row No. with a script component, check HOW TO ADD A ROWNUMBER TO A DATA FLOW TASK IN SSIS 2012. In that link, an extra row NO column is added to the output and when redirecting, you could see the row NO.
     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 9, 2016 3:06 AM
    Moderator
  • Hi Ben,

    The logged row No. helps to locate the problematic rows in the flat file source while the row redirect already separates the ones. so I'm not quite getting your idea.

    Maybe some trick to get the row No. with a script component, check HOW TO ADD A ROWNUMBER TO A DATA FLOW TASK IN SSIS 2012. In that link, an extra row NO column is added to the output and when redirecting, you could see the row NO.
     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Hi,

    Here is the scenario.

    Create a flat file connection to read a csv file.

    Add standard OnError logging.

    Run the package with an error in one of the lines of the csv.

    The sysssislog table will now include a row telling you which column and csv row number the error occurred.

    It is fantastic. With row direct I just cannot get the same type of data.


    Mr Shaw... One day I might know a thing or two about SQL Server!

    Wednesday, November 9, 2016 9:27 AM
  • I know how to get row numbers..... what i am focusing on is getting the error column name. sysssislog captures it nicely.

    Mr Shaw... One day I might know a thing or two about SQL Server!

    Wednesday, November 9, 2016 9:16 PM
  • Hi Shaw,

    Not exactly know how sissislog does that, however we can get the same information within the script component as well. Check the source flat file, in SSIS component output,  the column length of name and address are 5 and 8, obvious in this flat file, the name22 and address33 exceeding the length limitation.


    #region Help:  Introduction to the Script Component
    /* The Script Component allows you to perform virtually any operation that can be accomplished in
     * a .Net application within the context of an Integration Services data flow.
     *
     * Expand the other regions which have "Help" prefixes for examples of specific ways to use
     * Integration Services features within this script component. */
    #endregion
    
    #region Namespaces
    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
    using Microsoft.SqlServer.Dts.Runtime.Wrapper;
    using System.IO;
    using Microsoft.SqlServer.Dts.Pipeline;
    using System.Reflection;
    #endregion
    
    /// <summary>
    /// This is the class to which to add your code.  Do not change the name, attributes, or parent
    /// of this class.
    /// </summary>
    [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
    public class ScriptMain : UserComponent
    {
        #region Help:  Using Integration Services variables and parameters
        /* To use a variable in this script, first ensure that the variable has been added to
         * either the list contained in the ReadOnlyVariables property or the list contained in
         * the ReadWriteVariables property of this script component, according to whether or not your
         * code needs to write into the variable.  To do so, save this script, close this instance of
         * Visual Studio, and update the ReadOnlyVariables and ReadWriteVariables properties in the
         * Script Transformation Editor window.
         * To use a parameter in this script, follow the same steps. Parameters are always read-only.
         *
         * Example of reading from a variable or parameter:
         *  DateTime startTime = Variables.MyStartTime;
         *
         * Example of writing to a variable:
         *  Variables.myStringVariable = "new value";
         */
        #endregion
    
        #region Help:  Using Integration Services Connnection Managers
        /* Some types of connection managers can be used in this script component.  See the help topic
         * "Working with Connection Managers Programatically" for details.
         *
         * To use a connection manager in this script, first ensure that the connection manager has
         * been added to either the list of connection managers on the Connection Managers page of the
         * script component editor.  To add the connection manager, save this script, close this instance of
         * Visual Studio, and add the Connection Manager to the list.
         *
         * If the component needs to hold a connection open while processing rows, override the
         * AcquireConnections and ReleaseConnections methods.
         * 
         * Example of using an ADO.Net connection manager to acquire a SqlConnection:
         *  object rawConnection = Connections.SalesDB.AcquireConnection(transaction);
         *  SqlConnection salesDBConn = (SqlConnection)rawConnection;
         *
         * Example of using a File connection manager to acquire a file path:
         *  object rawConnection = Connections.Prices_zip.AcquireConnection(transaction);
         *  string filePath = (string)rawConnection;
         *
         * Example of releasing a connection manager:
         *  Connections.SalesDB.ReleaseConnection(rawConnection);
         */
        #endregion
    
        #region Help:  Firing Integration Services Events
        /* This script component can fire events.
         *
         * Example of firing an error event:
         *  ComponentMetaData.FireError(10, "Process Values", "Bad value", "", 0, out cancel);
         *
         * Example of firing an information event:
         *  ComponentMetaData.FireInformation(10, "Process Values", "Processing has started", "", 0, fireAgain);
         *
         * Example of firing a warning event:
         *  ComponentMetaData.FireWarning(10, "Process Values", "No rows were received", "", 0);
         */
        #endregion
    
        /// <summary>
        /// This method is called once, before rows begin to be processed in the data flow.
        ///
        /// You can remove this method if you don't need to do anything here.
        /// </summary>
        /// 
    
        private StreamReader textReader;
        private string exportedAddressFile;
        private int rowNumber = 0;
    
    
    
        public override void AcquireConnections(object Transaction)
        {
    
            IDTSConnectionManager100 connMgr = this.Connections.MyFlatFileConn;
            exportedAddressFile = (string)connMgr.AcquireConnection(null);
    
        }
        public override void PreExecute()
        {
            base.PreExecute();
            textReader = new StreamReader(exportedAddressFile);
            /*
             * 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();
            textReader.Close();
            /*
             * 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".
            */
            string nextLine;
            string[] columns;
    
            char[] delimiters;
            delimiters = ",".ToCharArray();
             
    
            nextLine = textReader.ReadLine();
            while (nextLine != null)
            {
                rowNumber = rowNumber+1;
    
                string columnName = "";
    
                columns = nextLine.Split(delimiters);
    
                try
                {
                    Output0Buffer.AddRow();
                    columnName = "name";
                    Output0Buffer.name = columns[0];
                    columnName = "address";
                    Output0Buffer.address = columns[1];
                }
    
                catch (Exception e) {
                    RemoveRow(Output0Buffer); 
    
                    ErrorOutputBuffer.AddRow();
                    ErrorOutputBuffer.errmsg = String.Format("truncations happens in row {0},on column {1}", rowNumber, columnName);              
    
                }
    
    
    
                nextLine = textReader.ReadLine();
            }
        }
    
        public void RemoveRow(ScriptBuffer scriptBuffer)
        {
            BindingFlags bindFlags = BindingFlags.Instance | BindingFlags.NonPublic;
            FieldInfo field = scriptBuffer.GetType().GetField("Buffer", bindFlags);
            ((PipelineBuffer)field.GetValue(scriptBuffer)).RemoveRow();
        }
    
    }
    

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Mr Shaw Saturday, November 19, 2016 7:07 PM
    Thursday, November 10, 2016 2:48 AM
    Moderator
  • Thanks for the in great response.

    In your experience if you were asked to extract data from a csv file would you use the flat file source component or would you script.

    To me not knowing where an error has happened in a file just seems wrong.

    Thanks,

    Ben

     

    Mr Shaw... One day I might know a thing or two about SQL Server!

    Friday, November 11, 2016 12:23 AM
  • Hi Ben,

    I'd say it depends.

    If there's too many columns and problematic rows, I'd like to use script to find where errors happen, then log and redirect.

    If much less columns and problematic rows, I'd prefer flat file source. By the way, as the flat file source can also redirect and log where the problems happen, not sure why you don't like it?
     

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 11, 2016 12:55 AM
    Moderator
  • Hi Ben,

    I'd say it depends.

    If there's too many columns and problematic rows, I'd like to use script to find where errors happen, then log and redirect.

    If much less columns and problematic rows, I'd prefer flat file source. By the way, as the flat file source can also redirect and log where the problems happen, not sure why you don't like it?
     

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    I am not a fan of the flat file source because when an a row is redirected it only tells you the lineageID of the column of the column in question. I do feel this is very useful. I find it amazing that it is not until SQL 2016 that column names are included.

     


    Mr Shaw... One day I might know a thing or two about SQL Server!

    Friday, November 11, 2016 9:04 AM
  • Hi Shaw,

    How is it going on? If no further question, please mark the replies making sense as answer to close this thread.
    Thanks.

     

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 16, 2016 1:44 AM
    Moderator
  • It would certainly be useful being able to add a line number to the output from flat files (not just on redirect link)
    Wednesday, November 16, 2016 2:07 AM
  • Sorry for the delay in replying.

    The solution given was amazing. My only concern is that when I have finished the project it will require somebody with C# skills to maintain it.

    Thanks,

    Ben 


    Mr Shaw... One day I might know a thing or two about SQL Server!

    Saturday, November 19, 2016 7:06 PM