Как экспортировать данные из таблицы SQL в несколько файлов excel в SSIS? Из sql в excel

sql-server - Как экспортировать данные из таблицы SQL в несколько файлов excel в SSIS?

Я создал пакет SSIS, который извлекает данные из SQL и загружает их в excel. У меня возникла проблема с динамическим пакетом и загрузкой данных в несколько файлов excel.

Сначала я создал задачу выполнения SQL, которая содержит все имена файлов с кодом SQL: SELECT FileName FROM Files и его результирующий набор будет храниться в переменной FileNameObj. Затем я создал Контейнер контура Foreach и добавил внутри него задачу потока данных.

Foreach Loop Container: Foreach ADO Enumerator, ADO Object source variable - FileNameObj, отображение переменных - FileName и индекс - 0. В рамках задачи потока данных я добавил источник OLE DB и назначение Excel, указывающее путь к файлу: C:\Test\ABC.xlsx.

Вот свойство Connection Manager:

ConnectionString: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test\ABC.xlsx;Extended Properties="Excel 12.0;HDR=YES";

Для параметра DelayValidation установлено значение True

ExcelFilePath - это C:\Test\ABC.xlsx

Затем я создал выражение с Property ExcelFilePath и Expression: "C:\\Test\\"+ @[User::FileName]+".xlsx" Затем моя ConnectionString изменится на: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test\.xlsx;Extended Properties="Excel 12.0;HDR=YES";

Я уже создал все шаблоны моих файлов (одинаковые структуры для всех) в тестовой папке. Я получаю следующую ошибку:

Ошибка при выполнении потока данных [Назначение Excel [131]]: Код ошибки SSIS DTS_E_OLEDBERROR. Произошла ошибка OLE DB. Код ошибки: 0x80040E37.

Ошибка в задаче потока данных [Назначение Excel [131]]: открытие набора строк для "Sheet1 $" не выполнено. Убедитесь, что объект существует в базе данных.

Исключение из HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)

Что я делаю не так? Или это возможно сделать в SSIS?

qaru.site

sql - Таблица в Excel из хранимой процедуры SQL Server с полем параметров в книге

Я получил его для работы, используя шаги, приведенные в следующем связанном сообщении в блоге.

Шаги, скопированные здесь, в случае разрыва ссылок в будущем.

Выберите вкладку "Данные" в ленте Excel, а затем в группе "Получить исходные данные" выберите раскрывающийся список "Из других источников". Затем выберите "Из запроса Microsoft"

В раскрывающемся окне "Выбрать источник данных" выберите свой SQL Server, затем нажмите "ОК" .

При необходимости закройте окно "Добавить таблицы".

Нажмите кнопку "SQL" или выберите "Просмотр" > "SQL", чтобы открыть всплывающий редактор SQL.

Введите следующий синтаксис: {CALL myDatabaseName.dbo.myStoredProc(?,?,?)}

Например: {CALL northwind.dbo.spGetMaxCost(?,?,?)}

Обязательно включите фигурные скобки вокруг оператора вызова. Каждый знак вопроса (?) Указывает параметр. Если ваша хранимая процедура вызывает более или менее параметры, добавьте или вычитайте вопросительные знаки по мере необходимости.

Нажмите кнопку OK. В поле вопроса должно появиться высказывание "Запрос SQL не может быть представлен графически, продолжайте в любом случае?", Просто нажмите кнопку "ОК" .

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

Как только вы ввели последний параметр, вы должны получить некоторые результаты в Microsoft Query. Если они выглядят хорошо, закройте Microsoft Query.

Теперь вы должны посмотреть всплывающее окно "Импорт данных". Нажмите кнопку "Свойства", которая откроет всплывающее окно "Свойства подключения".

Выберите вкладку "Определение", затем нажмите кнопку "Параметры". Теперь вы должны увидеть всплывающее окно "Параметры", в котором вы можете подключить этот параметр к определенной ячейке.

