Как получить доступ к источнику данных Excel из пакета SSIS, развернутого на 64-разрядном сервере?

Этот пример step-by-step предназначен для тех, кто может наткнуться на этот вопрос. В этом примере для выполнения задания используется SSIS 2005 и SQL Server 2005 64-bit edition server .

Ответ здесь концентрируется только на исправлении сообщения об ошибке, упомянутого в вопросе. В этом примере будут показаны шаги для повторного создания проблемы, а также причины возникновения проблемы, а затем способы ее устранения.

NOTE: я бы рекомендовал использовать опцию хранения значений конфигурации пакета в базе данных или использовать косвенную конфигурацию XML с помощью переменных окружения. Кроме того, шаги по созданию файла Excel будут выполнены с использованием шаблона, который затем будет архивирован путем перемещения в другую папку. Эти шаги не обсуждаются в этом посте. Как уже упоминалось ранее, цель этого поста-устранить ошибку.

Давайте перейдем к примеру. Я также написал об этом ответе в блоге, который можно найти по этой ссылке . Это тот же самый ответ.

Создайте пакет SSIS ( шаги для создания пакета SSIS). В этом примере используется BIDS 2005. Я назвал пакет в формате YYYYMMDD_hhmm в начале, а затем SO означает переполнение стека, а затем идентификатор вопроса SO и, наконец, описание. Я не говорю, что вы должны так называть свой пакет. Это для меня, чтобы легко сослаться на это позже. Обратите внимание, что у меня также есть источник данных под названием Adventure Works. Я буду использовать источник данных Adventure Works, который указывает на базу данных AdventureWorks, загруженную по этой ссылке . В этом примере используется база данных SQL Server 2008 R2. Смотрите скриншот #1 .

В базе данных AdventureWorks создайте хранимую процедуру с именем dbo.GetCurrency , используя приведенный ниже сценарий.

CREATE PROCEDURE [dbo].[GetCurrency]
AS
BEGIN
    SET NOCOUNT ON;
    SELECT 
    TOP 10      CurrencyCode
            ,   Name
            ,   ModifiedDate 
    FROM        Sales.Currency
    ORDER BY    CurrencyCode
END
GO

В разделе Диспетчер соединений пакета щелкните правой кнопкой мыши и выберите пункт Создать соединение из источника данных . В диалоговом окне Выбор источника данных выберите пункт Adventure Works и нажмите кнопку OK. Теперь вы должны увидеть источник данных Adventure Works в разделе диспетчеры соединений .

В разделе диспетчеры соединений пакета щелкните правой кнопкой мыши еще раз, но на этот раз выберите пункт новое соединение... . Это делается для создания соединения Excel. Добавить SSIS диспетчера подключений, выберите EXCEL . В Диспетчере соединений Excel введите путь C:\Temp\Template.xls . Когда мы развернем его на сервере, мы изменим этот путь. Я выбрал версию Excel Microsoft Excel 97-2005 и решил оставить checkbox первая строка имеет имена столбцов проверено так, чтобы создать файл Excel создается заголовки столбцов. Нажмите кнопку OK . Переименуйте соединение Excel в Excel, просто чтобы сохранить его простым. Смотрите скриншоты #2 - #7 .

В пакете создайте следующую переменную. Смотрите скриншот #8 .

• SQLGetData: эта переменная имеет тип String. Это будет содержать инструкцию выполнения хранимой процедуры. В этом примере используется значение EXEC dbo.GetCurrency

Снимок экрана #9 показывает выходные данные инструкции выполнения хранимой процедуры EXEC dbo.GetCurrency

На вкладке поток управления пакета поместите символ Data Flow task и назовите его экспорт в Excel. Смотрите скриншот #10 .

Дважды щелкните на задаче поток данных, чтобы перейти на вкладку поток данных.

На вкладке поток данных поместите OLE DB Source для подключения к данным сервера SQL для извлечения данных из хранимой процедуры и назовите его SQL. Дважды щелкните источник OLE DB, чтобы открыть редактор источников OLE DB. В разделе Диспетчер соединений выберите Adventure Works из диспетчера соединений OLE DB, выберите команду SQL из переменной в режиме доступа к данным и выберите переменную User::SQLGetData из раскрывающегося списка имя переменной. В разделе столбцы убедитесь, что имена столбцов сопоставлены правильно. Нажмите кнопку OK, чтобы закрыть редактор исходного кода OLE DB. См. скриншоты #11 и #12 .

На вкладке поток данных поместите Excel Destination , чтобы вставить данные в файл Excel, и назовите его Excel. Дважды щелкните пункт назначения Excel, чтобы открыть редактор назначения Excel. В разделе Диспетчер соединений выберите Excel в диспетчере соединений OLE DB и выберите таблица или представление в режиме доступа к данным. На данный момент у нас нет Excel, потому что при создании диспетчера соединений Excel мы просто указали путь, но никогда не создавали файл. Следовательно, в раскрывающемся имени листа Excel не будет никаких значений. Итак, нажмите кнопку New... (вторая новая), чтобы создать новый лист Excel. В окне создать таблицу BIDS автоматически предоставляет лист создания на основе входящего источника данных. Вы можете изменить значения в соответствии с вашими предпочтениями. Я просто нажму OK, сохранив значение по умолчанию. Имя листа будет заполнено в раскрывающемся списке имя листа Excel. Имя листа берется из имени задачи, здесь в данном случае адресат Excel, который мы назвали как Excel. В разделе сопоставления убедитесь, что имена столбцов сопоставлены правильно. Нажмите кнопку OK, чтобы закрыть редактор назначения Excel. Смотрите скриншоты #13 - #16 .

Как только задача потока данных настроена, она должна выглядеть так, как показано на скриншоте #17 .

Выполните пакет, нажав клавишу F5. Скриншоты #18 - #21 показывают успешное выполнение пакета как в потоке управления, так и в задаче потока данных. Кроме того, файл генерируется в пути C:\Temp\Template.xls , предоставленном в соединении Excel, и данные, показанные в выходных данных выполнения хранимой процедуры, совпадают с данными, записанными в файл.

Пакет разработан на моем локальном компьютере в папке path C:\Learn\Learn.VS2005\Learn.SSIS . Теперь нам нужно развернуть файлы на сервере, на котором размещена 64-разрядная версия сервера SQL, чтобы запланировать задание. Таким образом, папка на сервере будет D:\SSIS\Practice . Скопируйте файл пакета (.dtsx) с локального компьютера и вставьте его в папку сервера. Кроме того, чтобы пакет работал правильно, мы должны иметь электронную таблицу Excel, присутствующую на сервере. В противном случае проверка завершится ошибкой. Обычно я создаю папку шаблона,которая будет содержать пустой файл электронной таблицы Excel, соответствующий выходу. Позже, во время выполнения, я изменю выходной путь Excel в другое место, используя конфигурацию пакета. Для этого примера я собираюсь сохранить его простым. Итак, давайте скопируем файл Excel, созданный на локальной машине по пути C:\Temp\Template.xls , в расположение сервера D:\SSIS\Practice . Я хочу, чтобы задание SQL сгенерировало файл с именем Currencies.xls. Итак, переименуйте файл Template.xls в Currencies.xls . Смотрите скриншот #22 .

Чтобы показать, что я действительно собираюсь запустить задание на сервере в 64-разрядной версии SQL Server, я выполнил команду SELECT @@version на сервере SQL, и скриншот #23 показывает результаты.

Мы будем использовать утилиту Execute Package Utility (dtexec.exe) для генерации параметров командной строки. Войдите на сервер, который будет запускать пакет SSIS в задании SQL. Дважды щелкните на файле пакета, это приведет к запуску утилиты выполнения пакета. В разделе Общие выберите файловая система из источника пакета. Нажмите на многоточие и перейдите к пути пакета. В разделе диспетчеры соединений выберите Excel и измените путь внутри файла Excel с C:\Temp\Template.xls на D:\SSIS\Practice\Currencies.xls. Изменения, внесенные в утилиту, будут генерировать командную строку соответственно в разделе командной строки. В разделе Командная строка скопируйте командную строку, содержащую все необходимые параметры. Мы не собираемся выполнять этот пакет отсюда. Нажмите Кнопку Закрыть . Смотрите скриншоты #24 - #26 .

Далее нам нужно настроить задание для запуска пакета SSIS. Мы не можем выбрать тип пакета служб интеграции сервера SQL, поскольку он будет работать под 64-разрядной версией и не найдет поставщика подключения Excel. Итак, мы должны запустить его как тип задания Operating System (CmdExec) . Перейдите в SQL Server Management Studio и подключитесь к ядру базы данных. Разверните узел SQL Server Agent и щелкните правой кнопкой мыши узел задания. Выберите Новое Задание.... В разделе Общие окна свойств задания укажите имя задания как 01_SSIS_Export_To_Excel, владельцем которого будет пользователь, создающий задание. У меня есть категория с именем SSIS, поэтому я выберу ее, но по умолчанию категория [Uncategorized (Local)] и предоставлю краткое описание. В разделе шаги Нажмите кнопку Создать. ... Это приведет к появлению свойств шага задания. В разделе Общие свойств шага задания укажите имя шага как экспорт в Excel, выберите тип Operating system (CmdExec), оставьте учетную запись запуска от имени по умолчанию как SQL учетная запись службы агента сервера и введите следующую команду. Нажмите кнопку OK. В окне новое задание щелкните OK. Смотрите скриншоты #27 - #31 .

C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe /FILE 
"D:\SSIS\Practice\20110723_1015_SO_21448_Excel_64_bit_Error.dtsx" 
/CONNECTION Excel;"\"Provider=Microsoft.Jet.OLEDB.4.0;Data 
Source=D:\SSIS\Practice\Currencies.xls;Extended Properties=""EXCEL 8.0;HDR=YES"";\""  
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING EWCDI

Новое задание должно появиться в узле SQL Server Agent –> Jobs. Щелкните правой кнопкой мыши на вновь созданном задании 01_SSIS_Export_To_Excel и выберите запустить задание на шаге..., это позволит начать выполнение задания. Задание завершится неудачей, как и ожидалось, потому что это является контекстом данной проблемы. Нажмите кнопку Закрыть, чтобы закрыть диалоговое окно запуск заданий. Смотрите скриншоты #32 и #33 .

Давайте посмотрим, что произошло. Перейдите к узлу SQL Server Agent and Jobs. Щелкните правой кнопкой мыши на задании 01_SSIS_Export_To_Excel и выберите пункт просмотр истории. Это приведет к появлению окна просмотра файлов журнала. Вы можете заметить, что задание не удалось выполнить. Разверните узел рядом с красным крестом и нажмите на строку, которая шаг ID значение 1. В нижнем разделе вы можете увидеть сообщение об ошибке Option “8.0;HDR=YES’;” is not valid. Нажмите кнопку Закрыть, чтобы закрыть окно просмотра файлов журнала. Смотрите скриншоты #34 и #35 .

Теперь щелкните правой кнопкой мыши на задании и выберите Свойства, чтобы открыть Свойства задания. Вы также можете дважды щелкнуть по заданию, чтобы открыть окно свойств задания. Нажмите на шаги в левом разделе. и нажмите кнопку Изменить. Замените команду следующей командой и нажмите кнопку OK. Щелкните OK в свойствах задания, чтобы закрыть окно. Щелкните правой кнопкой мыши на задании 01_SSIS_Export_To_Excel и выберите запустить задание на шаге..., после чего начнется выполнение задания. Задание не будет успешно выполнено. Нажмите кнопку Закрыть, чтобы закрыть диалоговое окно запуск заданий. Давайте взглянем на историю. Щелкните правой кнопкой мыши на задании 01_SSIS_Export_To_Excel и выберите пункт просмотр истории. Это приведет к появлению окна просмотра файлов журнала. Вы можете заметить, что задание было успешно выполнено во время второго запуска. Разверните узел рядом с зеленым крестиком галочки и нажмите на строку, которая шаг ID имеет значение 1. В нижнем разделе вы можете увидеть сообщение о том, что шаг выполнен успешно. Нажмите кнопку Закрыть, чтобы закрыть окно просмотра файлов журнала. Файл D:\SSIS\Practice\Currencies.xls будет успешно заполнен данными. Если вы успешно выполните задание несколько раз, данные будут добавлены в файл, и вы найдете больше данных. Как я уже упоминал ранее, это не правильный способ создания файлов. Этот пример был создан для демонстрации исправления этой проблемы. Смотрите скриншоты #36 - #38 .

Снимок экрана #39 показывает различия между рабочими и нерабочими аргументами командной строки. Тот, что справа, - это рабочая командная строка, а левый-неверный. Для исправления ошибки потребовалась еще одна двойная кавычка с обратной косой чертой escape-последовательности. Могут быть и другие способы исправить это хорошо, но этот вариант, кажется, работает.

Таким образом, пример продемонстрировал способ устранения проблемы аргумента командной строки при обращении к источнику данных Excel из пакета SSIS, развернутого на 64-разрядном сервере.

Надеюсь, это кому-то поможет.

Скриншоты:

#1: Решение_эксплорер

#2: New_Connection_Data_Source

#3: Select_Data_Source

#4: New_Connection

#5: Add_SSIS_Connection_Manager

#6: Excel_Connection_Manager

#7: Connection_Managers

#8: переменных

#9: Stored_Procedure_Output

#10: Control_Flow

#11: OLE_DB_Source_Connections_Manager

#12: OLE_DB_Source_Columns

#13: Excel_Destination_Editor_New

#14: Excel_Destination_Create_Table

#15: Excel_Destination_Edito

#16: Excel_Destination_Mappings

#17: Data_Flow

#18: Successful_Package_Execution_Control

#19: Successful_Package_Execution_Data_flow

#20: C_Temp_File_Created

#21: Data_Populated

#22: File_On_Server

#23: SQL_Server_Version

#24: Execute_Package_Utility_General

#25: Execute_Package_Utility_Connection_managers

#26: Execute_Package_Utility_Command_line

#27: Job_New_Job

#28: New_Job_General

#29: New_Job_Step

#30: New_Job_Step_General

#31: New_Job_Steps_Added

#32: Job_Start_Job_at_Step

#33: SQL_Job_Execution_Failure

#34: View_History

#35: SQL_Job_Error_Message

#36: SQL_Job_Execution_Success

#37: SQL_Job_Success_Message

#38: Excel_File_Generated

#39: Command_Comparison

Вы можете использовать соединение Excel в 64-битной среде. Перейдите к свойствам конфигурации пакета.

Отладка - > параметры отладки - > Run64BtRuntime - > изменить на False Кроме того, если вы используете агент SQL, перейдите к свойствам шага задания и затем проверьте 32-разрядную среду выполнения.

Примечание: это относится только к отладке в Visual Studio...

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

1. Определите переменную @ExcelPath .
2. Используйте свойство выражение соединения для построения строки соединения-пример: "Data Source=" + @[User::FilePath] + ";Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBASE IV;"
3. Присвойте значение @ExcelPath в конфигурации пакета.

Внимательно посмотрите на строку подключения выше. Он взят из рабочего пакета. Я не уверен в этом, но, возможно, вам вообще не нужны никакие кавычки (те, что выше, существуют только потому, что их требует редактор выражений).

У меня также были некоторые проблемы с SSIS на 64-битном SQL Server 2005. Этот пост из моего блога не отвечает на ваш вопрос, но он несколько связан, поэтому я размещаю ссылку.

Нет 64-разрядного поставщика Jet OLEDB, поэтому вы не можете получить доступ к файлам Excel из 64-разрядного SSIS.

Однако вы можете использовать 32-разрядный SSIS даже на 64-разрядном сервере. Он уже установлен, когда вы установили 64-разрядную версию, и все, что вам нужно сделать, это запустить 32-разрядный DTEXEC.EXE - установленный Program Files (x86)\Microsoft Sql Server\90\Dts\Binn (замените 90 на 100 , если вы используете SSIS 2008).

Я вроде как сделал то же, что и доктор зим, но я скопировал файл DTExec C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe в папку C:\Program Files\Microsoft SQL Server\90\DTS\Binn\ , но назвал 32-битный файл DTExec32.exe

затем я смог запустить свой сценарий SSIS через сохраненный proc:

set @params = '/set \package.variables[ImportFilename].Value;"\"' + @FileName + '\"" '
set @cmd =  'dtexec32 /SQ "' + @packagename + ' ' + @params + '"'
--DECLARE @returncode int
exec master..xp_cmdshell @cmd
--exec @returncode = master..xp_cmdshell @cmd
--select @returncode