I covered this pattern on https://stackoverflow.com/a/34868545/181965 but you're looking for a package that looks something like this
A sequence container that contains everything that one of those 8 discrete buckets of work would require. In your case, a Variable for
- CurrentPackage String
- rsObject Object
- ContainerId Int32
The containerId will be the values 0 through 7 (since you have 8 buckets of work). As outlined in the other answer, we must scope the variables to the Sequence Container. The default in 2012+ is to create them at the Control Flow level, whereas 2005/2008 would create them at the level of the selected object.
Set up
I created a table and loaded it with 200 rows
CREATE TABLE dbo.so_35415549
(
id int IDENTITY(1,1) NOT NULL
, PackageName sysname
);
INSERT INTO
dbo.so_35415549
(
PackageName
)
SELECT TOP 200
'Package' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS varchar(3))
FROM
sys.all_columns AS AC;
Get My Bucket's data
The modulus, modulo, mod whatever you call it operator is our friend here. The mod operator will return the remainder after division. e.g. 10 mod 3 is 1 because 3*3 + 1 = 10
In your case, you'll be modding via 8 so you know the remainder will be bounded between 0 and 7.
SQL Server implements the mod operator as % and you can test the correctness via the following query
SELECT
S.id
, S.PackageName
, S.id % 8 AS ModValue
FROM
dbo.so_35415549 AS S
ORDER BY
1;
Sample output
id PackageName ModValue
1 Package1 1
2 Package2 2
3 Package3 3
4 Package4 4
5 Package5 5
6 Package6 6
7 Package7 7
8 Package8 0
9 Package9 1
10 Package10 2
...
199 Package199 7
200 Package200 0
SQL Get Work List
Using the above query as a template, we will use the following query. Notice the ? in there. That is the placeholder for an Execute SQL Tasks parameterization for an OLE DB Connection Manager.
SELECT
S.PackageName
FROM
dbo.so_35415549 AS S
WHERE
S.id % 8 = ?
ORDER BY
1;
The Parameter we pass in will be @[User::ContainerId]
The Result Set option will be updated from None to Full ResultSet and we push the value into rsObject
FELC Shred Work List
This is a standard shredding of a recordset. We got our variable populated in the previous step so let's enumerate through the results. There will be one column in our result set and you will map that to User::CurrentPackageName
EPT Run Package
This is your Execute Package Task. Use the value of CurrentPackageName and you're set.
