Biml - Execute SQL Task

The Execute SQL Task allows an SSIS package to run an arbitrary SQL statement. You can parameterize it and return result sets as you please. This generates a package with three different connection type managers and Execute SQL Tasks using each of them. The Tasks demonstrate parameterization using the different syntax and how to pull back a result set. I'm going to create an SSIS Variable to hold the parameterized query, our parameter and our result set object.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <AdoNetConnection Name="CM_ADO_DB" ConnectionString="Data Source=localhost\dev2012;Integrated Security=SSPI;Connect Timeout=30;Database=msdb;" Provider="SQL"  />
        <OdbcConnection Name="CM_ODBC" ConnectionString="Driver={SQL Server};Server=localhost\dev2012;Database=tempdb;Trusted_Connection=Yes;" />
        <OleDbConnection Name="CM_OLE" ConnectionString="Data Source=localhost\dev2012;Initial Catalog=tempdb;Provider=SQLNCLI11.0;Integrated Security=SSPI;"/>
    </Connections>

    <Packages>
        <Package ConstraintMode="Linear" Name="Task_ExecuteSQL">
            <Variables>
                <Variable Name="QueryADONet" DataType="String">SELECT D.Col1 FROM (SELECT @Parameter ) D(Col1) CROSS APPLY(VALUES (1), (2), (3)) C(C1);</Variable>
                <Variable Name="QueryODBC" DataType="String">SELECT D.Col1 FROM (SELECT ? ) D(Col1) CROSS APPLY(VALUES (1), (2), (3)) C(C1);</Variable>
                <Variable Name="QueryOLEDB" DataType="String">SELECT D.Col1 FROM (SELECT ? ) D(Col1) CROSS APPLY(VALUES (1), (2), (3)) C(C1);</Variable>
                <Variable Name="parameter" DataType="String">Hello world</Variable>
                <Variable Name="rsObject" DataType="Object"></Variable>
            </Variables>

            <Tasks>
                <ExecuteSQL 
                    ConnectionName="CM_ADO_DB" 
                    Name="SQL ADO"
                    ResultSet="Full">
                    <VariableInput VariableName="User.QueryADONet" />
                    <Parameters>
                        <Parameter 
                            DataType="String" 
                            VariableName="User.parameter" 
                            Name="@Parameter"></Parameter>
                    </Parameters>
                    <Results>
                        <Result 
                            Name="0" 
                            VariableName="User.rsObject"/>
                    </Results>
                </ExecuteSQL>

                <!--
                This is not behaving as expected. 
                Not specifying a length results in 
                "Invalid Precision Value Err w/ Character Field Parameters"
                http://support.microsoft.com/kb/132960
                The mapping between a string doesn't go smooth so
                we need to prvide the target data type explicitly.
                I did this by manually setting the data type in
                an existing SSIS package and viewing the code
                find SQLTask:DataType
                See also
                https://twitter.com/BimlScript/status/431798702403903489

                After fixing that, the next problem is the resultset.
                The query generates data correctly but the assignment
                back does not behave as expected.

                declare @p1 int
                set @p1=-1
                exec sp_prepexec @p1 output,N'@P1 varchar(50)',N'SELECT D.Col1 FROM (SELECT @P1 ) D(Col1) CROSS APPLY(VALUES (1), (2), (3)) C(C1);','H'
                select @p1                

-->
                <ExecuteSQL
                    ConnectionName="CM_ODBC"
                    Name="SQL ODBC"
                    ResultSet="Full">
                    <VariableInput VariableName="User.QueryODBC" />
                    <Parameters>
                        <Parameter
                            DataType="String"
                            VariableName="User.parameter"
                            DataTypeCodeOverride="12"
                            Length="50"
                            Name="1"></Parameter>
                    </Parameters>
                    <Results>
                        <Result
                            Name="0"
                            VariableName="User.rsObject"/>
                    </Results>
                </ExecuteSQL>
                
                <ExecuteSQL
                    ConnectionName="CM_OLE"
                    Name="SQL OLEDB"
                    ResultSet="Full">
                    <VariableInput VariableName="User.QueryOLEDB" />
                    <Parameters>
                        <Parameter
                            DataType="String"
                            VariableName="User.parameter"
                            Name="0"></Parameter>
                    </Parameters>
                    <Results>
                        <Result
                            Name="0"
                            VariableName="User.rsObject"/>
                    </Results>
                </ExecuteSQL>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Result

We generate an SSIS package with an ADO.NET, ODBC and OLEDB connection manager. There are three Execute SQL Tasks in the package and you can see how it's configured.