ssis обновление excel файла / Microsoft SQL Server / Sql.ru

Добро пожаловать в форум, Guest >> Войти \| Регистрация \| Поиск \| Правила \|			В избранное \| Подписаться
Все форумы / Microsoft SQL Server

ssis обновление excel файла [new]
Yagrus2 Member Откуда: Москва Сообщений: 902	Здравствуйте. Есть 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

Откуда: Москва
Сообщений: 902

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 Сообщений: 3015	SSIS экспорт в Excel
10 дек 12, 08:21 [13604207] Ответить \| Цитировать Сообщить модератору

Re: ssis обновление excel файла [new]

Yagrus2
Member

Откуда: Москва
Сообщений: 902

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 Откуда: Москва Сообщений: 902	Хотя иконки получаются зеленые.
10 дек 12, 18:55 [13609058] Ответить \| Цитировать Сообщить модератору

Re: ssis обновление excel файла [new]
Yagrus2 Member Откуда: Москва Сообщений: 902	Ради эксперимента создал отдельно пакет состоящий из 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 Откуда: Москва Сообщений: 902	Провел еще эксперименты и выяснил. Допустим при первом запуске пакета выгрузилось N строк. И пусть потом пользователь внес еще 1 строку, то есть получилось всего в файле N+1 строка. При последующем запуске исходного пакета, запишется только N строк. При этом в процессе дебага Excel Destination окрасится в красный цвет. Вопрос: Я делаю неправильные настройки или это нормальное положение вещей для SSIS?
10 дек 12, 20:55 [13609458] Ответить \| Цитировать Сообщить модератору

Re: ssis обновление excel файла [new]

HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3015

Yagrus2
в результате лист Dest1 оказался пустой. Я предполагал, что произойдет удаление листа, а не его чистка.

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

Yagrus2

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

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

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

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

11 дек 12, 08:34 [13610405] Ответить | Цитировать Сообщить модератору

Re: ssis обновление excel файла [new]

HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3015

HandKot

Yagrus2

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

DROP TABLE Dest1
Go
DROP TABLE Dest1$
Go

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

11 дек 12, 08:59 [13610456] Ответить | Цитировать Сообщить модератору

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

Re: ssis обновление excel файла [new]

HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3015

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

предположение одно - разные отображения (наполнения) листа.
но лучше спросить в форуме по экселю, там лучше должны знать объектную модель оного

11 дек 12, 10:03 [13610675] Ответить | Цитировать Сообщить модератору

Re: ssis обновление excel файла [new]

Yagrus2
Member

Откуда: Москва
Сообщений: 902

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 Откуда: Москва Сообщений: 902	Yagrus2, На шаге4 получил такую картинку К сообщению приложен файл. Размер - 6Kb
11 дек 12, 10:14 [13610765] Ответить \| Цитировать Сообщить модератору

Re: ssis обновление excel файла [new]

Yagrus2
Member

Откуда: Москва
Сообщений: 902

HandKot

пропущено...

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

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
Сообщений: 3015

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

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

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

К сообщению приложен файл. Размер - 42Kb

11 дек 12, 11:28 [13611236] Ответить | Цитировать Сообщить модератору

Re: ssis обновление excel файла [new]
Yagrus2 Member Откуда: Москва Сообщений: 902	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 Сообщений: 3015	может тогда проще сделать макрос в экселе? зачем городить огород?
11 дек 12, 13:30 [13612312] Ответить \| Цитировать Сообщить модератору

Re: ssis обновление excel файла [new]

Yagrus2
Member

Откуда: Москва
Сообщений: 902

HandKot
может тогда проще сделать макрос в экселе? зачем городить огород?

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

11 дек 12, 15:04 [13613038] Ответить | Цитировать Сообщить модератору

Re: ssis обновление excel файла [new]

HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3015

Yagrus2

HandKot
может тогда проще сделать макрос в экселе? зачем городить огород?

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

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

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

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

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

1. как уже говорил 13611236, вызовите мастер экспорта/импорта и выгрузите нужную табличку в эксель
2. сохраните результат мастера как пакет и начните работать с ним

11 дек 12, 15:23 [13613196] Ответить | Цитировать Сообщить модератору

Все форумы / Microsoft SQL Server

Ссылка на сообщение

Ссылка (включая название темы)

Ссылка (URL)