Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 SSIS,excel и макрос на октрытие книги  [new]
lisyonok
Member

Откуда:
Сообщений: 15
Подскажите, пожалуйста, можно ли как-то эффективнее,быстрее выполнять макросы VBA в Excel через SSIS пакет. У меня есть некий шаблон Excel, в котором имеются названия столбцов (для помещения табличных данных из sql) и модуль книги,который срабатывает при открытии книги и форматирует колонтитул и затем код из модуля очищается. Этот шаблон копируется и заполняется данными.Я думала, что при заполнении данных SSIS вызывает откытие книги Excel и тем самым отрабатывает макрос, но увы он открывает его как-то по-другому и макрос не срабатывает. Поэтому пришлось добавить в SSIS пакет компоненту Script Task с кодом открытия документа:

Option Strict Off
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

    Public Sub Main()
        Dim oExcel As Object = Nothing
        oExcel = CreateObject("Excel.Application")
        oExcel.Workbooks.Open(Dts.Variables("fileOut").Value)
        Dts.TaskResult = Dts.Results.Success
    End Sub
End Class

Тогда всё отрабатывает, но фалов Excel очень много, около 20 тясыч. И раньше без компоненты Script Task пакет отрабатывал за 3-4 часа, а сейчас все 16 часов. Это очень долго и не позволительно в этой задачи. На выходе должны быть Excel документы с отфомартированным колонтитулом и без модуля книги. Может кто знает способ побыстродейственнее? Заранее благодарю!
6 фев 15, 02:14    [17225969]     Ответить | Цитировать Сообщить модератору
 Re: SSIS,excel и макрос на октрытие книги  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35369
Блог
lisyonok,

проще всего запускать в несколько потоков
6 фев 15, 04:34    [17226017]     Ответить | Цитировать Сообщить модератору
 Re: SSIS,excel и макрос на октрытие книги  [new]
Glory
Member

Откуда:
Сообщений: 104751
lisyonok
Я думала, что при заполнении данных SSIS вызывает откытие книги Excel и тем самым отрабатывает макрос, но увы он открывает его как-то по-другому и макрос не срабатывает

Потому что обращение к файлу через провайдера типа JETOLEDB не моежт выполнять какие-то макросы

lisyonok
много, около 20 тясыч. И раньше без компоненты Script Task пакет отрабатывал за 3-4 часа, а сейчас все 16 часов. Это очень долго и не позволительно в этой задачи. На выходе должны быть Excel документы с отфомартированным колонтитулом и без модуля книги. Может кто знает способ побыстродейственнее?

Создаете шаблоны с нужным форматированием шапок. Новый файл создаете через копирование шаблона. Файл потом заполняете данными
Или
Начинаете использовать какую нибудь отчетную систему, которая позволяет экспортировать свои отчеты в файлы разных форматов
6 фев 15, 13:31    [17228115]     Ответить | Цитировать Сообщить модератору
 Re: SSIS,excel и макрос на октрытие книги  [new]
lisyonok
Member

Откуда:
Сообщений: 15
Glory
Создаете шаблоны с нужным форматированием шапок. Новый файл создаете через копирование шаблона. Файл потом заполняете данными


Спасибо! Но я так и делаю. Копирую шаблон,в котором прописан модуль на форматирование колонтитулов и который срабатывает при открытии книги,а только потом заполняю данными созданные на основе шаблона файлы. И этот модуль не отрабатывает, не происходит вызова метода Открытия книги....а информация в колонтитулах должна варьироваться в зависимости от имени создаваемого файла.
7 фев 15, 10:19    [17232232]     Ответить | Цитировать Сообщить модератору
 Re: SSIS,excel и макрос на октрытие книги  [new]
Glory
Member

Откуда:
Сообщений: 104751
lisyonok
Копирую шаблон,в котором прописан модуль на форматирование колонтитулов

Это не шаблон
Шаблон - это файл без всяких макросов с уже готовыми колонтитулами и пр.
У вас должен быть шаблон под каждый вариант

lisyonok
И этот модуль не отрабатывает, не происходит вызова метода

Еще раз
Обращение к данным файла через провайдера - это не открытие файла в приложении Excel
7 фев 15, 16:00    [17232901]     Ответить | Цитировать Сообщить модератору
 Re: SSIS,excel и макрос на октрытие книги  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8805
lisyonok,

выполнение макроса при открытии - это настройки приложения. Если Вы используете автоматизацию, то запускайте макросы самостоятельно.

Вообще, Вы решили задачу шиворот-навыворот, поставили телегу впереди лошади. Т.е. SQL сервер у Вас в роли клиента, а Иксель - с роли сервера.

Во должны написать импорт (запрос) и формирование документа на VBA, например, в самом Excel.
8 фев 15, 00:09    [17233868]     Ответить | Цитировать Сообщить модератору
 Re: SSIS,excel и макрос на октрытие книги  [new]
lisyonok
Member

Откуда:
Сообщений: 15
Glory,спасибо,я не правильно поняла значение слова "шаблон". У меня каждый выходной excel имеет свой отличный коллонтитул,данные для которого извлекаются из sql таблицы. поэтому я думаю не имеет смысла создавать 20 тысяч шаблонов...надо как-то по-другому. До этого всё было прекрасно пару лет, пока не понадобилось это форматирование колонтитула.Наверное из-за этой вроде бы мелочи придется всё менять серьезно...
8 фев 15, 06:14    [17234247]     Ответить | Цитировать Сообщить модератору
 Re: SSIS,excel и макрос на октрытие книги  [new]
lisyonok
Member

Откуда:
Сообщений: 15
Владислав Колосов,спасибо!вы имеете в виду переписать весь SSIS пакет в excel код на VBA, и тогда задача будет быстро отрабатывать?просто в SSIS пакете замешано несколько sql-таблиц, хранимых процедур,функций и множество входных и выходных xls файлов...
8 фев 15, 06:18    [17234248]     Ответить | Цитировать Сообщить модератору
 Re: SSIS,excel и макрос на октрытие книги  [new]
Glory
Member

Откуда:
Сообщений: 104751
lisyonok
У меня каждый выходной excel имеет свой отличный коллонтитул,данные для которого извлекаются из sql таблицы. поэтому я думаю не имеет смысла создавать 20 тысяч шаблонов...надо как-то по-другому.

Т.е. 20 000 файлов-шаблонов - это много, а 20 000 записей в таблице для описания шаблонов - это нормально ?

lisyonok
.Наверное из-за этой вроде бы мелочи придется всё менять серьезно...

Создание собственной системы отчетов - это всегда серьезно.
Кокретно для Excel можно использовать сразу xml формат без всяких форматирований
9 фев 15, 10:07    [17236782]     Ответить | Цитировать Сообщить модератору
 Re: SSIS,excel и макрос на октрытие книги  [new]
lisyonok
Member

Откуда:
Сообщений: 15
Glory,спасибо! 20 000 записей в sql таблице не для описания шаблона - это второстепенная их роль, а так они нужны для решения самой задачи. да и как создать заведомо 20 тысяч файлов-шаблонов .xls, данные для которых мы получаем в процессе выполнения самого пакета. Либо я что-то не понимаю...я конечно в этом начинающий.
А создание собственной системы отчетов это наверное трудоемкий процесс.Я просто вот этого точно не понимаю,увы,что это и какой принцип. Благодарю Вас за помощь!
9 фев 15, 10:33    [17236932]     Ответить | Цитировать Сообщить модератору
 Re: SSIS,excel и макрос на октрытие книги  [new]
Glory
Member

Откуда:
Сообщений: 104751
lisyonok
Glory,спасибо! 20 000 записей в sql таблице не для описания шаблона - это второстепенная их роль, а так они нужны для решения самой задачи. да и как создать заведомо 20 тысяч файлов-шаблонов .xls, данные для которых мы получаем в процессе выполнения самого пакета. Либо я что-то не понимаю...я конечно в этом начинающий.

Еще раз
Заранее сформированный шаблон уменьшает время генерации отчета на время равное времени формирования ваших колонтитулов.
Вы разве не с уменьшением этого времени боретесь ?
Зачем каждый раз делать форматирование, если его можно сделать 1 раз ?
9 фев 15, 10:37    [17236965]     Ответить | Цитировать Сообщить модератору
 Re: SSIS,excel и макрос на октрытие книги  [new]
