For any SSIS project that contains more than a few packages chances are you have a master (or coordinator) package that controls execution of various packages within your project. In this post I’ll show you how to use Package Parameters with BIML.
When I first started searching around on how to do this, I can across this sample on the BIMLScript.com site: [embed]http://bimlscript.com/Snippet/Details/73[/embed]
1
2
3
4
5
6
7
8
9
10
11
12
|
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
<Package Name="PackageParameters" ConstraintMode="Parallel">
<Parameters>
<Parameter Name="PackageParamStr" DataType="String">Test</Parameter>
</Parameters>
<Variables>
<Variable Name="TestPackageParam" DataType="String" EvaluateAsExpression="true">@[$Package::PackageParamStr]</Variable>
</Variables>
</Package>
</Packages>
</Biml>
|
In this sample the value of the parameter is being assigned to a package variable, which is then used within the package. Unfortunately I couldn’t get this working so had to keep looking for another solution. Trying to use the parameter directly proved to be an exercise in experimentation. After a few different attempts it turned out to be very simple, here is an example of using a Package Parameter with an OLE DB Source:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
<Package Name="PackageParameters" ConstraintMode="Parallel">
<Parameters>
<Parameter Name="PackageParamStr" DataType="String">Test</Parameter>
</Parameters>
<Tasks>
<Dataflow Name="DFT - Move some data around">
<Transformations>
<OleDbSource ConnectionName="Source" Name="SRC - Client">
<DirectInput>EXEC ETL.GetSomeData @sp_param1 = ?</DirectInput>
<Parameters>
<Parameter VariableName="PackageParamStr" Name="@sp_param1" />
</Parameters>
</OleDbSource>
</Transformations>
</Dataflow>
</Tasks>
</Package>
</Packages>
</Biml>
|
Line 13 shows how to use the package parameter as a parameter for the stored procedure being called by the OLE DB Source.
🍪 I use Disqus for comments
Because Disqus requires cookies this site doesn't automatically load comments.
I don't mind about cookies - Show me the comments from now on (and set a cookie to remember my preference)