Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 SSIS - foreach + Execute SQL Task  [new]
Лентяй.
Guest
Уважаемые коллеги!
Помогите пожалуйста реализовать простую задачу для SSIS.
Задача следующая.
Нужен пакет, который будт запускаться из job'а, принимая параметр.
С использованием этого параметра будет получаться набор данных, после чего для каждой строки набора будет выполняться запрос, результат которого нужно будет выгрузить в Excel-файл.

+ Скрипты для данных
use master
go
if exists(select * from sys.databases d where d.[name] = 'SSIS_Test')
 drop database SSIS_Test
go 
create database SSIS_Test
go
use SSIS_Test
go

if object_id('test1','U') is not null
 drop table test1;
if object_id('test2','U') is not null
 drop table test2;
 
create table test1
(  _id1 int identity(1,1) not null primary key
 , _date datetime not null
 , _value varchar(63));
   
create table test2
(  _id2 int identity(1,1) not null primary key
 , _id1 int	
 , _value varchar(63));
 
 insert into dbo.test1
 (_date,_value)
 select '20090101', '1_1' union all
 select '20090101', '1_2' union all
 select '20090101', '1_3' union all
 select '20090102', '2_1' union all
 select '20090102', '2_2' union all
 select '20090102', '2_3'

insert into dbo.test2
(_id1,	_value)
select t._id1, t._value + ' '+ss.tt   
from dbo.test1 t
cross join 
	(select '1' as tt union all 
	 select '2' as tt union all
	 select '3' as tt) as ss

--select * from dbo.test1 t
--select * from dbo.test2 t
go

create procedure dbo.pTest
@_id1 int
as
begin
	select _value 
	from dbo.test2 T
	where T._id1 = @_id1
end;
go

--execute dbo.pTest @_id1 = 1 


Что не получилось :
Сделать ExecuteSQL Task, который выполняет параметрический запрос, причем значение параметра принимается из переменной пакета.
Не понял, как внутри цикла ForEach использовать переменную цикла.
Как формировать имена файлов. в которые выполняется выгрузка.

+ Как сам пытался

Объявлены две глобальные переменные
- dt типа DateTime уровня пакета
- obj типа Object тоже уровня пакета
создан Execute SQL Task с такими свойствами :
- ResultSet : Full result set
- Connection type : ADO
- SQL statement :
declare @d datetime
set @d = ?
select _id1 from dbo.test1 where _date = @d
- Parameter mapping :
добавлен User::dt; Direction = Input; DataType = adDate; ParameterName = 0
- ResultSet :
Name = 0; VarName = User::obj

При попытке выполнить даже такой таск, вылетает ошибка :
[Execute SQL Task] Error: Executing the query "declare @d datetime set @d = ? select _id1 from dbo.test1 where _date = @d" failed with the following error: "Значение не попадает в ожидаемый диапазон.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Далее.
Добавлен контейнер ForEach
- Enumerator : Foreach ADO Enumerator
- Object source variable : User::obj

Внутри ForEach DatFlowTask. Теперь что в нём :
- OLEDBSource
- Data Conversion
- Excel destination

В OLEDBSource нужно указать хранимку, которая принимает в качестве параметра переменную цикла. Как это сделать?
Например так : execute dbo.pTest @_id1 = 1
Как можно генерить имена файлов, в которые будет выгружаться результат.
Например так : param_[сюда значение параметра @_id1].xls

Плиз хелп!
:)
25 дек 09, 10:13    [8117044]     Ответить | Цитировать Сообщить модератору
 Re: SSIS - foreach + Execute SQL Task  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Лентяй.
Объявлены две глобальные переменные
- dt типа DateTime уровня пакета
- obj типа Object тоже уровня пакета
создан Execute SQL Task с такими свойствами :
- ResultSet : Full result set
- Connection type : ADO
- SQL statement :
declare @d datetime
set @d = ?
select _id1 from dbo.test1 where _date = @d
- Parameter mapping :
добавлен User::dt; Direction = Input; DataType = adDate; ParameterName = 0
- ResultSet :
Name = 0; VarName = User::obj

При попытке выполнить даже такой таск, вылетает ошибка :
[Execute SQL Task] Error: Executing the query "declare @d datetime set @d = ? select _id1 from dbo.test1 where _date = @d" failed with the following error: "Значение не попадает в ожидаемый диапазон.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Внимательней доку читайте...
Попробуйте так:
 - SQL statement :
	select _id1 from dbo.test1 where _date = ?
 - Parameter mapping :
	User::dt; Direction = Input; DataType = adDate; ParameterName = Param1
25 дек 09, 10:36    [8117208]     Ответить | Цитировать Сообщить модератору
 Re: SSIS - foreach + Execute SQL Task  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Лентяй.
Далее.
Добавлен контейнер ForEach
- Enumerator : Foreach ADO Enumerator
- Object source variable : User::obj

Внутри ForEach DatFlowTask. Теперь что в нём :
- OLEDBSource
- Data Conversion
- Excel destination

В OLEDBSource нужно указать хранимку, которая принимает в качестве параметра переменную цикла. Как это сделать?
Например так : execute dbo.pTest @_id1 = 1
Как можно генерить имена файлов, в которые будет выгружаться результат.
Например так : param_[сюда значение параметра @_id1].xls
Сделайте скриптовую задачу, в которой сформируйте текст вызова процедуры в выходную переменную, которую, в свою очередь, и используйте в качестве запроса в своей DatFlowTask.
А вот имя выходного файла придется формировать в Expression для соответствующего коннект менеджера, опять же, с использованием ваших переменных.
25 дек 09, 10:50    [8117318]     Ответить | Цитировать Сообщить модератору
 Re: SSIS - foreach + Execute SQL Task  [new]
Лентяй.
Guest
tpg
Внимательней доку читайте...
Попробуйте так:
 - SQL statement :
	select _id1 from dbo.test1 where _date = ?
 - Parameter mapping :
	User::dt; Direction = Input; DataType = adDate; ParameterName = Param1

Спасибо, tpg, работает.

Я читал вот тут но только в русской справке, для меня такое обращение к параметрам получилось неочевидным.
Товарищи, поясните пожалуйста параграф из спойлера. Насколько я понял, параметры можно именовать. Однако, если в тексте запроса я пишу так :
declare @d datetime
set @d = ?_date
select _id1 from dbo.test1 where _date = @d
SSIS ругается. Как показать, что я ввожу именованный параметр (в данном случае имя параметра должно быть _date)?

+ Parameters with ADO Connection Managers

ADO connection managers require that the SQL command use question marks (?) as parameter markers, but you can use any user-defined name, except for integer values, as parameter names.

To provide values to parameters, variables are mapped to parameter names and the Execute SQL task uses the ordinal value of the parameter name in the parameter list to load values from variables to parameters.


И подскажите про это. пожалуйста
Не понял, как внутри цикла ForEach использовать переменную цикла.
25 дек 09, 10:57    [8117380]     Ответить | Цитировать Сообщить модератору
 Re: SSIS - foreach + Execute SQL Task  [new]
Лентяй.
Guest
tpg
Лентяй.
Далее.
Добавлен контейнер ForEach
- Enumerator : Foreach ADO Enumerator
- Object source variable : User::obj

Внутри ForEach DatFlowTask. Теперь что в нём :
- OLEDBSource
- Data Conversion
- Excel destination

В OLEDBSource нужно указать хранимку, которая принимает в качестве параметра переменную цикла. Как это сделать?
Например так : execute dbo.pTest @_id1 = 1
Как можно генерить имена файлов, в которые будет выгружаться результат.
Например так : param_[сюда значение параметра @_id1].xls
Сделайте скриптовую задачу, в которой сформируйте текст вызова процедуры в выходную переменную, которую, в свою очередь, и используйте в качестве запроса в своей DatFlowTask.
А вот имя выходного файла придется формировать в Expression для соответствующего коннект менеджера, опять же, с использованием ваших переменных.

Спасибо огромное, сейчас буду пробовать.
25 дек 09, 10:59    [8117388]     Ответить | Цитировать Сообщить модератору
 Re: SSIS - foreach + Execute SQL Task  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Лентяй.
SSIS ругается.
Интересно, как?
25 дек 09, 11:30    [8117630]     Ответить | Цитировать Сообщить модератору
 Re: SSIS - foreach + Execute SQL Task  [new]
Лентяй.
Guest
tpg
Лентяй.
SSIS ругается.
Интересно, как?
Примерно так :

[Execute SQL Task] Error: Executing the query "declare @d datetime set @d = ?_date select _id1 from dbo.test1 where _date = @d" failed with the following error: "Must declare the scalar variable "@P1_date".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
25 дек 09, 12:47    [8118220]     Ответить | Цитировать Сообщить модератору
 Re: SSIS - foreach + Execute SQL Task  [new]
Лентяй.
Guest
tpg
Сделайте скриптовую задачу, в которой сформируйте текст вызова процедуры в выходную переменную, которую, в свою очередь, и используйте в качестве запроса в своей DatFlowTask.
А вот имя выходного файла придется формировать в Expression для соответствующего коннект менеджера, опять же, с использованием ваших переменных.

Не получается
Сделал такой ScriptTask.
+
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
Dim _res As String
Public Sub Main()
_res = "execute dbo.pTest @_id1 = " + Dts.Variables("_id").Value;
Dts.TaskResult = Dts.Results.Success
End Sub
End Class

Теперь вопросы такие :
- у меня из SQLTask возвращается набор данных в объектную переменную.
ForEach task должен быть
Enumerator= Foreach From Variable Enumerator
или
Enumerator= Foreach ADO Enumerator?

- Объявил переменную _id типа Integer для ForEach task. Как мне сделать, чтобы переменная _id была переменной цикла?
- Как обратиться к полю _res класса ScriptMain из DataFlow?

Коллеги, помогите плиз освоить это чудо индусской мысли!!!
Я так вроде не очень тупой, и опыт вроде есть. Но что-то логика пакетов SSIS до меня никак не доходит!
25 дек 09, 16:28    [8119824]     Ответить | Цитировать Сообщить модератору
 Re: SSIS - foreach + Execute SQL Task  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Лентяй.
Не получается
Ошибка то какая?
И причем тут индусы?
28 дек 09, 06:20    [8125157]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: SSIS - foreach + Execute SQL Task  [new]
Vezyr
Member

Откуда:
Сообщений: 6
А какая версия сервера? В 2005 вместо параметризованного запроса лучше использовать выражения или значение переменной.
28 мар 13, 19:00    [14109426]     Ответить | Цитировать Сообщить модератору
 Re: SSIS - foreach + Execute SQL Task  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
Vezyr, доброе утрое
28 мар 13, 19:01    [14109433]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить