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

Откуда: Москва
Сообщений: 912
Здравствуйте.
Есть MS SQL сервер и EXEL файл. Нужно заставить пакет SSIS перезаписывать файл EXEL данными хранящимися в одной из таблиц SQL сервера.
Пока у меня только получилось дописывать информацию в конец EXEL файла при каждом запуске SSIS пакета.
Подскажите, как настроить пакет для перезаписи файла?
PS: в работе с SSIS новичок.
9 дек 12, 16:38    [13602572]     Ответить | Цитировать Сообщить модератору
 Re: ssis обновление excel файла  [new]
Ruuu
Member

Откуда: Иркутск
Сообщений: 4272
Yagrus2,
Вроде как, именно перезаписывать файлы xls через ssis не получится. Для этого делается шаблон, старый файл удаляется, на его место копируется шаблон и в него по новой заливаются данные. Если вам нужны какие-то данные из старой версии файла, то их можно сначала скопировать во временную таблицу на сервере.
9 дек 12, 17:03    [13602640]     Ответить | Цитировать Сообщить модератору
 Re: ssis обновление excel файла  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 912
Ruuu
Yagrus2,
Для этого делается шаблон, старый файл удаляется, на его место копируется шаблон и в него по новой заливаются данные.


Поясните пожалуйста:
Как я понял, вы предлагаете каждый раз создавать новый EXEL-файл. Я не понимаю как быть с его именем. Если я его предварительно удалю, то SSIS выдаст ошибку. И еще вопрос, чем автоматически удалять старый EXEL-файл.
9 дек 12, 17:37    [13602735]     Ответить | Цитировать Сообщить модератору
 Re: ssis обновление excel файла  [new]
Ruuu
Member

Откуда: Иркутск
Сообщений: 4272
Yagrus2,

Имя можно сохранить в переменную.
Копировать, удалять, переименовывать итп. файлы можно с помощью File Sistem Task.
9 дек 12, 17:54    [13602788]     Ответить | Цитировать Сообщить модератору
 Re: ssis обновление excel файла  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3104
SSIS экспорт в Excel
10 дек 12, 08:21    [13604207]     Ответить | Цитировать Сообщить модератору
 Re: ssis обновление excel файла  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 912
HandKot,
Сделал как вы посоветовали.
Добавил два Execute SQL Task.
В первом комнда
DROP TABLE Dest1
, во втором
CREATE TABLE Dest1 (    D Long,    F Long,    G Long   )
.
В EXCEL Destination указано Name of the Excel sheet: Dest1 с таким же количеством столбцов и их названиями как и во втором Execute SQL Task.
Дебаг выдает предупреждения и ошибки из- за того что заваливается первый Execute SQL Task

Warning: 0x0 at Execute SQL1: Произошли ошибки во время выполнения многошаговой операции OLE DB. По возможности, проверьте значения всех состояний OLE DB. Работа не выполнена.

Есть предположения, что может быть настроено не правильно?

К сообщению приложен файл. Размер - 8Kb
10 дек 12, 18:54    [13609055]     Ответить | Цитировать Сообщить модератору
 Re: ssis обновление excel файла  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 912
Хотя иконки получаются зеленые.
10 дек 12, 18:55    [13609058]     Ответить | Цитировать Сообщить модератору
 Re: ssis обновление excel файла  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 912
Ради эксперимента создал отдельно пакет состоящий из Execute SQL Task с командой
DROP TABLE Dest1
.
в результате лист Dest1 оказался пустой.
Я предполагал, что произойдет удаление листа, а не его чистка.

Так же добавил в EXSEL файл еще одну строку, в хвост таблицы, потом закрыл его с сохранением. И последним этапом запустил пакет описанный в начале этого поста. Результат--> добавленная руками строка не удалилась. Магия или так и должно быть?

В ходе дебага также появилось предупреждение: Warning: 0x0 at Execute SQL1: Произошли ошибки во время выполнения многошаговой операции OLE DB. По возможности, проверьте значения всех состояний OLE DB. Работа не выполнена.

Это магия или так и должно быть?
10 дек 12, 19:28    [13609155]     Ответить | Цитировать Сообщить модератору
 Re: ssis обновление excel файла  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 912
Провел еще эксперименты и выяснил.
Допустим при первом запуске пакета выгрузилось N строк.
И пусть потом пользователь внес еще 1 строку, то есть получилось всего в файле N+1 строка.

При последующем запуске исходного пакета, запишется только N строк. При этом в процессе дебага Excel Destination окрасится в красный цвет.
Вопрос: Я делаю неправильные настройки или это нормальное положение вещей для SSIS?
10 дек 12, 20:55    [13609458]     Ответить | Цитировать Сообщить модератору
 Re: ssis обновление excel файла  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3104
Yagrus2
в результате лист Dest1 оказался пустой.
Я предполагал, что произойдет удаление листа, а не его чистка.

происходит именно очистка листа, а не удаление

Yagrus2
Так же добавил в EXSEL файл еще одну строку, в хвост таблицы, потом закрыл его с сохранением. И последним этапом запустил пакет описанный в начале этого поста. Результат--> добавленная руками строка не удалилась. Магия или так и должно быть?

посмотрел, действительно, если пользователь ввел руками строку, то она остается, может только затереться выводимыми данными, видать она не является частью таблицы
тоже сегодня - завтра поэкспериментирую, посмотрю, что можно сделать, если возможно

Yagrus2
При последующем запуске исходного пакета, запишется только N строк. При этом в процессе дебага Excel Destination окрасится в красный цвет.
Вопрос: Я делаю неправильные настройки или это нормальное положение вещей для SSIS?

у меня все работает без ошибок. тут два варианта: или ошибка в настройках, или Вы не закрыли файл экселя
11 дек 12, 08:34    [13610405]     Ответить | Цитировать Сообщить модератору
 Re: ssis обновление excel файла  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3104
HandKot
Yagrus2
Так же добавил в EXSEL файл еще одну строку, в хвост таблицы, потом закрыл его с сохранением. И последним этапом запустил пакет описанный в начале этого поста. Результат--> добавленная руками строка не удалилась. Магия или так и должно быть?

посмотрел, действительно, если пользователь ввел руками строку, то она остается, может только затереться выводимыми данными, видать она не является частью таблицы
тоже сегодня - завтра поэкспериментирую, посмотрю, что можно сделать, если возможно


решил так, если посмотреть на дестинейшен, то там для каждого листа должно быть по две таблицы с названиями Dest1 и Dest1$
вот в шаге удаления талицы, надо удалять их обе

DROP TABLE Dest1
Go
DROP TABLE Dest1$
Go


и тогда, данные заведенный пользователем, тоже удаляются
11 дек 12, 08:59    [13610456]     Ответить | Цитировать Сообщить модератору
 Re: ssis обновление excel файла  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 912
HandKot,
Есть предположения, что за объект Dest1$?
Вопрос возник еще когда создавал Excel Destination. То есть не понятно, почему происходит дублирование.
11 дек 12, 09:53    [13610612]     Ответить | Цитировать Сообщить модератору
 Re: ssis обновление excel файла  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3104
Yagrus2
HandKot,
Есть предположения, что за объект Dest1$?
Вопрос возник еще когда создавал Excel Destination. То есть не понятно, почему происходит дублирование.

предположение одно - разные отображения (наполнения) листа.
но лучше спросить в форуме по экселю, там лучше должны знать объектную модель оного
11 дек 12, 10:03    [13610675]     Ответить | Цитировать Сообщить модератору
 Re: ssis обновление excel файла  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 912
HandKot
Yagrus2
При последующем запуске исходного пакета, запишется только N строк. При этом в процессе дебага Excel Destination окрасится в красный цвет.
Вопрос: Я делаю неправильные настройки или это нормальное положение вещей для SSIS?

у меня все работает без ошибок. тут два варианта: или ошибка в настройках, или Вы не закрыли файл экселя

Файл Excel был закрыт.
В настройках я ничего не менял.
Суть моих манипуляций.
Шаг0. Пакерт работает. OLE DB Source Содержит такой sql запрос
 SELECT 8 AS D, 9 AS F, 10 AS G

Шаг1 Запускаю его. Он отрабатывает. В файл Excel появляется заветная строчка.
Шаг2 Редактирую файл Excel, а именно добавляю руками вторую строку(1,2,3) и закрываю его с сохранением.
Шаг3 Редактирую запрос OLE DB Source
SELECT 812 AS D, 912 AS F, 1012 AS G
union all
SELECT 8122 AS D, 912 AS F, 1012 AS G

Шаг4 Запускаю дебаг пакета
Шаг5 Смотрю результат в EXCEL-файле
D F G
812 912 1012
1 2 3
11 дек 12, 10:13    [13610751]     Ответить | Цитировать Сообщить модератору
 Re: ssis обновление excel файла  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 912
Yagrus2,
На шаге4 получил такую картинку

К сообщению приложен файл. Размер - 6Kb
11 дек 12, 10:14    [13610765]     Ответить | Цитировать Сообщить модератору
 Re: ssis обновление excel файла  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 912
HandKot
HandKot
пропущено...

посмотрел, действительно, если пользователь ввел руками строку, то она остается, может только затереться выводимыми данными, видать она не является частью таблицы
тоже сегодня - завтра поэкспериментирую, посмотрю, что можно сделать, если возможно


решил так, если посмотреть на дестинейшен, то там для каждого листа должно быть по две таблицы с названиями Dest1 и Dest1$
вот в шаге удаления талицы, надо удалять их обе

DROP TABLE Dest1
Go
DROP TABLE Dest1$
Go


и тогда, данные заведенный пользователем, тоже удаляются


Попробовал сделать удалять оба объекта и Dest1 и Dest1$.
Получил следующий неожиданный результат.
1. Добавление Execute SQL Task с командой
DROP TABLE Dest1$
после
DROP TABLE Dest1
ведет к тому, что первая команда DROP TABLE Dest1 трет еще и названия столбцов.
2. Добавление Execute SQL Task с командой
DROP TABLE Dest1$
перед
DROP TABLE Dest1
ведет к тому,что уже команжа
DROP TABLE Dest1$
трет и содержимое таблицы и названия столбцов.

В обоих вариантах остаются не тронутыми записи добавленные руками в хвост таблицы, а так же естественно дальше команды первого Execute SQL Task пакет не шел.


Также создал отдельно пакет состоящий из Execute SQL Task с командой
DROP TABLE Dest1$

Удалил все их файла EXCEL все кроме шапки. Сохранил изменения потом добавил одну строчку (1,2,3) и закрыл его.
Запустил пакет. В результате файл оказался пустой.

Получается следующее:
Если SSIS залил данные в файл EXCEL, то он как-то помечает конец таблички. И дальше этого символа команды
DROP TABLE
не идут.
При занесении данных руками такой символ "отсутствует".

Команды
DROP TABLE Dest1$
и
DROP TABLE Dest1$
упорядочить не получается. Не важно как они расположены первой будет удалятся таблица с названиями столбцов.

Если другой способ не SSIS синзронизации файла EXCEL и таблицы MSSQL?
11 дек 12, 11:08    [13611085]     Ответить | Цитировать Сообщить модератору
 Re: ssis обновление excel файла  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3104
чтобы получить корректный пакет для работы с эксель, воспользуйтесь мастером Экспорта-Импорта
В настройках не забудте указать "очищать данные ....". Результат работы мастера сохраните как пакет

Yagrus2
Если другой способ не SSIS синзронизации файла EXCEL и таблицы MSSQL?

но это не синхронизация - а получение данных с сервера

К сообщению приложен файл. Размер - 42Kb
11 дек 12, 11:28    [13611236]     Ответить | Цитировать Сообщить модератору
 Re: ssis обновление excel файла  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 912
HandKot, Такой вариант мне не подойдет.
Задача с SSIS возникла последующей причине

Есть пользователи, которые могут работать только с excel и просматривать отчеты в SSRS.
В EXCEL они должны будут заносить/редактировать плановые показатели по своим подразделениям. Причем список подразделений хранится на SQL сервере и может меняться со временем. Сервер ничего не знает про планы подразделений.
Плановые показатели нужно подрубить к отчетности SSRS.

Моя задумка была в следующем.
Каждую ночь забираю планы подразделений из EXCEl в таблицу SQL.
Потом смотрю историю переходов подразделений по пользователям. Делаю расчеты и корректировки, а потом перезаписываю EXCEL.

То есть EXCEL и SSIS должны играть роль средства, внесения изменений в базу.

Есть задумка попробовать использовать File System Task, но пока не разобрался с его настройками.
11 дек 12, 12:03    [13611526]     Ответить | Цитировать Сообщить модератору
 Re: ssis обновление excel файла  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3104
может тогда проще сделать макрос в экселе?
зачем городить огород?
11 дек 12, 13:30    [13612312]     Ответить | Цитировать Сообщить модератору
 Re: ssis обновление excel файла  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 912
HandKot
может тогда проще сделать макрос в экселе?
зачем городить огород?


Подскажите пожалуйста, как с помощью макроса писать в базу?
и запускать в определенное время?
11 дек 12, 15:04    [13613038]     Ответить | Цитировать Сообщить модератору
 Re: ssis обновление excel файла  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3104
Yagrus2
HandKot
может тогда проще сделать макрос в экселе?
зачем городить огород?


Подскажите пожалуйста, как с помощью макроса писать в базу?
и запускать в определенное время?


лучше обратиться в форум по эексклю
а так примерно
1. создается подключение, формируется строка запроса и выполняется
2. если максросом, то только события использовать

и "вернемся к нашим баранам"
если Вы все точно описали, что требуется, то SSIS должно Вам хватить за глаза, надо только все аккуратно сделать
Yagrus2
В обоих вариантах остаются не тронутыми записи добавленные руками в хвост таблицы, а так же естественно дальше команды первого Execute SQL Task пакет не шел.

может проблема, что записи остаются, в том, что неверно что-то указали?
у меня все чистится и удаляется
давайте по порядку

1. как уже говорил 13611236, вызовите мастер экспорта/импорта и выгрузите нужную табличку в эксель
2. сохраните результат мастера как пакет и начните работать с ним
11 дек 12, 15:23    [13613196]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить