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: http://bimlscript.com/Snippet/Details/73
<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:
<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.
Hi,
I have been having issues doing exactly this but the parameter in the stored procedure is a datetime2. When I pass a string representation of a date, all is good but obviously, I am supposed to pass a datetime to the sp. Whenever I do that, I get an error saying “string was not recognized as a valid datetime”.
Any ideas?
it worked for datetime2 column, but with the Direct SQL, not using stored procedure