lisyonok
Member

Откуда:
Сообщений: 15
Glory, да,нужно чтобы быстрее отрабатывало. Но я не понимаю как я могу подготовить заранее 20 тысяч файлов шаблонов,информация в колонтитулах которых меняется в зависимости от записей в sql таблице в определенный момент времени. Это информация об объектах,количество которых меняется и сама информация о них тоже.Это же всё равно придется сначала сгенерировать эти файлы (что думаю не быстро),и делать каждый раз это перед выполнением пакета.
Ранее я для каждого объекта по циклу нужную информацию об объекте помещала в название xls файла, который копировался по шаблону с вcтроенным макросом (формирующим колонтитулы из названия файла), заполняла его сформированными данными и вызывала событие "открытия книги".
Я не понимаю как сделать то,что вы хотите мне сказать...
9 фев 15, 14:59    [17238919]     Ответить | Цитировать Сообщить модератору
 Re: SSIS,excel и макрос на октрытие книги  [new]
Glory
Member

Откуда:
Сообщений: 104751
lisyonok
Но я не понимаю как я могу подготовить заранее 20 тысяч файлов шаблонов,информация в колонтитулах которых меняется в зависимости от записей в sql таблице в определенный момент времени.

Написать код, который возмет эту "информацию о колонтитулах" и сделать готовый файл-шаблон
Запускать этот код каждый раз, когда "информация о колонтитулах" меняется
9 фев 15, 15:03    [17238945]     Ответить | Цитировать Сообщить модератору
 Re: SSIS,excel и макрос на октрытие книги  [new]
lisyonok
Member

Откуда:
Сообщений: 15
Glory,
спасибо,но это получится я думаю не сильно быстрее,увы.Каждый раз перед запуском пакета информация эта новая.а формировать 20 тысяч xls файлов с колонтитулами думаю в любой среде не быстро.И потом еще формировать уже нужные заполненные эти тысячи файлов. Так что наверное придется остановится на 17 часах...
Я очень благодарна за потраченное вами время на меня и помощь )
9 фев 15, 15:13    [17239021]     Ответить | Цитировать Сообщить модератору
 Re: SSIS,excel и макрос на октрытие книги  [new]
Glory
Member

Откуда:
Сообщений: 104751
lisyonok
Glory,
спасибо,но это получится я думаю не сильно быстрее,увы.Каждый раз перед запуском пакета информация эта новая.а формировать 20 тысяч xls файлов с колонтитулами думаю в любой среде не быстро.И потом еще формировать уже нужные заполненные эти тысячи файлов. Так что наверное придется остановится на 17 часах...
Я очень благодарна за потраченное вами время на меня и помощь )

Млин
Перед каким "каждым запуском" ?
Есть в таблице "Описание отчета" с вашими колонтитулами для каждого отчета.
Для этого "описания" сгенерирован шаблон, который используется отчетом.
При вснесении изменений в "Описание отчета" шаблон перегенерируется.
9 фев 15, 15:21    [17239057]     Ответить | Цитировать Сообщить модератору
 Re: SSIS,excel и макрос на октрытие книги  [new]
lisyonok
Member

Откуда:
Сообщений: 15
Glory,
каждый запуск - это я про запуск ssis пакета, который нужен для формирования файлов по объектам.И вот по этим объектам информация меняется постоянно.то есть перед каждым запуском пакета я сначала импортирую в sql таблицу данные с описанием этих объектов.
9 фев 15, 15:33    [17239137]     Ответить | Цитировать Сообщить модератору
 Re: SSIS,excel и макрос на октрытие книги  [new]
Glory
Member

Откуда:
Сообщений: 104751
lisyonok
каждый запуск - это я про запуск ssis пакета, который нужен для формирования файлов по объектам.И вот по этим объектам информация меняется постоянно.то есть перед каждым запуском пакета я сначала импортирую в sql таблицу данные с описанием этих объектов.

