Search code examples
sqlssisssis-2012

Dynamically Generate file connection for several packages in SSIS

In a project we have several SSIS packages (around 200), all the package names are stored in a control table. We need to create a master package which can run all the 200 packages.

Since the max concurrent executable setting was set to 8. So planning to create 8 execute package tasks in a container and was thinking of generating the connection string(Execute package task- File connection String) dynamically using the package names stored in the table.

The control table is in the below format

Id PackageName
---------------
1  Package1
2  Package2

Ideas on how should be implemented helps.

Solution

  • I covered this pattern on https://stackoverflow.com/a/34868545/181965 but you're looking for a package that looks something like this

    enter image description here

    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.