Передача параметров в Execute Task или как мы до этого доехали.
<Disclaimer: ???????? ???? ???? ????? ? ??????????? ???????????????. ???? ??????????? ???, ??? ?? ????>
??? ??? ?????????? ? ????????? ???????, ????? ????????..
?????????? ????????????, ?????????? ?????? ? ?.?.
??????????????? ? ??????? ????????, ????? ????????? ?? ?????????? ???? – ???? ?? ???????????? ???????????? ??????? ?????? ?? ??????? ? ????? ??????? ? ??????? ?? ??????. ??????????, ??? ????????, ??????? ?????????? ????? ??????????? ? ??????? ???? ????????: ???? ????????? «????», ???? ?????? ??????????, ?? ???? ????????? ? «?????????? ???????» . ?????? «????» ????? ?????? «????????».. ??????? ??????? ????? ?????, ????????, ?????????? ?????? ?? ???.
??????????????? ????????? ???????, ?????, ??? ???? ???????????? ?????????? ?? ?????? ??????? ? ???????????? ???????? ? ???? ????? ?????????? SSIS.
??? ??? ????????, ??????????? ??????? – ????. ??? ???????? ?????????? ????????, ???????????, ??? ??????? ? ??? ????:
USE Sample
CREATE TABLE SourceTable (Range int, Value int)
? ???????? ????? ??, ???????, ?????? ???????? ????????? ?? ???????????, ?? ?? ?????? ?????? ?? ??? ?????. ?????, ????? ? ??? ???? ??????????? ??????? ??????? ?? ?????? ?????????. ? ?????? ??????? ???? ?????????? ?????? ??????? Range, ??? ? ??? «??????» ????????? ?? 0 ?? 1000, 1001 ?? 2000, ? ?.?. ?? 10 000.
???? Value ????? ????? ??????? ?????-?????? ?????. ??? ?????? ?? ??? ?????.
????, SSIS ????? ??????????, ?????? «???????????» ?????? ?? ??????? ?… ??????-?? ???????? ???? ?? ????? ???? ?????, ??????? ????? ????????? ????????? ???, ??? ????????? ?????????? ???????, ?? ????? ??? ????????? ??????????? ? «?????? ???????. ?? ???? ?? ???? ?????-?? ??????? ???????, ????? ?????? ???? ????????. ??? ???? ???? ???? ??????? ??? ???? ????????? ??????? [dbo].[work_queue], ??????? ???????? ?????????????? ??? ???:
work_id |
group |
assignment |
is_active |
is_done |
1 |
SSISLoad |
1000 |
0 |
0 |
2 |
SSISLoad |
2000 |
0 |
0 |
3 |
SSISLoad |
3000 |
0 |
0 |
4 |
SSISLoad |
4000 |
0 |
0 |
5 |
SSISLoad |
5000 |
0 |
0 |
6 |
SSISLoad |
6000 |
0 |
0 |
7 |
SSISLoad |
7000 |
0 |
0 |
8 |
SSISLoad |
8000 |
0 |
0 |
9 |
SSISLoad |
9000 |
0 |
0 |
?? ????, ?? ?????? ????????????? «??????» (work_id) ? ????????, ??????? ?????? ????? ????????????? (assignment). ??? ??????. ????? ????????? ?????? ????? «?????» ????? ????????????, ????? ????????? , ????? ?????? ??? ???????????. ? ?????? ??????? ??????????? ????? ???????.
?????, ??? ?????? ???????? ???? «? ??????», ???? is_active ?????? ????????? ? 0 ?? 1. ?? ? ?? ????????? ?????? ???? is_done ???? ???????? ? 0 ?? 1.
?????? ?? ????????? ???? ????? ????????? ?????????:
CREATE PROCEDURE [dbo].[sp_get_work]
@group NVARCHAR(128) = NULL /* The group to get*/
, @work_id INT = NULL OUTPUT /* the work_id assigned */
, @assignment NVARCHAR(MAX) = NULL OUTPUT /* The description of the work to do */
??? ????? ?? ????, ????????? ?????????? @assignment, ?.?. ????????, ??????? ???? «????????» ?? ???????. ??? ??? ??????????? ????? ? SSIS ???????.
??? ????, ????? ?????? «????????????», ????? ??????? ????????? ?????????? ?????? ( ?? ?????????? ??????? SSIS, ??????? ????? ???????????? ???????).
??????????, ????????????? ????? ?????????.. ????? ?????????? ? ??? ?????, ???? ??????? ???? ???-???.
???????? ?????????? ?????? SSIS ??????.
??? ?????? ??????? ????? ??????, ? ??? ????? SSIS ?????. ????????, ??? ??? LoadStaiging1.dstx.
????? ?????????? ? Control Flow ?????????????????? ?????????? ?????. ??????, ??? ?? ??????? – ????????, ??? ????????? ???????, ???? ?? ???????????? ?????? - ????? .
???.1 «?????????» ?????? ? ???????
??? ????? ? ?????? SQLStatement ????? ????????? ?????????? ???????? ??????? T-SQL ??????. ??? ?? ? ?????????????.
?????, ???? ??? ???-?? ??????, ??? ???? ?? ????????????? ????? – ?? ??????? ?????????, ??????? ?????????? ?????????? ????????, ??????? «?????» ?????????? ?? ??????? ?, ??? –?????? ?? ???????? ? ?????????? ????????, ???????? ??? ??? is_active. ??? ??? ??? ???? ????? ? ?????? ????? ??????? ??????????, ??????????? ?? ?????????? ????? ??????. ????????? ?? ??? ????? ????????? ????????, ??????? ????? ?????????????? ?? ????????? ?????? ???, ????? ????? ???????????.
???.2 ???????????? ??????????.
????????? ??????? ? ?????? ????? ??? ??? ????????? ????, ????? ???????? ? ??? ?????? «????????». ??? ????? ? ???? ????? Execute SQL Task.
??? 3. Execute Task - ?????????, ????? ???????? ??? ?? ? ??????
??? ????? ?? ????, ? ?????? ? Connection Type ADO.NET ??????????. ???????? ?????? ????? ????, ??? ? ??? ??????? ???????? ? ???????????. ????????? ??? ? ???:
??? ??? ?????? ?? SQL Statement
EXEC [Sample].[dbo].[sp_get_work] @group, @work_id, @assignment OUTPUT
?????????? ??????????? ???, ?? ??? ??? ? ?????? OLE DB ??????????? ??? ???????? ?? ??? ???:
EXEC [Sample].[dbo].[sp_get_work] ?, ?, ? OUTPUT
???? ????? ??? ??????? ???????.
??????, ???? ??????????? ?????????? ?????? ???? ?????? ? ????, ??? ?? ?????????? ?? ?????? ??????.
???.4 "??????????" ?????????? ? ??????????
?? ????: User:: work _ id ????????????? ????????? @work_id,
User:: group ????????????? ????????? @ group ,
User :: assignment ????????????? ????????? @ assignment.
User :: assignment ? ????Direction ??????? Output, ??? ?????????? ??????? ????????? ?? ??, ??? ???? ???????? ????? ??????? ??????.
«??????» ???????? ????????? Execute SQL Task, ??? ? ???? SQLStatement ????? ???????? ????????? ???:
INSERT INTO Staging1 ([RANGE], [VALUE]) SELECT ST.[RANGE], ST.[VALUE] from Sample.dbo.SourceTable ST where ST.[Range] = @assignment
??? @assignment ????? ?????????????? ? ?????????? User :: assignment
??? ?????????? ? ???.
?????? ????? ???. ? ????????? ? ???, ??? ???????? ?????????? ????? Execute SQL Task.
?????, ????????? ?????????? ?????????? ??????? ?????????? ?? ???? Document Flow.
? ???? ?????????? ?????? ??? ???? ???? ????????, ????????? ?? ?????? ?? ????????? ??????? ? ??????? ? ?????????????? ??????? ? ??, ??????? ?? ????????? ????????? ? ?????????????? ???????.
??? ???? ??????? ? ??????? ?????????? ??????? ???????????, ??????? ? SSIS ???????? ?????????????? ??? ???:
