1

Closed

BIML: Execute SQL Parameter DataType Mismatch

description

Given the following BIML:
<ExecuteSQL Name="SQL Log Row Count" ConnectionName="MyDb">
    <PrecedenceConstraints>
        <Inputs>
            <Input OutputPathName="DFT Stage Incoming File.Output" EvaluationValue="Success"></Input>
        </Inputs>
    </PrecedenceConstraints>
    <DirectInput>INSERT INTO dbo.ImportLog ([Filename],[QtyRows],[StageTime]) VALUES(@Filename,@QtyRows,SYSDATETIME())</DirectInput>
    <Parameters>
        <Parameter Name="@Filename" DataType="AnsiString" VariableName="User.File"></Parameter>
        <Parameter Name="@QtyRows" DataType="Int32" VariableName="User.FileRows"></Parameter>
    </Parameters>
</ExecuteSQL>
The parameter @Filename gets translated to datatype 200 in SSIS instead of varchar.
Closed Mar 17 at 8:45 PM by furmangg

comments

lotsahelp wrote Feb 6, 2014 at 2:44 PM

It appears that AnsiStringFixedLength will map to VARCHAR which should map to CHAR.

dgosbell wrote Feb 6, 2014 at 6:39 PM

You haven't said what data type you are trying to map to.

If you are looking for a fixed length string have you looked at the following example http://bimlscript.com/Snippet/Details/102 which sets the length property on the parameter object

lotsahelp wrote Feb 6, 2014 at 7:29 PM

The parameter @Filename gets translated to datatype 200 in SSIS instead of varchar.
I was trying to map to varchar and oddly AnsiStringFixedLength was the only BIML type to map to it.

furmangg wrote Mar 17 at 8:44 PM

The following Biml appears to work in BIDS Helper 1.7.0. Can you test yours again and let us know if it's still broken?
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <AdoNetConnection Name="MyDb" ConnectionString="Data Source=.;Initial Catalog=tempdb;Integrated Security=True;" Provider="System.Data.SqlClient.SqlConnection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" CreateInProject="false" />
    </Connections>
    <Packages>
        <Package Name="datatypes" ConstraintMode="Linear" AutoCreateConfigurationsType="None">
            <Tasks>
                <!--
                setup this demo:
                create table tempdb.dbo.ImportLog ([Filename] varchar(100),[QtyRows] int,[StageTime] datetime) 
                -->
                <ExecuteSQL Name="SQL Log Row Count" ConnectionName="MyDb">
                    <DirectInput>INSERT INTO dbo.ImportLog ([Filename],[QtyRows],[StageTime]) VALUES(@Filename,@QtyRows,SYSDATETIME())</DirectInput>
                    <Parameters>
                        <Parameter Name="@Filename" DataType="AnsiString" VariableName="User.File"></Parameter>
                        <Parameter Name="@QtyRows" DataType="Int32" VariableName="User.FileRows"></Parameter>
                    </Parameters>
                </ExecuteSQL>
            </Tasks>
            <Variables>
                <Variable DataType="String" Name="File">x</Variable>
                <Variable DataType="Int32" Name="FileRows">0</Variable>
            </Variables>
        </Package>
    </Packages>
</Biml>