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

Find ramblings

Wednesday, February 19, 2014

Biml - Execute SQL Task

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.

SQL ADO

General

Parameter Mapping

Result Set

SQL ODBC

General

Parameter Mapping

Result Set

SQL OLEDB

General

Parameter Mapping

Result Set

References

No comments: