locked
C# script to verify excel file is giving an error RRS feed

  • Question

  • 'm not familiar with C# and I have a script component in SSIS that reads an excel file which is provided by a variable [Excel_File_Path] I can build the script fine but whenever I run the package I get this error

    DTS Script Task has encountered an exception in user code: An exception has been thrown by the target of an invocation.

    at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
       at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
       at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
       at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
       at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

    The script that I have is as follows:

    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    
    namespace ST_fcd56ec20b8646dfb3261487b129ff89.csproj
    {
        [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
        public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
        {
    
            #region VSTA generated code
            enum ScriptResults
            {
                Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
                Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
            };
            #endregion
    
            public void Main()
            {
                // TODO: Add your code here
                int error_count = 0;
                string error_detail = "";
                string excelFilePath = Dts.Variables["User::Excel_File_Path"].Value.ToString();
                string strCn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelFilePath + ";Excel 12.0;HDR=YES";
                DataTable dtTables = new DataTable(); ;
                System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(strCn);
                cn.Open();
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand
                {
                    Connection = cn,
                    CommandText = "SELECT * FROM [Sheet1$]"
                };
                System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter();
                adapter.SelectCommand = command;
                adapter.Fill(dtTables);
                string str = string.Empty;
                if (dtTables.Columns.Count != 45)
                {
                    error_count += 1;
                    error_detail = "Mis-match in the columns count" + "\r\n";
                    MessageBox.Show("Column Count is Not Equal To 45, Please Check!");
                }
    
                Dts.Variables["User::ErrorsInSource"].Value = error_count;
                Dts.Variables["User::Body"].Value = error_detail;
    
    
    
                cn.Close();
            }
    
            //Dts.TaskResult = (int)ScriptResults.Success;
    
    
        }
    }

    What am am I missing in here?


    Tuesday, August 4, 2020 12:20 AM

All replies

  • Hi Sam Mitch,

    Please use the try{} catch{} statement to get the detailed error message.

    We can refer to Logging in the Script Task.

    The use of logging in Integration Services packages lets you record detailed information about execution progress, results, and problems by recording predefined events or user-defined messages for later analysis.

    using System;  
    using System.Data;  
    using Microsoft.SqlServer.Dts.Runtime;  
      
    public class ScriptMain  
    {  
      
        public void Main()  
            {  
                ...  
                
                try  
                {  
                    ...  
                    Dts.TaskResult = (int)ScriptResults.Success;  
                }  
                catch (Exception ex)  
                {  
                    //An error occurred.  
                    Dts.Events.FireError(0, "Script Task Example", ex.Message + "\r" + ex.StackTrace, String.Empty, 0);  
                    Dts.TaskResult = (int)ScriptResults.Failure;  
                }  
      
            }

    Best Regards,

    Mona


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Tuesday, August 4, 2020 2:46 AM
  • Thanks, that helped figuring out the error

    [Script Task Example] Error: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
       at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper)
       at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
       at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.OleDb.OleDbConnection.Open()
       at ST_fcd56ec20b8646dfb3261487b129ff89.csproj.ScriptMain.Main()

    After running the script this is the error that I'm getting, what does it mean?

    Tuesday, August 4, 2020 3:30 AM
  • Hi Sam Mitch,

    Please download and install the Microsoft ACE driver.

    The following links will be helpful:

      Microsoft Access Database Engine 2010 Redistributable

      Installing the Microsoft.ACE.OLEDB.12.0 Provider for Both 64-bit and 32-bit Processing

    Best Regards,

    Mona


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Tuesday, August 4, 2020 5:22 AM