Связанный сервер ms sql 2008 подключить файл excel
This article describes the complete steps for Microsoft Excel data import to SQL Server using linked servers technique.
The article describes the steps for all modern platforms:
- Microsoft SQL Server 2005-2016 on the x86/x64 platform.
- Microsoft Excel 2003-2016 files like *.xls, *.xlsx, *.xlsm, *.xlsb.
You can develop amazing Microsoft Excel applications for working with Microsoft SQL Server using database development skills only!
Connect to tables, views, and stored procedures, edit the data and save it back to a database.
Add features to your Microsoft Excel applications step by step configuring apps via SQL.
Table of Contents
The Basics of Excel Data Import to SQL Server Using Linked Servers
To import data from Microsoft Excel 2003 files to 32-bit SQL Server the Microsoft.Jet.OLEDB.4.0 provider can be used. Use the T-SQL code like this to add a linked server to Excel 2003 workbook:
To import data from Microsoft Excel 2007 to 32-bit SQL Server or from any Microsoft Excel files to 64-bit SQL Server the Microsoft.ACE.OLEDB.12.0 provider should be used. Use the T-SQL code like this:
IMEX=1 defines to import all Excel column data including data of mixed types.
HDR=YES defines that Excel data contain column headers.
The way to modify a linked server is to drop and create it again. Use the T-SQL code like this:
There are two ways to use linked server data. The first way is like this:
and the second one is the use of the OPENQUERY function:
The use of the OPENQUERY function is more flexible because queries can contain Excel ranges unlike the entire sheet in the first case.
Configuration Steps for Excel Data Import to SQL Server Using Linked Servers
Install Microsoft.ACE.OLEDB.12.0 driver
To import Excel 2007-2016 files to SQL Server the Microsoft.ACE.OLEDB.12.0 driver should be installed.
To download the driver use the following link:
Don't worry about "Access" in the name.
Warning! x64 driver cannot be installed if Microsoft Office 2007-2016 x86 is already installed!
So, there is no way to import Excel data to SQL Server x64 using Linked Servers technique on a machine with Microsoft Office x86!
The SQL Server Error Message if Microsoft.ACE.OLEDB.12.0 is not installed
Grant rights to TEMP directory
This step is required only for 32-bit SQL Server with any OLE DB provider.
The main problem is that an OLE DB provider creates a temporary file during the query in the SQL Server temp directory using credentials of a user who run the query.
The default directory for SQL Server is a default directory for SQL Server service account.
If SQL Server is run under the Network Service account the temp directory is like:
C:\Windows\ServiceProfiles\ NetworkService\AppData\Local\Temp
If SQL Server is run under the Local Service account the temp directory is like:
C:\Windows\ServiceProfiles\ LocalService\AppData\Local\Temp
Microsoft recommends two ways for the solution:
- A change of SQL Server TEMP directory and a grant of full rights for all users to this directory.
- Grant of read/write rights to the current SQL Server TEMP directory.
Usually, only a few accounts are used for import operations. So, we can just add the rights for these accounts.
For example, icacls utility can be used for the rights setup:
if SQL Server is started under Network Service and login "vs" is used to run the queries.
The SQL Server Error Message if a user has no rights for SQL Server TEMP directory
or the message for Microsoft.ACE.OLEDB.12.0 provider:
Configure ACE OLE DB properties
This step is required only if the Microsoft.ACE.OLEDB.12.0 provider is used.
Use the following T-SQL code:
The SQL Server Error Messages if OLE DB properties are not configured
Configure linked servers
The configuring of linked servers is discussed in the Basics topic.
Use the T-SQL code like this for Excel 2003 linked servers:
Use the T-SQL code like this for Excel 2007 linked servers or on SQL Server x64:
How-To: Import Excel 2003 to SQL Server x86
Step 1. Grant rights to TEMP directory
The most commonly used paths:
C:\Windows\ServiceProfiles\ NetworkService\AppData\Local\Temp
C:\Windows\ServiceProfiles\ LocalService\AppData\Local\Temp
Step 2. Configure linked server using Microsoft.Jet.OLEDB.4.0 provider
How-To: Import Excel 2007 to SQL Server x86
Step 1. Install the 32-bit Microsoft.ACE.OLEDB.12.0 driver
Step 2. Grant rights to TEMP directory
The most commonly used paths:
C:\Windows\ServiceProfiles\ NetworkService\AppData\Local\Temp
C:\Windows\ServiceProfiles\ LocalService\AppData\Local\Temp
Step 3. Configure ACE OLE DB properties
Step 4. Configure linked server using Microsoft.ACE.OLEDB.12.0 provider
How-To: Import Excel 2003/2007 to SQL Server x64
Step 1. Install 64-bit Microsoft.ACE.OLEDB.12.0 driver
Step 2. Configure ACE OLE DB properties
Step 3. Configure linked server using Microsoft.ACE.OLEDB.12.0 provider
Using the described techniques you can import data from Microsof Excel 2003-2016 to SQL Server 2005-2016 on the 32-bit or 64-bit platform.
Импортировать данные из файлов Excel в SQL Server или базу данных SQL Azure можно несколькими способами. Некоторые методы позволяют импортировать данные за один шаг непосредственно из файлов Excel. Для других методов необходимо экспортировать данные Excel в виде текста (CSV-файла), прежде чем их можно будет импортировать.
В этой статье перечислены часто используемые методы и содержатся ссылки для получения дополнительных сведений. Однако в ней не указано полное описание таких сложных инструментов и служб, как SSIS или Фабрика данных Azure. Дополнительные сведения об интересующем вас решении доступны по ссылкам ниже.
Список методов
Существует несколько способов импортировать данные из Excel. Для использования некоторых из этих инструментов может понадобиться установка SQL Server Management Studio (SSMS).
Для импорта данных из Excel можно использовать следующие средства:
Сначала экспортировать в текст (SQL Server и база данных SQL) | Непосредственно из Excel (только в локальной среде SQL Server) |
---|---|
Мастер импорта неструктурированных файлов | мастер импорта и экспорта SQL Server |
Инструкция BULK INSERT | Службы SQL Server Integration Services |
BCP | Функция OPENROWSET |
Мастер копирования (Фабрика данных Azure) | |
Фабрика данных Azure. |
Если вы хотите импортировать несколько листов из книги Excel, обычно нужно запускать каждое из этих средств отдельно для каждого листа.
Дополнительные сведения см. в разделе Ограничения и известные проблемы загрузки данных в файлы Excel или из них.
Мастер импорта и экспорта
Импортируйте данные напрямую из файлов Excel с помощью мастера импорта и экспорта SQL Server. Также можно сохранить параметры в виде пакета SQL Server Integration Services (SSIS), который можно настроить и использовать повторно в будущем.
В SQL Server Management Studio подключитесь к экземпляру SQL Server Компонент Database Engine.
Разверните узел Базы данных.
Щелкните базу данных правой кнопкой мыши.
Наведите указатель мыши на пункт Задачи.
Выберите Импортировать данные или Экспортировать данные:
Дополнительные сведения см. в следующих статьях:
Integration Services (SSIS)
Если вы работали с SQL Server Integration Services (SSIS) и не хотите запускать мастер импорта и экспорта SQL Server, создайте пакет SSIS, который использует в потоке данных источник "Excel" и назначение "SQL Server".
Дополнительные сведения см. в следующих статьях:
Чтобы научиться создавать пакеты SSIS, см. руководство How to Create an ETL Package (Как создать пакет ETL).
OPENROWSET и связанные серверы
В базе данных SQL Azure невозможно импортировать данные непосредственно из Excel. Сначала необходимо экспортировать данные в текстовый файл (CSV).
Поставщик ACE (прежнее название — поставщик Jet), который подключается к источникам данных Excel, предназначен для интерактивного клиентского использования. Если поставщик ACE используется на сервере SQL Server, особенно в автоматизированных процессах или процессах, выполняющихся параллельно, вы можете получить непредвиденные результаты.
Распределенные запросы
Импортируйте данные напрямую из файлов Excel в SQL Server с помощью функции Transact-SQL OPENROWSET или OPENDATASOURCE . Такая операция называется распределенный запрос.
В базе данных SQL Azure невозможно импортировать данные непосредственно из Excel. Сначала необходимо экспортировать данные в текстовый файл (CSV).
Перед выполнением распределенного запроса необходимо включить параметр ad hoc distributed queries в конфигурации сервера, как показано в примере ниже. Дополнительные сведения см. в статье ad hoc distributed queries Server Configuration Option (Параметр конфигурации сервера "ad hoc distributed queries").
В приведенном ниже примере кода данные импортируются из листа Excel Sheet1 в новую таблицу базы данных с помощью OPENROWSET .
Ниже приведен тот же пример с OPENDATASOURCE .
Чтобы добавить импортированные данные в существующую таблицу, а не создавать новую, используйте синтаксис INSERT INTO . SELECT . FROM . вместо синтаксиса SELECT . INTO . FROM . из предыдущих примеров.
Для обращения к данным Excel без импорта используйте стандартный синтаксис SELECT . FROM . .
Дополнительные сведения о распределенных запросах см. в указанных ниже разделах.
-
(Распределенные запросы по-прежнему поддерживаются в SQL Server 2019, но документация по этой функции не обновляется.)
Связанные серверы
Кроме того, можно настроить постоянное подключение от SQL Server к файлу Excel как к связанному серверу. В примере ниже данные импортируются из листа Excel Data на существующем связанном сервере EXCELLINK в новую таблицу базы данных SQL Server с именем Data_ls .
Вы можете создать связанный сервер в SQL Server Management Studio или запустить системную хранимую процедуру sp_addlinkedserver , как показано в примере ниже.
Дополнительные сведения о связанных серверах см. в указанных ниже разделах.
Примеры и дополнительные сведения о связанных серверах и распределенных запросах см. указанных ниже разделах.
Предварительное требование — сохранение данных Excel как текста
Чтобы использовать другие методы, описанные на этой странице (инструкцию BULK INSERT, средство BCP или фабрику данных Azure), сначала экспортируйте данные Excel в текстовый файл.
В Excel последовательно выберите Файл | Сохранить как и выберите как целевой тип файла Текст (разделитель — табуляция) (*.txt) или CSV (разделитель — запятая) (*.csv) .
Если вы хотите экспортировать несколько листов из книги, выполните эту процедуру для каждого листа. Команда Сохранить как экспортирует только активный лист.
Чтобы оптимизировать использование средств импорта, сохраняйте листы, которые содержат только заголовки столбцов и строки данных. Если сохраненные данные содержат заголовки страниц, пустые строки, заметки и пр., позже при импорте данных вы можете получить непредвиденные результаты.
Мастер импорта неструктурированных файлов
Импортируйте данные, сохраненные как текстовые файлы, выполнив инструкции на страницах мастера импорта неструктурированных файлов.
Как было описано выше в разделе Предварительное требование, необходимо экспортировать данные Excel в виде текста, прежде чем вы сможете импортировать их с помощью мастера импорта неструктурированных файлов.
Дополнительные сведения о мастере импорта неструктурированных файлов см. в разделе Мастер импорта неструктурированных файлов в SQL.
Команда BULK INSERT
BULK INSERT — это команда Transact-SQL, которую можно выполнить в SQL Server Management Studio. В приведенном ниже примере данные загружаются из файла Data.csv с разделителями-запятыми в существующую таблицу базы данных.
Как было описано выше в разделе Предварительное требование, необходимо экспортировать данные Excel в виде текста, прежде чем вы сможете использовать BULK INSERT для их импорта. BULK INSERT не может считывать файлы Excel напрямую. С помощью команды BULK INSERT можно импортировать CSV-файл, который хранится локально или в хранилище BLOB-объектов Azure.
Дополнительные сведения и примеры для SQL Server и базы данных SQL см. в следующих разделах:
Средство BCP
BCP — это программа, которая запускается из командной строки. В приведенном ниже примере данные загружаются из файла Data.csv с разделителями-запятыми в существующую таблицу базы данных Data_bcp .
Как было описано выше в разделе Предварительное требование, необходимо экспортировать данные Excel в виде текста, прежде чем вы сможете использовать BCP для их импорта. BCP не может считывать файлы Excel напрямую. Используется для импорта в SQL Server или базу данных SQL из текстового файла (CSV), сохраненного в локальном хранилище.
Для текстового файла (CSV), хранящегося в хранилище BLOB-объектов Azure, используйте BULK INSERT или OPENROWSET. Примеры см. в разделе Пример.
Дополнительные сведения о программе BCP см. в указанных ниже разделах.
Мастер копирования (ADF)
Импортируйте данные, сохраненные как текстовые файлы, с помощью пошаговой инструкции мастера копирования Фабрики данных Azure (ADF).
Как было описано выше в разделе Предварительное требование, необходимо экспортировать данные Excel в виде текста, прежде чем вы сможете использовать фабрику данных Azure для их импорта. Фабрика данных не может считывать файлы Excel напрямую.
Дополнительные сведения о мастере копирования см. в указанных ниже разделах.
Фабрика данных Azure
Если вы уже работали с фабрикой данных Azure и не хотите запускать мастер копирования, создайте конвейер с действием копирования из текстового файла в SQL Server или Базу данных SQL Azure.
Как было описано выше в разделе Предварительное требование, необходимо экспортировать данные Excel в виде текста, прежде чем вы сможете использовать фабрику данных Azure для их импорта. Фабрика данных не может считывать файлы Excel напрямую.
Дополнительные сведения об использовании этих источников и приемников фабрики данных см. в указанных ниже разделах.
Чтобы научиться копировать данные с помощью фабрики данных Azure, см. указанные ниже разделы.
Распространенные ошибки
Эта ошибка возникает, так как не установлен поставщик OLE DB. Установите его через Распространяемый пакет ядра СУБД Microsoft Access 2010. Не забудьте установить 64-разрядную версию, если Windows и SQL Server — 64-разрядные.
Полный текст ошибки.
Не удалось создать экземпляр поставщика OLE DB "Microsoft.ACE.OLEDB.12.0" для связанного сервера "(null)".
Это означает, что Microsoft OLEDB не был настроен должным образом. Чтобы устранить проблему, выполните приведенный ниже код Transact-SQL.
Полный текст ошибки.
32-разрядный поставщик OLE DB "Microsoft.ACE.OLEDB.12.0" не может быть загружен в процессе на 64-разрядной версии SQL Server.
Это происходит, когда 32-разрядная версия поставщика OLD DB устанавливается вместе с 64-разрядной версией SQL Server. Чтобы устранить эту проблему, удалите 32-разрядную версию и вместо нее установите 64-разрядную версию поставщика OLE DB.
Полный текст ошибки.
Поставщик OLE DB "Microsoft.ACE.OLEDB.12.0" для связанного сервера "(null)" сообщил об ошибке.
Не удалось проинициализировать объект источника данных поставщика OLE DB "Microsoft.ACE.OLEDB.12.0" для связанного сервера "(null)".
Обе эти ошибки обычно указывают на ошибку разрешений между процессом SQL Server и файлом. Убедитесь, что учетная запись, с которой выполняется служба SQL Server, имеет разрешение на полный доступ к файлу. Мы не рекомендуем импортировать файлы с настольного компьютера.
В этой статье показано, как подключаться к источникам данных Microsoft Excel со страницы Выбор источника данных или Выбор назначения в мастере импорта и экспорта SQL Server.
На следующем снимке экрана показан пример подключения к книге Microsoft Excel.
Для подключения к файлам Excel может потребоваться скачать и установить дополнительные файлы. Дополнительные сведения см. в разделе Получение файлов, необходимых для подключения к Excel.
Дополнительные сведения о подключении к файлам Excel, а также об ограничениях и известных проблемах, связанных с загрузкой данных в файлы этого приложения и из них, см. в разделе Загрузка данных в приложение Excel или из него с помощью служб SQL Server Integration Services (SSIS).
Указываемые параметры
Параметры подключения для этого поставщика данных одинаковы независимо от того, является ли Excel источником или назначением. Таким образом, на страницах Выбор источника данных и Выбор назначения мастера отображаются одинаковые параметры.
Путь к файлу Excel
Укажите полный путь и имя для файла Excel. Пример:
- Для файла на локальном компьютере C:\MyData.xlsx.
- Для файла в общей сетевой папке \\Sales\Database\Northwind.xlsx.
или нажмите Обзор.
Обзор
Выберите электронную таблицу с помощью диалогового окна Открыть.
Мастер не может открыть защищенный паролем файл Excel.
Версия Excel
Выберите версию Excel для исходной или целевой рабочей книги.
Первая строка содержит имена столбцов
Укажите, содержит ли первая строка данных имена столбцов.
- Если данные содержат имена столбцов, но этот параметр включен, мастер рассматривает первую строку исходных данных как имена столбцов.
- Если данные содержат имена столбцов, но этот параметр отключен, мастер рассматривает строку имен столбцов как первую строку данных.
Если указать, что в данных отсутствуют имена столбцов, мастер внутренним образом использует F1, F2 и т. д. в качестве таких заголовков.
Excel не отображается в списке источников данных
Если вы не видите Excel в списке источников данных, определите, не используете ли вы 64-разрядный мастер? Поставщики для Excel и Access обычно 32-разрядные и поэтому не отображаются в 64-разрядном мастере. Запустите 32-разрядный мастер.
Чтобы использовать 64-разрядную версию мастера экспорта и импорта SQL Server, нужно установить SQL Server. SQL Server Data Tools (SSDT) и SQL Server Management Studio (SSMS) являются 32-разрядными приложениями и устанавливают только 32-разрядные файлы, включая 32-разрядную версию мастера.
В этой статье приводится информация о подключении, которую вы должны предоставить, и необходимые настройки подключения для импорта данных из Excel и экспорта данных в Excel с помощью SQL Server Integration Services (SSIS).
В следующих разделах содержится вся информация, которая потребуется для успешной работы с приложением Excel из служб SSIS, а также для понимания и устранения распространенных проблем:
Средства, которые можно использовать.
Файлы, которые вам потребуются.
Сведения о подключении, которые необходимо предоставить, и параметры, которые необходимо настроить для загрузки данных в/из Excel с использованием SSIS.
-
в качестве источника данных.
- Укажите имя файла Excel и путь к нему.
- Выберите версию Excel.
- Укажите, содержит ли первая строка имена столбцов.
- Укажите лист или диапазон, который содержит данные.
Известные проблемы и ограничения.
- Проблемы с типами данных.
- Проблемы с импортом.
- Проблемы с экспортом.
Средства, которые можно использовать
Вы можете импортировать и экспортировать данные в Excel с использованием SSIS при помощи любого из следующих инструментов.
SQL Server Integration Services (SSIS) . Создайте пакет SSIS, настроив для него Excel в качестве источника или назначения с помощью диспетчера подключений Excel. (В этой статье не рассматривается создание пакетов SSIS.)
Получение файлов, необходимых для подключения к Excel
Прежде чем импортировать или экспортировать данные в Excel с использованием SSIS, вам может потребоваться скачать компоненты подключения для Excel, если они еще не установлены. По умолчанию компоненты подключений для Excel не устанавливаются.
Используйте таблицу из статьи Не удалось использовать интерфейсы Access ODBC, OLEDB или DAO за пределами Office Click-to-Run, чтобы понять, нужны ли дополнительные компоненты для вашей среды.
Примечание. Системные драйверы Office поддерживаются только в определенных случаях, конкретные руководства см. в статье Рекомендации по автоматизации Office на стороне сервера.
Назначение Excel в качестве источника данных
Сначала необходимо указать, что вам требуется установить подключение к Excel.
В службах SSIS
В службах SSIS создайте диспетчер соединений Excel для подключения к файлу источника или назначения Excel. Создать диспетчер соединений можно несколькими способами:
Щелкните правой кнопкой в области Диспетчеры соединений и выберите команду Создать соединение. В диалоговом окне Добавление диспетчера соединений со службами SSIS выберите EXCEL и затем Добавить.
В меню Службы SSIS выберите Создать соединение. В диалоговом окне Добавление диспетчера соединений со службами SSIS выберите EXCEL и затем Добавить.
Диспетчер соединений необходимо создавать в то время, как вы настраиваете источник Excel или назначение Excel на странице Диспетчер соединений в редакторе источника Excel или в редакторе назначения Excel.
В мастере импорта и экспорта SQL Server
В мастере импорта и экспорта на странице Выбор источника данных или Выбор назначения выберите Microsoft Excel в списке Источник данных.
Если вы не видите Excel в списке источников данных, убедитесь, что используется 32-разрядная версия мастера. Компоненты подключений для Excel обычно представляют собой 32-разрядные файлы и не отображаются в 64-разрядной версии мастера.
Файл Excel и путь к нему
В первую очередь необходимо предоставить имя файла Excel и путь к нему. Это можно сделать в редакторе диспетчера соединений Excel в пакете служб SSIS либо на странице Выбор источника данных или Выбор назначения в мастере импорта и экспорта.
Введите имя файла и путь к нему в следующем формате:
Для файла на локальном компьютере: C:\TestData.xlsx.
Для файла в общей сетевой папке: \\Sales\Data\TestData.xlsx.
Также можно нажать кнопку Обзор и выбрать электронную таблицу в диалоговом окне Открыть.
Подключить защищенный паролем файл Excel нельзя.
Версия Excel
Далее следует предоставить сведения о версии файла Excel. Это можно сделать в редакторе диспетчера соединений Excel в пакете служб SSIS либо на странице Выбор источника данных или Выбор назначения в мастере импорта и экспорта.
Выберите версию Microsoft Excel, в которой был создан файл, или другую совместимую версию. Например, если вам не удалось установить компоненты подключений для версии 2016, вы можете установить компоненты для версии 2010 и выбрать в этом списке вариант Microsoft Excel 2007-2010.
Если у вас установлены старые версии компонентов подключений, вы не сможете выбрать более новые версии Excel в этом списке. Список Версия Excel содержит все версии Excel, поддерживаемые службами SSIS. Наличие элементов в этом списке не означает, что необходимые компоненты подключений были установлены. Например, вариант Microsoft Excel 2016 будет присутствовать в этом списке даже в том случае, если у вас не установлены компоненты подключений для версии 2016.
Первая строка содержит имена столбцов
Если вы импортируете данные из Excel, далее необходимо указать, содержит ли первая строка данных имена столбцов. Это можно сделать в редакторе диспетчера соединений Excel в пакете служб SSIS или на странице Выбор источника данных в мастере импорта и экспорта.
- Если этот параметр отключен, поскольку в данных источника отсутствуют имена столбцов, мастер будет использовать в качестве заголовков столбцов значения F1, F2 и т. д.
- Если данные содержат имена столбцов, но этот параметр отключен, мастер импортирует строку имен столбцов как первую строку данных.
- Если данные не содержат имена столбцов, но этот параметр включен, мастер использует первую строку данных источника как имена столбцов. В этом случае первая строка в данных источника не включается в сами данные.
Если вы экспортируете данные из Excel и включили этот параметр, первая строка экспортированных данных будет включать имена столбцов.
Листы и диапазоны
В качестве источника или назначения для данных можно использовать три типа объектов Excel: лист, именованный диапазон или неименованный диапазон ячеек, который задается с помощью адреса.
Лист Чтобы указать лист, добавьте в конец имени листа символ $ и окружите строку разделителями, например [Листt1$] . Также можно выполнить поиск имени, заканчивающегося символом $ , в списке существующих таблиц и представлений.
Именованный диапазон Чтобы указать именованный диапазон, используйте имя диапазона, например Мой_диапазон. Также можно выполнить поиск имени, не заканчивающегося символом $ , в списке существующих таблиц и представлений.
Неименованный диапазон Чтобы указать диапазон ячеек, которым не были заданы имена, добавьте символ $ после имени листа, добавьте спецификацию диапазона и окружите строку разделителями, например [Лист1$A1:B4] .
Чтобы выбрать или указать тип объекта Excel, который вы хотите использовать в качестве источника или назначения для данных, выполните одно из следующих действий:
В службах SSIS
В службах SSIS на странице Диспетчер соединений в редакторе источника Excel или редакторе назначения Excel выполните одно из следующих действий:
Чтобы использовать лист или именованный диапазон, выберите Таблица или представление в разделе Режим доступа к данным. Затем выберите лист или именованный диапазон в списке Имя листа Excel.
Чтобы использовать неименованный диапазон, который задается по адресу, выберите Команда SQL в разделе Режим доступа к данным. Затем в поле Текст команды SQL введите запрос, аналогичный представленному ниже примеру:
В мастере импорта и экспорта SQL Server
В мастере импорта и экспорта выполните одно из следующих действий:
При импорте из Excel выполните одно из следующих действий:
Чтобы использовать лист или именованный диапазон, на странице Выбор копирования таблицы или запроса выберите Скопировать данные из одной или нескольких таблиц или представлений. Затем на странице Выбор исходных таблиц и представлений в столбце Источник укажите исходные листы и именованные диапазоны.
Чтобы использовать неименованный диапазон, который задается по адресу, на странице Выбор копирования таблицы или запроса выберите Написать запрос, указывающий данные для передачи. Затем на странице Определение исходного запроса введите запрос, аналогичный показанному ниже:
При экспорте в Excel выполните одно из следующих действий:
Чтобы использовать лист или именованный диапазон, на странице Выбор исходных таблиц и представлений в столбце Назначение выберите целевые листы и именованные диапазоны.
Чтобы использовать неименованный диапазон, который задается по адресу, на странице Выбор исходных таблиц и представлений в столбце Назначение укажите диапазон в следующем формате без использования разделителей: Sheet1$A1:B5 . Мастер автоматически добавит разделители.
После выбора или ввода объектов Excel для импорта или экспорта вы также можете выполнить следующие действия на странице Выбор исходных таблиц и представлений мастера:
Проверить сопоставления столбцов между источником и назначением, выбрав команду Изменить сопоставления.
Выполнить предварительный просмотр данных, чтобы убедиться в правильности выбора, с помощью команды Предварительный просмотр.
Проблемы с типами данных
Типы данных
Драйвер Excel распознает только ограниченный набор типов данных. Например, все числовые столбцы воспринимаются как тип double (DT_R8), а все строковые столбцы (кроме столбцов типа memo) воспринимаются как строки в Юникоде длиной 255 символов (DT_WSTR). Службы SSIS сопоставляют типы данных Excel следующим образом:
Числовой — с плавающей запятой двойной точности (DT_R8)
Денежный — денежный (DT_CY)
Логический — логический (DT_BOOL)
Дата и время — datetime (DT_DATE)
Строковый — строка в Юникоде длиной в 255 символов (DT_WSTR)
Memo — текстовый поток в Юникоде (DT_NTEXT)
Преобразования типов данных и длины по умолчанию
В службах SSIS неявное преобразование типов данных не выполняется. В результате, возможно, потребуется использовать преобразование "Производный столбец" или "Преобразование данных" для явного преобразования данных Excel до их загрузки в назначение, отличное от Excel, либо для преобразования данных из источника, отличного от Excel, до их загрузки в назначение Excel.
Ниже приведены некоторые примеры преобразований, которые могут потребоваться:
Преобразование между строковыми столбцами Excel в Юникоде и строковыми столбцами в формате с конкретными кодовыми страницами, отличными от Юникода.
Преобразование между строковыми столбцами Excel длиной в 255 символов и строковыми столбцами другой длины.
Преобразование между числовыми столбцами Excel с плавающей запятой двойной точности и числовыми столбцами других типов.
Если вы используете мастер импорта и экспорта и вам требуется выполнить некоторые из этих преобразований данных, мастер выполнит настройку необходимых преобразований автоматически. В результате, даже если вам требуется использовать пакет служб SSIS, часто бывает полезно создать первичный пакет с помощью мастера импорта и экспорта. В таком случае мастер создаст и настроит диспетчеры соединений, источники, преобразования и назначения автоматически.
Проблемы с импортом
Пустые строки
Если в качестве источника указан лист или диапазон, драйвер считывает непрерывный блок ячеек, начиная с первой непустой ячейки в верхнем левом углу листа или диапазона. В результате данные могут начинаться не со строки 1, однако в данных источника не должны присутствовать пустые строки. Например, пустые строки не допускаются между заголовками столбцов и строками данных, а также после названия в верхней части листа.
Если над данными располагаются пустые строки, вы не сможете выполнить их запрос в качестве листа. В Excel необходимо выбрать диапазон данных, присвоить ему имя и выполнить запрос к именованному диапазону, а не к листу.
Отсутствующие значения
Драйвер Excel считывает определенное количество строк (по умолчанию 8 строк) в указанном источнике для определения типа данных каждого столбца. Если столбец содержит смешанные типы данных, особенно если числовые данные смешаны с текстовыми данными, драйвер принимает решение в пользу того типа данных, которого больше, и возвращает значения NULL в ячейки, содержащие данные другого типа. (В случае равенства преимущество получает числовой тип.) Большинство параметров форматирования ячеек в листе Excel не затрагивает это определение типа данных.
Можно изменить поведение драйвера Excel, указав режим импорта для импорта всех значений в виде текста. Чтобы указать режим импорта, добавьте IMEX=1 к значению расширенных свойств в строке соединения диспетчера соединений с Excel в окне "Свойства".
Усеченный текст
Когда драйвер определяет, что столбец Excel содержит текстовые данные, он выбирает тип данных (строковый или memo) на основании самого длинного значения. Если драйвер не обнаруживает значений длиннее 255 символов в выбираемых строках, он считает, что столбец является строковым с длиной 255 символов, а не столбцом типа memo. Поэтому значения длиннее 255 символов могут быть усечены.
Чтобы импортировать данные из столбца типа memo без усечения, можно воспользоваться любым из двух способов:
Убедитесь, что столбец типа memo как минимум в одной из выбранных строк содержит значение длиной более 255 символов.
Увеличьте число строк в выборке драйвера, чтобы включить такую строку. Чтобы увеличить количество строк, включаемых в выборку, достаточно увеличить значение TypeGuessRows в следующем разделе реестра:
Проблемы с экспортом
Создание нового файла назначения
В службах SSIS
Создайте диспетчер соединений Excel с именем создаваемого файла Excel и путем к нему. Затем в редакторе назначения Excel в разделе Имя листа Excel выберите Создать, чтобы создать лист назначения. В этот момент службы SSIS создают новый файл Excel с указанным листом.
В мастере импорта и экспорта SQL Server
На странице Выбор назначения нажмите кнопку Обзор. В диалоговом окне Открыть перейдите в папку, в которой требуется создать новый файл Excel, укажите имя этого файла и выберите Открыть.
Экспорт в достаточно большой диапазон
Экспорт длинных текстовых значений
Чтобы успешно сохранять в столбцы Excel строки, имеющие длину более 255 символов, драйвер должен распознать тип данных целевого столбца как memo , а не как string.
- Если в существующей целевой таблице уже содержатся строки данных, то в столбце типа memo в первых нескольких строках, которые проверит драйвер, должен содержаться, по крайней мере, один экземпляр значения, имеющего длину более 255 символов.
См. также
Дополнительные сведения о компонентах и процедурах, описываемых в этой статье, см. в следующих статьях:
Итак, в прошлом материале мы выяснили, что связанный сервер (Linked Server) – это объект на SQL Server, который хранит подключение к внешнему источнику данных. И к данному объекту мы можем обращаться и выполнять распределённые запросы к разнородным источникам данных, которые расположены за пределами SQL Server.
Иными словами, связанный сервер в Microsoft SQL Server обладает примерно такой же функциональностью, что и конструкции OPENDATASOURCE и OPENROWSET, но в случае с Linked Server нам не нужно непосредственно в запросе указывать строку подключения к источнику данных.
Создание связанного сервера в Microsoft SQL Server
Создать связанный сервер в Microsoft SQL Server можно следующим образом:
- Используя инструкции T-SQL;
- Используя графический интерфейс среды SQL Server Management Studio.
Сегодня мы рассмотрим оба способа.
Исходные данные для примеров
Давайте представим, что у нас есть файл Excel, к которому нам нужно периодически обращаться в своих запросах, поэтому мы решили создать связанный сервер для удобства.
Файл Excel мы сохранили на диск D и он содержит следующие данные.
Подготовка к созданию связанного сервера
Чтобы подключаться к внешним источникам данных и выполнять распределенные запросы, на сервере должен быть установлен соответствующий провайдер.
В нашем случае мы будем использовать провайдер Microsoft.ACE.OLEDB.12.0 x64.
Поэтому перед тем как переходить к созданию связанного сервера, нам необходимо убедиться в том, что данный провайдер у нас установлен, так как в противном случае примеры, которые мы рассмотрим ниже, работать не будут.
Чтобы проверить, есть ли у нас этот провайдер, мы можем запустить следующую процедуру или в обозревателе объектов открыть контейнер «Объекты сервера -> Связанные серверы -> Поставщики» и посмотреть там.
В случае если у Вас в списке нет нужного провайдера, Вам необходимо скачать его с официального сайта Microsoft и установить.
Создание связанного сервера на T-SQL
Для создания и управления связанными серверами в Microsoft SQL Server существуют специальные хранимые процедуры:
- sp_addlinkedserver – процедура создания связанного сервера;
- sp_addlinkedsrvlogin – процедура настройки безопасности связанного сервера.
Таким образом, чтобы создать связанный сервер, нам необходимо выполнить следующие инструкции.
В результате выполнения данных инструкций будет создан связанный сервер, источником которого будет выступать файл Excel, а также будут выполнены определённые настройки для авторизации на данном сервере.
В обозревателе объектов отобразится данный сервер.
Описание параметров процедуры sp_addlinkedserver
- @server – название связанного сервера;
- @srvproduct – название продукта;
- @provider – провайдер (поставщик);
- @datasrc – источник данных;
- @provstr – строка поставщика для подключения.
Описание параметров процедуры sp_addlinkedsrvlogin
- @rmtsrvname – название связанного сервера;
- @useself – указывает, как будет происходить авторизация, с указанием логина и пароля, либо с использованием сопоставления с контекстом безопасности;
- @locallogin – имя входа на локальный сервер;
- @rmtuser – удаленное имя входа, используемое для подключения к связанному серверу;
- @rmtpassword – пароль для удаленного имени входа.
Создание связанного сервера с помощью SQL Server Management Studio
Все то же самое, что мы сделали чуть выше с помощью инструкций T-SQL, мы можем выполнить и в графическом интерфейсе среды SQL Server Management Studio.
Для этого нажмите на контейнер «Связанные серверы» правой кнопкой мыши и выберите «Создать связанный сервер».
Затем в открывшемся окне внесите соответствующие данные для подключения (данные соответствуют параметрам процедуры sp_addlinkedserver).
Чтобы сделать точно такую же авторизацию, которую мы сделали в примере с использованием процедуры sp_addlinkedsrvlogin, необходимо перейти на вкладку «Безопасность» и выбрать пункт «Устанавливать с использованием текущего контекста безопасности имени для входа».
После этого нажать «ОК», и точно такой же связанный сервер с файлом Excel будет создан.
Использование связанного сервера в Microsoft SQL Server
Теперь, когда связанный сервер создан, мы можем к нему обращаться и получать данные как из обычных таблиц или представлений, не указывая никаких данных для подключения к источнику.
При этом обратиться к связанному серверу мы можем с помощью двух способов:
- Используя функцию OPENQUERY (рекомендованный способ);
- Используя полное имя объекта.
Обращение к связанному серверу с помощью OPENQUERY
OPENQUERY – эта функция, с помощью которой можно обратиться к связанному серверу и выполнить указанный SQL запрос.
На эту функцию можно даже ссылаться в инструкциях по модификации данных, т.е. мы можем изменять данные на связанном сервере.
В случаях, когда необходимо получить данные из связанного сервера, функцию OPENQUERY нужно указывать в секции FROM и в качестве первого параметра указывать название связанного сервера, а в качестве второго — SQL запрос, который необходимо выполнить на связанном сервере.
Для примера давайте обратимся к связанному серверу и получим данные, которые хранятся в нашем тестовом файле Excel.
Обращение к связанному серверу с помощью указания полного имени объекта
Как было отмечено, обратиться к связанному серверу мы можем не только с помощью функции OPENQUERY, но и путем простого обращения к нему как к объекту, указав полное имя источника данных, в нашем случае это таблица Excel. Ведь связанный сервер – это объект на сервере, поэтому, соответственно, мы можем к нему обращаться. Это делается следующим образом.
Как видим, результат у нас точно такой же.
Заметка! Если Вас интересует язык SQL, то рекомендую почитать книгу «SQL код» – это самоучитель по языку SQL для начинающих программистов. В ней очень подробно рассмотрены основные конструкции языка.
В следующих материалах я подробно расскажу, как удалить связанный сервер, а на сегодня это все, надеюсь, статья была Вам интересна и полезна, удачи!
Читайте также: