Geek asks and answers

How to store DT_R8 in a variable in SSIS

I have an SSIS package to read data from a source table with a float data type. It displays as DT_R8 in SSIS (under external columns), I want to save the value in a variable in the SSIS package, and I use a double data type, but it does not work and throws this error ...

The type of the value assigned to the variable "User :: varDBVAL" is different from the current type of the variable

Can someone suggest a possible solution, I could only find a data type mapping between SSIS and SQL Server, but not for data types in SSIS.

Thanks, John

+3
source share
4 answers

. sql execute: POC Maverick , , , , .

enter image description here

, 4 , : Mav, Query, QueryMav varDBVAL. Mav - , 0. , - , , .

, , , . , , Execute SQL, @[User:: Maverick] . 1 , 0

enter image description here

SQL- float/double .

enter image description here

"" @[User::varDBVAL],

enter image description here

Biml

BIDS Helper, , SQL Server, , float/double SSIS.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="CM_OLE" ConnectionString="Data Source=localhost\dev2012;Initial Catalog=tempdb;Provider=SQLNCLI10.1;Integrated Security=SSPI;"></OleDbConnection>
    </Connections>
    <Packages>
        <Package Name="so_2176077" ConstraintMode="Linear">
            <Variables>
                <Variable DataType="Double" Name="varDBVAL">0</Variable>
                <Variable DataType="String" Name="Query">SELECT CAST(1.0123 AS float) AS dbVal </Variable>
                <Variable DataType="Int32" Name="Mav" EvaluateAsExpression="true">0</Variable>
                <Variable DataType="String" Name="QueryMav">SELECT 1 AS Mav</Variable>
            </Variables>
            <Tasks>
                <ExecuteSQL ConnectionName="CM_OLE" Name="SQL MavTest" ResultSet="SingleRow">
                    <VariableInput VariableName="User.QueryMav"></VariableInput>
                    <Results>
                        <Result VariableName="User.Mav" Name="0"></Result>
                    </Results>
                </ExecuteSQL>

                <ExecuteSQL ConnectionName="CM_OLE" Name="SQL AssignValue" ResultSet="SingleRow">
                    <VariableInput VariableName="User.Query"></VariableInput>
                    <Results>
                        <Result VariableName="User.varDBVAL" Name="0"></Result>
                    </Results>
                </ExecuteSQL>
            </Tasks>
        </Package>
    </Packages>
</Biml>

, 2012 2008.

enter image description here

CAST , , . , , , float.

2012 proc sys.sp_describe_first_result_set sys.dm_exec_describe_first_result_set. , , .

SELECT 
    T.name AS column_name
,   T.column_ordinal
,   T.system_type_name
FROM 
    sys.dm_exec_describe_first_result_set(N'SELECT 1.0123 AS Defaults, CAST(1.10123 AS float) AS Explicits;', NULL, NULL) AS T;

1.0123 (5,4) , float.

enter image description here

2005/2008 , SELECT ... INTO dbo.Temporary WHERE NULL = NULL, .

+2

-

1) User:: varDBVA > Evaluate.

2) EvaluateAsExpression TRUE

3) .

-1

enter image description here

enter image description here

, . , !!!

-1
source

Try the following steps:

  • Create an SSIS variable. enter image description here

  • Drag the Execute SQL task and configure as shown below. enter image description here

  • Map options as shown below. enter image description here

  • Run the package now, saving the breakpoint in Post Execute. Check the value for this variable. As shown below. enter image description here

And let me know your comments / results.

-2
source

More articles:


All Articles