Вопрос или проблема
У меня есть несколько проектов, которые извлекают данные с сервера клиента в файлы Excel, обрабатывают их, а затем обновляют выходные файлы Excel.
В настоящее время у меня есть несколько десятков задач сценариев, и я указываю файл и тайм-аут в каждом файле, и каждый из них может быть разным, и, конечно, я не знаю, сколько времени мне потребуется, пока не попробую это и не обнаружу, что это не сработало.
Мой код выглядит следующим образом:
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using Excel = Microsoft.Office.Interop.Excel;
using System.Threading;
using System.Windows.Forms;
using System.IO;
#endregion
namespace ST_5b129430179446449b528cfa97b05e80
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
/// <summary>
/// Обновляет все листы/вкладки в файле Excel
/// </summary>
public void Main()
{
Dts.TaskResult = (int)ScriptResults.Success;
/// Путь и имя файла Excel
string fileName = @"C:\OneDrive\Projects\Client\Data\Customers.xlsx";
Excel.Application m_objExcel;
Excel.Workbooks m_objBooks;
Excel.Workbook m_objBook;
try
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel.Workbook)m_objBooks.Open(fileName,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing);
/// Время, необходимое для открытия файла, увеличьте для больших файлов
Thread.Sleep(3000);
m_objBook.RefreshAll();
/// Время, необходимое для обновления и сохранения файла Excel
Thread.Sleep(5000);
m_objBook.Save();
m_objBook.Close();
m_objExcel.Quit();
}
catch (Exception exception)
{
MessageBox.Show("Я ловлю ошибку");
using (StreamWriter sw = File.CreateText("C:\\OneDrive\\Projects\\Client\\Data\\Logs\\" + "Customers_ErrorLog_" + DateTime.Now.ToString("yyyMMddHHmmss") + ".log"))
{
sw.WriteLine(exception.ToString());
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
finally
{
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
}
}
#region ScriptResults declaration
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}
Использование RefreshAll() и Thread.Sleep(x) — единственный способ, который я нашел, чтобы приостановить выполнение сценария, пока Excel открывается и обновляется. Я поражен, что нет более простого способа дождаться, пока Excel будет готов и/или завершит задачу.
Существует ли лучший метод для этого?
Ответ или решение
Как освежить вкладки Excel в SSIS: оптимальные подходы
Работа с файлами Excel в контексте интеграции данных через SQL Server Integration Services (SSIS) может стать настоящим вызовом, особенно когда речь идет о необходимости обновления данных. Ваша задача — освежение всех вкладок в Excel-файле — важный шаг для обеспечения актуальности данных, однако использование неподходящих методов может привести к дополнительным трудозатратам и неэффективности.
Ваша текущая реализация использует Thread.Sleep(x) для ожидания завершения операций Excel, что, хотя и может работать, является неэффективным подходом. Давайте рассмотрим более надежные и эффективные методы.
1. Использование событий Excel
Одной из основных причин, почему Thread.Sleep не рекомендуется, является то, что он блокирует поток выполнения, что может замедлить работу всего процесса. Вместо этого, вы можете использовать события Excel для отслеживания завершения действий. Вот как это можно организовать:
m_objBook.RefreshAll();
m_objExcel.Visible = false; // Скрыть Excel во время выполнения
// Использовать ожидание события обновления
m_objExcel.SheetChange += new Excel.AppEvents_SheetChangeEventHandler(SheetChangeHandler);Вам понадобится создать обработчик, который будет вызываться при завершении обновления.
2. Проверка статуса обновления
Вместо того чтобы полагаться на таймер, вы можете проверить, завершена ли операция обновления. Это можно сделать, опрашивая свойства Excel, такие как m_objExcel.Ready (если такая имеется), чтобы продолжить выполнение скрипта.
3. Управление потоками
Для более сложных сценариев вы можете рассмотреть использование асинхронного программирования или потоков в .NET для запуска Excel в отдельном потоке. Это позволит основному потоку продолжать выполнение, пока Excel завершает свою работу.
4. Обработка ошибок
Вы уже занимаетесь обработкой ошибок, но стоит убедиться, что вы аккуратно закрываете Excel независимо от результата выполнения. Обеспечение корректного завершения процессов Excel предотвратит возможные утечки ресурсов.
finally
{
if (m_objBook != null) m_objBook.Close(false);
if (m_objExcel != null) m_objExcel.Quit();
}5. Логи и мониторинг
Убедитесь, что вся информация об ошибках и временные метки записываются в журнале, как вы уже делаете. Это позволит вам более точно определять потенциальные проблемы в будущем.
Заключение
Использование методов, которые устраняют необходимость в Thread.Sleep, позволяет вам значительно увеличить надежность процесса обновления данных в Excel. Интеграция событий и асинхронных вызовов в вашу текущую структуру кода создаст более чистый и эффективный код, упрощая поддержку и расширение вашей системы в будущем.
С данным подходом вы сможете улучшить свое текущее решение и значительно повысить производительность и стабильность вашей ETL-процесса в SSIS.