Выберите Получить значение из следующей ячейки, а затем подключитесь к соответствующей ячейке в Excel, которая будет удерживать ваш параметр, щелкнув маленькую рамку со стрелкой.

Если вы хотите, чтобы данные обновлялись каждый раз, когда вы меняете ячейку, содержащую этот параметр, установите флажок "Обновить автоматически при изменении значения ячейки"

Продолжайте, как указано выше, для других параметров. По завершении нажмите "ОК" , чтобы вернуться к всплывающему окну "Свойства подключения". Нажмите "ОК" , чтобы вернуться к всплывающему окну "Импорт данных", и снова нажмите "ОК" .

Теперь вы должны иметь некоторые данные прямо из хранимой процедуры.

qaru.site

Excel - Подключение и получение данных с SQL сервера | Info-Comp.ru

Мало пользователей, да и начинающих программистов, которые знают о возможности Excel подключаться к внешним источникам, и в частности к SQL серверу, для загрузки данных из этих источников. Эта возможность достаточно полезна, поэтому сегодня мы займемся ее рассмотрением.

Функционал Excel получения данных из внешних источников значительно упростит выгрузку данных с SQL сервера, так как Вам не придется просить об этом программиста, к тому же данные попадают сразу в Excel. Для этого достаточно один раз настроить подключение и в случае необходимости получать данные в Excel из любых таблиц и представлений Views, из базы настроенной в источнике, естественно таких источников может быть много, например, если у Вас несколько баз данных.

Задача для получения данных в Excel

И для того чтобы более понятно рассмотреть данную возможность, мы это будем делать как обычно на примере. Другими словами допустим, что нам надо выгрузить данные, одной таблицы, из базы SQL сервера, средствами Excel, т.е. без помощи вспомогательных инструментов, таких как Management Studio SQL сервера.

Примечание! Все действия мы будем делать, используя Excel 2010. SQL сервер у нас будет MS Sql 2008.

И для начала разберем исходные данные, допустим, есть база test, а в ней таблица test_table, данные которой нам нужно получить, для примера будут следующими:

Эти данные располагаются в таблице test_table базы test, их я получил с помощью простого SQL запроса select, который я выполнил в окне запросов Management Studio. И если Вы программист SQL сервера, то Вы можете выгрузить эти данные в Excel путем простого копирования (данные не большие), или используя средство импорта и экспорта MS Sql 2008. Но сейчас речь идет о том, чтобы простые пользователи могли выгружать эти данные.

Настройка Excel для получения данных с SQL сервера

Настройка, делается достаточно просто, но требует определенных навыков и консультации администратора SQL сервера. Вы, конечно, можете попросить программиста настроить Excel на работу или сделать это сами, просто спросив пару пунктов, а каких мы сейчас узнаем.

И первое что нам нужно сделать, это конечно открыть Excel 2010. Затем перейти на вкладку «Данные» и нажать на кнопку «Из других источников» и выбрать «С сервера SQL Server»

Затем у Вас откроется окно «Мастер подключения данных» в котором Вам необходимо, указать на каком сервере располагается база данных и вариант проверки подлинности. Вот именно это Вам придется узнать у администратора баз данных, а если Вы и есть администратор, то заполняйте поля и жмите «Далее».

Где,

Имя сервера – это адрес Вашего сервера, здесь можно указывать как ip адрес так и DNS имя, в моем случае сервер расположен на этом же компьютере поэтому я и указал localhost;
Учетные данные – т.е. это логин и пароль подключения к серверу, здесь возможно два варианта, первый это когда в сети Вашей организации развернута Active directory (Служба каталогов или домен), то в этом случае можно указать, что использовать те данные, под которыми Вы загрузили компьютер, т.е. доступы доменной учетки, и в этом случае никаких паролей здесь вводить не надо, единственное замечание что и на MSSql сервере должна стоять такая настройка по проверки подлинности. У меня именно так и настроено, поэтому я и выбрал этот пункт. А второй вариант, это когда администратор сам заводит учетные данные на SQL сервере и выдает их Вам, и в этом случае он должен их Вам предоставить.

Далее необходимо выбрать базу, к которой подключаться, в нашем примере это база test. Также это подключение можно настроить сразу на работу с определенной таблицей или представлением, список таблиц и представлений у Вас будет отображен, давайте мы сделаем именно так и настроем подключение сразу на нашу таблицу test_table. Если Вы не хотите этого, а хотите чтобы Вы подключались к базе и потом выбирали нужную таблицу, то не ставьте галочку напротив пункта «Подключаться к определенной таблице», а как я уже сказал, мы поставим эту галочку и жмем «Далее».

В следующем окне нам предложат задать имя файла подключения, название и описание, я например, написал вот так:

После того как Вы нажмете «Готово» у Вас откроется окно импорта этих данных, где можно указать в какие ячейки копировать данные, я например, по стандарту выгружу данные, начиная с первой ячейки, и жмем «ОК»:

В итоге у меня загрузятся из базы вот такие данные:

Т.е. в точности как в базе. Теперь когда, например, изменились данные в этой таблице, и Вы хотите выгрузить их повторно Вам не нужно повторять все заново, достаточно в excel перейти на вкладку «Данные» нажать кнопку «Существующие подключения» и выбрать соответствующее, т.е. то которое Вы только что создали.

Вот собственно и все, как мне кажется все достаточно просто.

Таким способом получать данные в Excel из базы SQL сервера очень удобно и главное быстро, надеюсь, Вам пригодятся эти знания полученные в сегодняшнем уроке. Удачи!

sql - Импорт данных из базы данных SQL в Excel 2003 VS Excel 2010 (VBA)

Итак, моя компания наконец-то обновилась до MS Office 2010. До сих пор я работаю в 2003 году. Я программист SQL, и я постоянно создаю отчеты в Excel, которые извлекают данные из нашей базы данных. В большинстве случаев я создам макросы, которые принимают параметры, которые пользователи будут вводить в определенные ячейки, изменяют запрос и затем обновляют его в соответствии с параметрами.

Вот действительно простой пример:

В Excel 2003 я бы открыл новую книгу.

Нажмите "Данные", затем "Импорт внешних данных", затем "Новый запрос базы данных".
Затем он предложит вам выбрать источник данных, поэтому я бы выбрал базу данных, из которой я хотел запросить (которая уже была установлена с подключением ODBC).
Затем я отменю из окон мастера запросов, а затем, когда я в редакторе запросов Microsoft, я просто вхожу в свой запрос.
- Для простоты я буду выбирать * из таблицы с именем Agents, которая представляет собой список агентов, работающих в компании, и их EmployeeIds.
- select * from Agents
Затем я "x" из редактора запросов, и появляется окно с именем "Импорт данных", где он спрашивает, куда вы хотите поместить данные: в существующем листе? новый рабочий лист? и т.д. Мне просто нужно вернуть данные в существующем листе, начиная с ячейки A2

Итак, я пишу этот простой макрос в редакторе Visual Basic в модуле для книги:

Sub Refresh() Dim oQuery as QueryTable Dim oAgent as String set oQuery = Sheet1.QueryTables(1) oAgent = Sheet1.Range("A1") oQuery.CommandText = "select * from Agents where Agent = '"+oAgent+"'" oQuery.Refresh End Sub

Я создаю кнопку, которая запускает этот макрос и вставляет его в B1. Таким образом, пользователь открывает отчет, вводит имя в A1, нажимает кнопку и агент и их идентификатор появляются в таблице ниже. Действительно просто? Но я не могу заставить это работать в Excel 2010.

Вот мои шаги и следующая ошибка:

