Partager via


Передача параметров в 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 ???????? ?????????????? ??? ???: