none
custom logging in SSIS script task.

    Question

  • Hi , 

    I'm trying to find a way to capture variable containing in catch block to better log the error in SSIS. The variable name 

    "File_Ability_Identity_Full" contains file path and error logged does not show the file name currently. As of today , exception thrown in catch just says "An error occurred with the following error message: "Could not find a part of the path.".

    I would like to capture file path which the error is pointing to. Variable "File_Ability_Identity_Full" Evaluates to expression : 

    @[User::Directory_Ability_From_Client_Base_Subpath] +  @[User::File_Ability_Identify_Name] -- which in turn contains folder and sub paths where to be processed file is located.  Please let me know if any further details are needed. 

    public void Main()
    		{
                //User::File_Ability_Identify_Full
                try
                {
                    string _fName = Dts.Variables["User::File_Ability_Identify_Full"].Value.ToString();
                    Int32 _doesExist = 0;
    
    
                    if (File.Exists(_fName))
                    {
                        _doesExist = 1;
                    }
                    else { _doesExist = 0; }
                    Dts.Variables["vFileExists"].Value = _doesExist;
                }
                catch (Exception e)
                { }
    
                
    
                
    
    			Dts.TaskResult = (int)ScriptResults.Success;
    		}
    
            #region ScriptResults declaration
            /// <summary>
            /// This enum provides a convenient shorthand within the scope of this class for setting the
            /// result of the script.
            /// 
            /// This code was generated automatically.
            /// </summary>
            enum ScriptResults
            {
                Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
                Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
            };
            #endregion
    
    	}
    }

    Friday, May 17, 2019 11:32 AM

Answers

All replies

  • Hi Vishal,

    It seems that the issue you are facing is with the file's fully qualified path construction.

    Please share the source code how you construct it.

    In the meantime, you can try the following:

    void Main()
    {
    	string _fName = string.Empty;
    
    	//User::File_Ability_Identify_Full
    	try
    	{
    		Int32 _doesExist = 0;
    		_fName = Dts.Variables["User::File_Ability_Identify_Full"].Value.ToString();
    
    		if (File.Exists(_fName))
    		{
    			_doesExist = 1;
    		}
    		else
    		{
    			_doesExist = 0;
    			// Don't forget to tick/check off System::TaskName variable as ReadOnly in the Task Parameters
    			Dts.Events.FireWarning(1, Dts.Variables["System::TaskName"].Value.ToString()
    			  , string.Format("file: '{0}' does not exist.", _fName)
    			  , "", 0);
    		}
    
    		Dts.Variables["vFileExists"].Value = _doesExist;
    		Dts.TaskResult = (int)ScriptResults.Success;
    	}
    	catch (Exception ex)
    	{
    		// Don't forget to tick/check off System::TaskName variable as ReadOnly in the Task Parameters
    		Dts.Events.FireError(18, Dts.Variables["System::TaskName"].Value.ToString()
    		  , string.Format("file: '{0}', error: {1}", _fName, ex.Message.ToString())
    		  , "", 0);
    	
    		Dts.TaskResult = (int)ScriptResults.Failure;
    	}
    }

    Friday, May 17, 2019 1:51 PM
  • Thank you. 
    • Marked as answer by vishal.... _ Friday, May 17, 2019 5:54 PM
    Friday, May 17, 2019 2:52 PM
  • Hi Vishal,

    Glad to hear that the proposed solution is working for you.

    Please don't forget to click "Mark as Answer" the response(s) that resolved your issue. This can be beneficial to other community members reading this thread.


    Friday, May 17, 2019 3:02 PM
  • Hi Vishal,

    You need to click "Mark as Answer" the response(s) that resolved your issue.

    Not the 'Thank you' phrase response.

    Friday, May 17, 2019 8:03 PM