0
votes

How can you dynamically set the PackageName in an Execute Package Task?

I've select the list of packages into a user variable, and got a ForEach loop with a script task showing the value I'm after in there in ... Dts.Variables["User::TgtPackage"].Value.ToString()

But I've tried using ...

  • User::TgtPackage
  • Dts.Variables["User::TgtPackage"].Value.ToString()

in the PackageName, but neither work

1

1 Answers

3
votes

Since you're on 2008, the way in which you're referencing a package in the Execute Package Task is either on the File System or in SQL Server. The Execute Package task is then using your Connection Manager

In the case of File based connection managers, you need to set an Expression on the file Connection Manager's ConnectionString property. Right click on the Connection Manager, in the Properties pane find your Expressions section, click the ellipses and map ConnectionString to @[User::TgtPackage] (assuming the value of TgtPackage is a fully qualified path)

In the case of SQL Server based packages, you will need to set an Expression on the Execute Package Task's PackagePath property. Double click on your Execute Package Task, on the Expressions tab, map the PackagePath property to @[User::TgtPackage] value (assuming the value of TgtPackage is just the package name, e.g. so_26718490Target)

enter image description here

Biml

Biml is the business intelligence markup language. If you have the free addon Bids Helper, you can add a Biml file and use the following code to generate a package that uses a For Each Item Enumerator. Inside that, an Execute Package Task which uses a file based source.

For an example of Biml to configure the package name, see https://stackoverflow.com/a/21170368/181965

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <FileConnection Name="FCChild" FilePath="c:\Sandbox\SO\SO\so_26718490Target.dtsx"></FileConnection>
    </Connections>
    <Packages>
        <Package ConstraintMode="Linear" Name="so_26718490Target"></Package>
        <Package
            ConstraintMode="Linear"
            Name="so_26718490">
            <Variables>
                <Variable DataType="String" Name="TgtPackage">c:\Sandbox\SO\SO\so_26718490Target.dtsx</Variable>
            </Variables>

            <Tasks>
                <ForEachItemLoop ConstraintMode="Linear" Name="FILC Run packages">
                    <Rows>
                        <Row>
                            <Columns>
                                <Column DataType="String" Value="c:\Sandbox\SO\SO\so_26718490Target.dtsx"></Column>

                            </Columns>
                        </Row>
                    </Rows>
                    <VariableMappings>
                        <VariableMapping VariableName="User.TgtPackage" Name="0"></VariableMapping>
                    </VariableMappings>
                    <Tasks>
                        <!--
                            Execute package task
                        -->
                        <ExecutePackage Name="EPT File Child">
                            <File ConnectionName="FCChild"></File>
                        </ExecutePackage>
                    </Tasks>
                </ForEachItemLoop>
            </Tasks>
            <Connections>
                <Connection ConnectionName="FCChild">
                    <Expressions>
                        <Expression ExternalProperty="ConnectionString">@[User::TgtPackage]</Expression>
                    </Expressions>
                </Connection>
            </Connections>
        </Package>
    </Packages>
</Biml>