1
Vote

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.

comments

lotsahelp wrote Feb 6 at 3:44 PM

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

dgosbell wrote Feb 6 at 7: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 at 8: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.