Я открываю Excel 2010 и перехожу на вкладку "Данные".
В разделе "Получить внешние данные" я нажимаю "Из других источников" и выбираю "Из запроса Mircrosoft" из раскрывающегося списка.
Затем появляется окно "Выбор источника данных", и это в основном то же самое, что и шаги 3,4 и 5 выше.

Затем я пишу тот же макрос, создаю кнопку и назначаю ее марке, но когда я нажимаю кнопку, я получаю следующую ошибку:

Run-time error '9': Subscript out of range

Я ударил debug, и отладчик выделяет эту строку

Set oQuery = Sheet1.QueryTables(1)

Я попытался сделать эту строку более конкретной:

Set oQuery = WorkBooks("Book 1").Sheets("Sheet 1").QueryTables(1)

Но я получаю ту же ошибку.

В принципе, мне нужно знать, как это сделать в Excel 2010. Но вот интересное замечание: если я создам этот отчет в Excel 2003, сохраните его как .xls, а затем откройте его в 2010 году, это будет работать. Я даже могу сохранить копию как .xlsm, тогда откройте ее, и она будет работать с этим же макросом. Это только когда я создаю отчет в 2010 году, что я не могу заставить его работать. По какой-то причине кажется, что он просто не может найти запрос, чтобы изменить его текст команды, а затем обновить. Пожалуйста, помогите, я застрял на этом в течение нескольких дней!

qaru.site

sql-server - Экспорт в Excel из SQL Server

Я застрял в проблеме, для которой я не могу найти никакой причины или решения.

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

Проблема. Заголовки столбцов отображаются внизу, и приложение ожидает, что они будут в верхней строке. Я не могу изменить функционирование приложения.

Это отлично работает в SQL 2005, но мы недавно обновились до SQL 2012, и это началось.

Я не нашел ничего в Интернете, чтобы решить эту проблему.

Это скрипт SQL, который я выполняю

