Вопрос или проблема
У меня есть несколько проектов, которые извлекают данные с сервера клиента в файлы Excel, обрабатывают их, а затем обновляют выходные файлы Excel.
В настоящее время у меня есть несколько десятков задач Script Task в проекте SSIS в Visual Studio 2019, и я задаю файл и тайм-аут в каждом файле, и каждый может быть разным, и, конечно, я не знаю, сколько времени мне нужно, пока не попробую это сделать, и это не сработает.
Мой код выглядит следующим образом:
#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;
/// Неудачная попытка использовать переменные
///string filePath = (string)Dts.Variables["$package::FilePath"].Value;
///string fileName = (string)Dts.Variables["$package::ExcelFileName"].Value;
/// Путь и имя файла 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
}
}
Использование Thread.Sleep(x) — это единственный способ, который я нашел, чтобы приостановить скрипт, пока Excel открывается и обновляется. Меня удивляет, что нет более простого способа дождаться, пока Excel будет готов и/или завершит задачу.
Я пытался добавить несколько переменных пакета для пути, имени файла, расширения и тайм-аута. Затем в Script Task я добавил их как ReadOnlyVariables, но я никогда не мог заставить их работать правильно. Мне действительно хотелось бы сделать это таким образом, поскольку редактирование самого скрипта для внесения небольших изменений, таких как увеличение времени, занимает гораздо больше времени, чем просто изменение в области переменной.
Думаю, у меня есть несколько связанных вопросов.
- Я правильно передавал переменные, создавая переменные пакета и добавляя их в ReadOnlyVariables в Script Task?
- Или есть способ указать значение переменной на экранах настройки Script Task?
- Я правильно ловил переменную в скрипте?
Ответ или решение
Обновление файла Excel с использованием переменных в SSIS проекте в Visual Studio
Ваша задача заключается в обновлении файлов Excel в процессе пакетной интеграции с помощью SQL Server Integration Services (SSIS) в Visual Studio 2019. Из-за существующих различных настроек временных задержек и путей к файлам вы хотите оптимизировать свой код, чтобы использовать переменные, определенные на уровне пакета.
Проблемы и решения
-
Использование переменных пакета в Script Task:
Для того чтобы передавать переменные из пакета в скрипт, нужно убедиться, что переменные правильно определены и доступны. Вам необходимо создать переменные на уровне пакета, такие какFilePath,ExcelFileName,Timeout, и затем добавить их в свойстваReadOnlyVariablesвашего Script Task.Пример объявления переменных:
User::FilePath(String)User::ExcelFileName(String)User::Timeout(Int32)
Убедитесь, что в разделе свойств вашего Script Task в Visual Studio вы правильно указали эти переменные. Например, в строке
ReadOnlyVariablesдолжно быть указано:User::FilePath;User::ExcelFileName;User::Timeout -
Получение значений переменных в коде скрипта:
В вашем коде, чтобы обратиться к значениям переменных, нужно использовать правильный синтаксис, как в следующем примере:string filePath = Dts.Variables["User::FilePath"].Value.ToString(); string fileName = Dts.Variables["User::ExcelFileName"].Value.ToString(); int timeout = (int)Dts.Variables["User::Timeout"].Value;Этот способ позволит вам динамически использовать значения, определенные в переменных пакета, вместо жестко закодированных значений.
-
Управление задержками в выполнении:
Вместо использованияThread.Sleep(x), вы можете применить другой подход для ожидания завершения операций Excel. Существует методApplication.Wait, который может быть более эффективен:DateTime endTime = DateTime.Now.AddSeconds(timeout); while (DateTime.Now < endTime) { Thread.Sleep(100); // Уменьшение частоты ожидания }Это поможет вам оптимизировать время ожидания, не блокируя ваше приложение в течение слишком долгого времени.
Пример обновленного кода
Вот как может выглядеть ваш скрипт с учетом вышеперечисленных рекомендаций:
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;
#endregion
namespace ST_5b129430179446449b528cfa97b05e80
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
Dts.TaskResult = (int)ScriptResults.Success;
string filePath = Dts.Variables["User::FilePath"].Value.ToString();
string fileName = Dts.Variables["User::ExcelFileName"].Value.ToString();
int timeout = (int)Dts.Variables["User::Timeout"].Value;
Excel.Application m_objExcel = new Excel.Application();
Excel.Workbooks m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
try
{
m_objBook = m_objBooks.Open(filePath + 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);
m_objBook.RefreshAll();
DateTime endTime = DateTime.Now.AddSeconds(timeout);
while (DateTime.Now < endTime)
{
Thread.Sleep(100);
}
m_objBook.Save();
m_objBook.Close();
}
catch (Exception exception)
{
LogError(exception.ToString());
Dts.TaskResult = (int)ScriptResults.Failure;
}
finally
{
m_objExcel.Quit();
GC.Collect();
}
}
private void LogError(string errorMessage)
{
string logFilePath = $"C:\\OneDrive\\Projects\\Client\\Data\\Logs\\Customers_ErrorLog_{DateTime.Now:yyyyMMddHHmmss}.log";
using (StreamWriter sw = File.CreateText(logFilePath))
{
sw.WriteLine(errorMessage);
}
}
#region ScriptResults declaration
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}Заключение
Используя описанные подходы для работы с переменными пакетами в SSIS, вы сможете оптимизировать процесс обновления Excel файлов. Это сократит время разработки и упростит процедуру изменения параметров. Воспользуйтесь предложениями для улучшения производительности и гибкости вашей SSIS логики, а также для упрощения отладки и уменьшения жесткокодированных настроек, что позитивно повлияет на поддерживаемость вашего кода.