A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Monday, February 17, 2014

Biml - Execute Package Task File System (Package Deployment Model)

Biml - Execute Package Task File System (Package Deployment Model)

While I don't think there's a real difference between executing a package stored in SQL Server versus one stored on the file system, since the question came up on the Biml Forums.

This post contains the Biml to generate an Execute Package Task that runs a package on the file system. An Execute Package Task uses a File Connection Manager to point to the target package. Knowing that, you can make the execution configurable by either putting an Expression on the Connection Manager itself or setting the Expression within the Task itself. The following Biml demonstrates both approaches although you'd clearly only want to use one approach. I would use the Connection Manager approach if I was running packages in serial while I'd employ the Task level expression if I was running things in parallel.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <FileConnection Name="CM_File" FilePath="C:\Dropbox\Sandbox\BimlReference\BimlReference\Task_ExecutePackage_ChildPackage.dtsx" FileUsageType="ExistingFile" HasMultipleFiles="false" />
    </Connections>
    <Packages>
        <Package ConstraintMode="Linear" Name="Task_ExecutePackage_ChildPackage_FS">
        </Package>

        <Package ConstraintMode="Linear" Name="Task_ExecutePackage_Package_FS">
            <Variables>
                <Variable Name="CurrentFileName" DataType="String">C:\Dropbox\Sandbox\BimlReference\BimlReference\Task_ExecutePackage_ChildPackage_FS.dtsx</Variable>
            </Variables>
            <Connections>
                <Connection ConnectionName="CM_File">
                    <Expressions>
                        <Expression PropertyName="CM_File.ConnectionString">@[User::CurrentFileName]</Expression>
                    </Expressions>
                </Connection>
            </Connections>
            <Tasks>
                <ExecutePackage Name="EPT Run Package" DelayValidation="true">
                    <File ConnectionName="CM_File"></File>
                    <Expressions>
                        <Expression PropertyName="PackageName">@[User::CurrentFileName]</Expression>
                    </Expressions>
                </ExecutePackage>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Result

Two packages are generated, our child package and the invoker with the Execute Package Task.

Task_ExecutePackage_ChildPackage_FS

This is simply a package to ensure there's a valid package reference.

Task_ExecutePackage_Package_FS

The driver package is contains a Variable, a File Connection Manager and an Execute Package Task.

Control flow

Execute Package Task

Package tab

The Package tab looks as expected, we have a file connection manager

File connection manager

I have an Expression on the File Connection Manager. It sets the ConnectionString property to our Variable @[User::CurrentFileName].

You might also be interested in Biml - Execute Package Task (Project Deployment Model)

3 comments:

Bill Sharon said...

How would you re-factor this to handle Package Configurations while resetting the FileConnection for each child package? Thanks!

Bill Fellows said...

Are you referring to a similar question on the varigence biml forum?

Bill Sharon said...

Yes, not too much out there for multi-environment dev examples, so I posted in a few places.

Thanks for getting back!