SELECT @columnNames = COALESCE( @columnNames + ',', '') + '['+ column_name + ']', @columnConvert = COALESCE( @columnConvert + ',', '') + 'convert(nvarchar(4000),' + '['+ column_name + ']' + case when data_type in ('datetime', 'smalldatetime') then ',121' when data_type in ('numeric', 'decimal') then ',128' when data_type in ('float', 'real', 'money', 'smallmoney') then ',2' when data_type in ('datetime', 'smalldatetime') then ',120' else '' end + ') as ' + '['+ column_name + ']' FROM tempdb.INFORMATION_SCHEMA.Columns WHERE table_name = '##TempExportData' -- execute select query to insert data and column names into new temp table SELECT @sql = 'select ' + @columnNames + ' into ##TempExportData2 from (select ' + @columnConvert + ', ''2'' as [temp##SortID] from ##TempExportData union all select ''' + replace(replace(replace(@columnNames, ',', ''', '''),'[',''),']','') + ''', ''1'') t order by [temp##SortID]' exec (@sql) -- build full BCP query DECLARE @bcpCommand VARCHAR(8000) SET @bcpCommand = 'bcp " SELECT * from ##TempExportData2" queryout' SET @bcpCommand = @bcpCommand + ' ' + @fullFileName + ' -T -w -S' + @serverInstance EXEC master..xp_cmdshell @bcpCommand

Где TempExportData2 содержит данные, которые вместе с заголовками столбцов

qaru.site

Подключение базы данных SQL Server к книге (Power Query)

На вкладке ленты Данные щелкните Получить данные > Из базы данных > Из базы данных SQL Server. Если вы не видите кнопки Получить данные, нажмите кнопку Создать запрос и выберите пункты Из базы данных -> Из базы данных SQL Server.
В диалоговом окне База данных Microsoft SQL укажите SQL Server, к которому нужно подключиться, в поле Имя сервера. При необходимости вы также можете указать имя базы данных.
Если данные нужно импортировать с использованием запроса на языке базы данных, укажите его в поле Инструкция SQL. Дополнительные сведения см. в статье Импорт данных из базы данных с помощью запроса на ее языке.
Нажмите кнопку ОК.
Выберите режим проверки подлинности для подключения к базе данных SQL Server.
1. Windows: этот режим используется по умолчанию. Выберите это значение, если вы хотите подключаться, используя проверку подлинности Windows.
2. База данных: выберите это значение, если вы хотите подключаться, используя проверку подлинности SQL Server. Затем укажите имя пользователя и пароль для подключения к экземпляру SQL Server.
По умолчанию установлен флажок Шифрование соединения, то есть Power Query подключается к базе данных через зашифрованное соединение. Если вы не хотите использовать зашифрованное соединение, снимите этот флажок, а затем нажмите кнопку Подключить.

Если подключение к SQL Server не установлено через зашифрованное соединение, Power Query предлагает подключиться, используя незашифрованное соединение. Нажмите кнопку ОК в окне сообщения, чтобы использовать незашифрованное соединение.

Пример формулы

Для написания формул Power Query можно также использовать редактор запросов.

= Sql.Databases(".")= Sql.Database(".","Contoso")

На вкладке ленты Power Query щелкните Из базы данных > Из базы данных SQL Server.
В диалоговом окне База данных Microsoft SQL укажите SQL Server, к которому нужно подключиться, в поле Имя сервера. При необходимости вы также можете указать имя базы данных.
Если данные нужно импортировать с использованием запроса на языке базы данных, укажите его в поле Инструкция SQL. Дополнительные сведения см. в статье Импорт данных из базы данных с помощью запроса на ее языке.
Нажмите кнопку ОК.
Выберите режим проверки подлинности для подключения к базе данных SQL Server.
1. Windows: этот режим используется по умолчанию. Выберите это значение, если вы хотите подключаться, используя проверку подлинности Windows.
2. База данных: выберите это значение, если вы хотите подключаться, используя проверку подлинности SQL Server. Затем укажите имя пользователя и пароль для подключения к экземпляру SQL Server.
По умолчанию установлен флажок Шифрование соединения, то есть Power Query подключается к базе данных через зашифрованное соединение. Если вы не хотите использовать зашифрованное соединение, снимите этот флажок, а затем нажмите кнопку Подключить.

Если подключение к SQL Server не установлено через зашифрованное соединение, Power Query предлагает подключиться, используя незашифрованное соединение. Нажмите кнопку ОК в окне сообщения, чтобы использовать незашифрованное соединение.

Пример формулы

Для написания формул Power Query можно также использовать редактор запросов.

= Sql.Databases(".")= Sql.Database(".","Contoso")

Функция "Получить и преобразовать" не была доступна в Excel 2007, но можно использовать файл подключения к данным Office (ODC) для подключения к базе данных Microsoft SQL Server из книги Excel 2007. SQL Server — это полнофункциональная реляционная СУБД, предназначенная для создания корпоративных решений в области управления данными с высокими требованиями к производительности, доступности, масштабируемости и безопасности.

На вкладке Данные в группе Получить внешние данные нажмите кнопку Из других источников и выберите пункт С сервера SQL Server.

Откроется мастер подключения данных, включающий три страницы.

Страница 1. Подключение к серверу базы данных
На шаге 1 в поле Имя сервера введите имя компьютера SQL Server, к которому требуется подключиться.
На шаге 2 в разделе Учетные сведения выполните одно из следующих действий:
- Чтобы использовать текущие имя пользователя и пароль Microsoft Windows, выберите вариант Использовать проверку подлинности Windows.
- Чтобы указать имя пользователя и пароль для доступа к базе данных, выберите вариант Использовать следующие имя пользователя и пароль и введите соответствующие данные в поля Имя пользователя и Пароль.
  
  Примечание по безопасности:
  - Используйте надежные пароли, состоящие из букв в верхнем и нижнем регистре, цифр и символов. В ненадежных паролях не используются сочетания таких элементов. Надежный пароль: Y6dh!et5. Ненадежный пароль: house1. Пароль должен состоять не менее чем из 8 знаков. Лучше всего использовать парольную фразу длиной не менее 14 знаков.
  - Очень важно запомнить пароль, поскольку корпорация Майкрософт не сможет помочь в его восстановлении. Все записанные пароли следует хранить в надежном месте вдали от сведений, для защиты которых они предназначены.
  Страница 2. Выбор базы данных и таблицы
В разделе Выберите базу данных, содержащую нужные сведения выберите базу данных. В разделе Подключение к определенной таблице выберите нужную таблицу или представление.

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

Страница 3. Сохранение файла подключения к данным и завершение работы
При необходимости в поле Имя файла измените предложенное имя файла. Нажмите кнопку Обзор, чтобы изменить место хранения файла, используемое по умолчанию ("Мои источники данных").
При необходимости введите в поля Описание, Имя и Ключевые слова поиска описание файла, имя и слова для поиска.
Чтобы гарантировать, что файл подключения всегда будет использоваться при обновлении данных, установите флажок Всегда пытаться использовать этот файл для обновления данных. Включение этого параметра гарантирует, что все книги, использующие данный файл подключения, всегда будут использовать его обновления.
Чтобы задать параметры доступа к внешнему источнику данных для отчета сводной таблицы при публикации книги на службы Excel и ее открытии через службы Excel, нажмите кнопку Параметры проверки подлинности и выберите один из следующих вариантов подключения к источнику данных:
- Проверка подлинности Windows. Выберите этот вариант, чтобы использовать имя и пароль Windows текущего пользователя. Этот способ является наиболее безопасным, но он может плохо сказаться на производительности при одновременном подключении к серверу многих пользователей.
- Единый вход. Выберите этот параметр, чтобы использовать единый вход (SSO), а затем введите соответствующий идентификатор в поле Код SSO. Администратор может настроить сайт Windows SharePoint Services для использования базы данных единого входа, в которой могут храниться имя пользователя и пароль. Этот способ является наиболее эффективным при подключении к серверу большого числа пользователей.
- Нет. Выберите этот вариант, чтобы сохранить имя пользователя и пароль в файле подключения.
  
  Примечание по безопасности: Не рекомендуется сохранять сведения о входе при подключении к источникам данных. Эта информация может храниться в виде обычного текста, и злоумышленники смогут получить с ее помощью доступ к источнику данных.
  
  Примечание: Параметры проверки подлинности используются только службы Excel, а не Excel.
Нажмите кнопку ОК.
Нажмите кнопку Готово, чтобы закрыть окно мастера подключения к данным.

Откроется диалоговое окно Импорт данных.
В разделе Выберите способ представления данных в книге выполните одно из следующих действий:
- Чтобы создать таблицу Excel, щелкните элемент Таблица (вариант по умолчанию).
- Чтобы создать отчет сводной таблицы, щелкните Отчет сводной таблицы.
- Чтобы создать сводную диаграмму и отчет сводной таблицы, щелкните Сводная диаграмма и отчет сводной таблицы.
  
  Примечание: Вариант Только создать подключение доступен только для базы данных OLAP.
В группе Куда следует поместить данные? выполните одно из следующих действий:
- Чтобы поместить данные на существующий лист, выберите вариант На существующий лист и введите имя первой ячейки в нужном диапазоне.
  
  Кроме того, можно нажать кнопку Свернуть диалоговое окно , выбрать ячейку на листе, а затем нажать кнопку Развернуть диалоговое окно .
- Чтобы поместить данные на новый лист, начиная с ячейки A1, установите переключатель На новый лист.
При необходимости можно изменить свойства подключения (вместе с файлом подключения). Для этого нажмите кнопку Свойства, внесите изменения в диалоговом окне Свойства подключения, а затем нажмите кнопку ОК.

Дополнительные сведения см. в статье Свойства подключения.

support.office.com