Custom logging in Active task SSIS

Question

I am facing two issues in my SSIS package. The SSIS package I have has an active task that formats an excel sheet as per my requirement and saves it as a different file, modified.xlsx. This file is then used in my data flow task to process and upload data to database tables. This package works perfectly in my local system, but when I created a scheduled job on my SQL server to run this package it fails with the generic error message "Microsoft (R) SQL Server Execute Package Utility Version 11.0.5058.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 12:06:55 PM Error: 2016-04-01 12:06:57.06 Code: 0x00000001 Source: Script Task Description: Exception has been thrown by the target of an invocation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:06:55 PM Finished: 12:06:57 PM Elapsed: 1.563 seconds. The package execution failed. The step failed."

To get a more detailed error message i tried to set up logging for the active task. I configured logging to write log entries for events to a CSV file as in the below screen shot.

I enabled logging for package and checked individuals tasks as well. In active tasks, I added Dts.Log("",0,bytes); to keep track of any exception if any also to log each steps.

public partial class ScriptMain:Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
 byte[] bytes = new byte[0];
    public void Main()
            {
                LogMessages("");
    
                LogMessages("Update Bug package execution started at :: " + DateTime.Now.ToLongTimeString());
                LogMessages("Loading package configuration values to local variables.");
    
                FileName = Convert.ToString(Dts.Variables["User::ExcelFileName"].Value);
                SourceFileLocation = Convert.ToString(Dts.Variables["User::SourceFileLoc"].Value);
    
                SourceFileName = Path.Combine(SourceFileLocation, FileName);
                saveLoc = Path.Combine(SourceFileLocation, "ModifiedExcel.xlsx");
               
    
                var excel = new Excel.Application();
                var workbook = excel.Workbooks.Open(SourceFileName);
                try
                {
    
                    foreach (Excel.Worksheet tempSheet in workbook.Worksheets)
                    {
                        LogMessages("For loop to check sheet names");
                        if (((Excel.Worksheet)(tempSheet)).Name.Contains("Test"))
                        {
                            if (File.Exists(saveLoc))
                            {
                                File.Delete(saveLoc);
                            }
    
                            //File.Create(saveLoc);
                            tempSheet.Select();
                            workbook.SaveAs(saveLoc);
                        }
    
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(tempSheet);
                    }
    
                    workbook.Save();
                    workbook.Close();
                    excel.Quit();
                    LogMessages("Quit Excel sheet");
    
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                    LogMessages("Release excel objects");
                }
                catch(Exception ex)
                {
                    LogMessages("Exception: " + ex.InnerException);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                }
    
    
                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
    
            #region Log messages to package log files/table.
            public void LogMessages(string strLogMsg)
            {
                Dts.Log(strLogMsg, 0, bytes);
            }
            #endregion
}

But when i run the package the log file is not updated. The log file just contains the following :

Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message

Can someone please help me understand what I am missing here for logging? Also, what could be the issue for the job to fail in SQL server?

score 4 · Answer 1 · edited May 23 '17 at 12:24

4

Why is it not logging?

Here's the fun part, as best as I've been able to divine over the years of dealing with SSIS. Dts.Log is pretty useless, at least if you want it to show up in the Logging facility built into SSIS.

Instead, change out your Dts.Log calls to Dts.Events.Fire e.g.

bool fireAgain = false;
Dts.Events.FireInformation(0, "This gest logged", "My long description here", string.Empty, 0, ref fireAgain);

Then, in your Details tab above, ensure that you have checked the OnInformation event (this also presumes you've configured the package to track it all)

Finally, if you don't actually click the button in Providers and Logs tab, it won't log to the table

Why is it not working?

The package isn't working because you're dealing with Excel and the error message specifies you are running in 64 bit mode.

Microsoft (R) SQL Server Execute Package Utility Version 11.0.5058.0 for 64-bit

Unless you've done something to explicitly make 64 bit Excel work on this server, it's not going to work. Instead, in the SQL Agent job, you need to specify that this task runs in 32 bit mode.

See also

Why does my ODBC connection fail when running an SSIS load in Visual Studio but not when running the same package using Execute Package Utility

edited May 23 '17 at 12:24

Community

1
1

answered Apr 01 '16 at 19:55

billinkc

59,250
9
102
159

Thank you so much for your reply. I am yet to try out the logging part, and will keep you updated on it.But,as for package not working, I set the task to use 32 bit in SQL job and it still gives the same error. I will try to get custom logging to work to get more details on the exception – perplexedDev Apr 05 '16 at 04:57
Logging is still not working.I get the same fields in the log file.I am wondering if I am missing any steps to enable logging other than mentioned above. – perplexedDev Apr 06 '16 at 23:24
@perplexedDev Update your question showing screenshot of your entire "Configure SSIS Logs" screen as shown above. Looking further at your code, you have not supplied all of the code required as it doesn't compile in my mental compiler - `Dts.Log("",0,bytes);` has no reference to bytes – billinkc Apr 07 '16 at 01:48
I have updated the question with the details you have asked for. – perplexedDev Apr 07 '16 at 20:54
@perplexedDev two things: the first is that as I described above, `Dts.Log` is not going to push anything into that file. You must use the `Dts.Events.FirstInformation` The second is when you are in the Script Task Editor, look at your Error List - is there anything listed? – billinkc Apr 07 '16 at 21:07
1

Thirdly, if you don't actually click the button in Providers and Logs tab, it won't log to the table – billinkc Apr 07 '16 at 21:12
The third option did it, it was such a silly thing, can't believe i missed it. Thank you so much – perplexedDev Apr 08 '16 at 00:38
Now while running the job i get the exception in Active Task" Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG))" at the code line Excel.Application excel = new Excel.Application(); Initial web search indicates it is bcoz excel is not installed in the server, I am trying to get more information on this. – perplexedDev Apr 08 '16 at 00:40
Yes, your code assumes you have installed and licensed Microsoft Office on the server. – billinkc Apr 08 '16 at 01:03
Well, I installed Excel and I am able to move past the creation of excel instance.But when I try to open the excel in the code workbook = excel.Workbooks.Open(SourceFileName); I get the error message Microsoft Excel cannot access the file '\\servername\test.xlsx'. There are several possible reasons: • The file name or path does not exist. • The file is being used by another program. • The workbook you are trying to save has the same name as a currently open workbook. What could be the reason for this?Note this works fine in my local system. – perplexedDev Apr 09 '16 at 01:04
I confirmed if the server has the required access, by logging into the server and checking that I can access the shared path(where the excel is stored). – perplexedDev Apr 09 '16 at 01:23
But is the SSIS package running as you or as the SQL Agent/SQL Server account? – billinkc Apr 09 '16 at 01:32
I have created a proxy account with my credentials to run the SQL job. – perplexedDev Apr 09 '16 at 02:03

Custom logging in Active task SSIS

Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message

1 Answers1

Why is it not logging?

Why is it not working?

Linked