Вы хотите сказать, что каждый запуск пакета отличается результатом от предыдущего запуска ?
И что у вас никогда не появится двух одинаковых результата ?
9 фев 15, 15:36    [17239156]     Ответить | Цитировать Сообщить модератору
 Re: SSIS,excel и макрос на октрытие книги  [new]
lisyonok
Member

Откуда:
Сообщений: 15
Glory,
да, набор файлов на выходе меняется. Конечно, периодически оно остается неизменным для некоторых объектов,но не для всего набора объектов.
9 фев 15, 15:42    [17239198]     Ответить | Цитировать Сообщить модератору
 Re: SSIS,excel и макрос на октрытие книги  [new]
Glory
Member

Откуда:
Сообщений: 104751
lisyonok
да, набор файлов на выходе меняется.

Какой еще набор файлов ? Вы о чем ?
9 фев 15, 15:43    [17239208]     Ответить | Цитировать Сообщить модератору
 Re: SSIS,excel и макрос на октрытие книги  [new]
lisyonok
Member

Откуда:
Сообщений: 15
Glory,
я о выходном наборе файлов. На выходе у нас как раз файлы с данными для каждого из объектов. краткое описание которых и вносится в колонтитул. например, файл Тойота.xls и в колонтитуле - номер м423лк цвет желтый и тп.
а цвет может поменялся, может ее вообще уже не существует...
9 фев 15, 15:50    [17239249]     Ответить | Цитировать Сообщить модератору
 Re: SSIS,excel и макрос на октрытие книги  [new]
Glory
Member

Откуда:
Сообщений: 104751
lisyonok
я о выходном наборе файлов. На выходе у нас как раз файлы с данными для каждого из объектов. краткое описание которых и вносится в колонтитул. например, файл Тойота.xls и в колонтитуле - номер м423лк цвет желтый и тп.
а цвет может поменялся, может ее вообще уже не существует...

Господи. Т.е. вы вертикальные данные траспонируете в горизонтальные что ли ?
9 фев 15, 15:53    [17239277]     Ответить | Цитировать Сообщить модератору
 Re: SSIS,excel и макрос на октрытие книги  [new]
lisyonok
Member

Откуда:
Сообщений: 15
Glory,
не совсем так. входная информация да, подается скажем в 'вертикальном' виде (в каждом из файлов описана для каждого человека какие у него есть допустим машины), но далее она преобразуется. С этой информацией о людях происходит доработка (замешено несколько sql таблиц) для полной информации о людях, которую не дали. и на выходе получаем файлы уже по машинам с подробным описаниям их владельцев.
вы думаете что эта задача была заведомо давно решена не правильно?
9 фев 15, 16:03    [17239360]     Ответить | Цитировать Сообщить модератору
 Re: SSIS,excel и макрос на октрытие книги  [new]
Glory
Member

Откуда:
Сообщений: 104751
lisyonok
не совсем так. входная информация да, подается скажем в 'вертикальном' виде (в каждом из файлов описана для каждого человека какие у него есть допустим машины), но далее она преобразуется. С этой информацией о людях происходит доработка (замешено несколько sql таблиц) для полной информации о людях, которую не дали. и на выходе получаем файлы уже по машинам с подробным описаниям их владельцев.
вы думаете что эта задача была заведомо давно решена не правильно?

Откуда тогда 20 000 _разных_ видов колонтитулов ? Откуда у машины столько "подробностей" ?
9 фев 15, 16:06    [17239383]     Ответить | Цитировать Сообщить модератору
 Re: SSIS,excel и макрос на октрытие книги  [new]
lisyonok
Member

Откуда:
Сообщений: 15
Glory,20 тысяч машин. А у машины выводится несколько её свойств. У каждой машины же они индивидуальные
9 фев 15, 16:08    [17239402]     Ответить | Цитировать Сообщить модератору
 Re: SSIS,excel и макрос на октрытие книги  [new]
Glory
Member

Откуда:
Сообщений: 104751
lisyonok
Glory,20 тысяч машин. А у машины выводится несколько её свойств. У каждой машины же они индивидуальные

Т.е. каждая из 20 тыс машин обладает своим уникальным набором свойств ??? Вы уверены ?
9 фев 15, 16:10    [17239423]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить