SQL Server BIT data type reports differently for V

I need to export data from SQL Server 2012 based on a view. While testing the export for a downstream system, I was manually extracting the data out of the table that the view is based on and the BIT data type columns were reporting as 1/0.

However, once I setup the view against the table, I noticed that the BIT data type columns reported as TRUE/FALSE. This happens whether I perform a select against the view or export from it.

Why does this happen and how can I maintain the same results in the view as the data table (1/0)?

回答1:

The bit data type is interpreted by clients differently. SSMS, will report back a 1 or 0 for a bit while the same 1/0 is interpreted by an SSIS's Data Flow as True or False.

Whether the source is a table or a view makes no matter for SSIS unless you explicitly change the data type.

For setup, I created 2 tables and a view

CREATE TABLE dbo.BaseTable
(
    SomeBit bit NOT NULL
,   RowDescription varchar(50) NOT NULL
);
CREATE TABLE dbo.TargetTable
(
    SomeBit bit NOT NULL
,   RowDescription varchar(50) NOT NULL
,   SourcePackage nvarchar(100) NOT NULL
);
GO
CREATE VIEW dbo.MyView
AS
SELECT
    BT.SomeBit
,   BT.RowDescription
FROM
    dbo.BaseTable AS BT;
GO
INSERT INTO 
    dbo.BaseTable
(
    SomeBit
,   RowDescription
)
VALUES
    (CAST(0 AS bit), 'Falsification')
,   (CAST(1 AS bit), 'True dat');
GO

At this point, if I use SSMS and query either dbo.BaseTable or dbo.MyView, I will get back a 1 and 0. But again, these are just artifacts of presentation. In C, 0 is false and any numeric value that isn't 0 is true. Excel will present it as FALSE and TRUE. Every client will interpret the value into whatever the local representation of a boolean value is. SSIS chose True and False.

I built out a simple package that pulls data from BaseTable or MyView and writes it to a text file and a table.

The basic control flow looks thus

The data flow looks complex but it's not.

I select from either my table or view, add a description for my target table, use a multicast so I can send the same data to multiple destinations and then write to a file and table.

If I query SSMS for my sources and destinations, you'll see that the destination libraries handle the translation between the local and foreign representation of the data type.

There is no such translation available for a flat file because there's no "standard" for the representation of a boolean. I might like Y/N. Even so, the

I tried a number of things to coerce a 1/0 to be written to the flat file. I set my data types to

Boolean DT_BOOL
Single byte signed int DT_I1
Four byte signed int DT_I4
String DT_STR

but it never mattered (which actually seems odd given how persnickety SSIS is about data types) --- my output was always the same

False,Falsification
True,True dat

Ultimately, if I wanted a 0 or a 1 in that output file, I needed to change my data type: either in the source query with an explicit cast or through a Derived Column component using the ternary operator SomeBit ? (DT_I1)1 : (DT_I1)0. Use DT_I1/I2/I4/I8 as you see fit

Fun trivia note: if you chose to use the Data Conversion component you're going to get 0 for False, -1 for True or if you use a lazy cast in the Derived Component (DT_I1) SomeBit It seems they follow the C interpretation of boolean values.

Biml it

No need to take my word for it. Using the above table definitions and population of values, if you install the free addon BIDS Helper you can generate the same code for any version of SSIS.

After installing BIDS Helper, right click on an SSIS project and in the context menu, select Add Biml file. Replace the contents of that file with the below code; save and then right-click to generate a new package.

You will need to edit the values for the Flat File Connection to point to valid locations as well as point the ole db connection string to wherever you spin up your tables.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <FlatFileConnection FilePath="C:\ssisdata\so_29244868.table.csv" FileFormat="FFF_table" Name="FF_Table" />
        <FlatFileConnection FilePath="C:\ssisdata\so_29244868.view.csv" FileFormat="FFF_table" Name="FF_View" />
        <OleDbConnection Name="CM_OLE" ConnectionString="Data Source=localhost\dev2014;Initial Catalog=tempdb;Provider=SQLNCLI11.0;Integrated Security=SSPI;" />
    </Connections>
    <FileFormats>
        <FlatFileFormat
            Name="FFF_table" IsUnicode="false" CodePage="1252"
            FlatFileType="RaggedRight">
            <Columns>
                <Column Name="SomeBit" DataType="Boolean" Delimiter="," />
                <Column Name="RowDescription" DataType="AnsiString" Length="50" Delimiter="CRLF"/>
            </Columns>

        </FlatFileFormat>
    </FileFormats>
    <Packages>
        <Package ConstraintMode="Parallel" Name="so_29244868">
            <Tasks>
                <Dataflow Name="DFT Table example">
                    <Transformations>
                        <OleDbSource ConnectionName="CM_OLE" Name="OLE_SRC dbo_BaseTable">
                            <ExternalTableInput Table="dbo.BaseTable" />
                        </OleDbSource>
                        <DerivedColumns Name="DER Package name">
                            <Columns>
                                <Column DataType="String" Name="SourcePackage" Length="100">"DFT Table example"</Column>
                            </Columns>
                        </DerivedColumns>
                        <Multicast Name="MC Dupe">
                            <OutputPaths>
                                <OutputPath Name="FF" />
                                <OutputPath Name="Table" />
                            </OutputPaths>
                        </Multicast>
                        <FlatFileDestination ConnectionName="FF_Table" Name="FF_DST table">
                            <InputPath OutputPathName="MC Dupe.FF" />
                        </FlatFileDestination>
                        <OleDbDestination 
                            ConnectionName="CM_OLE" 
                            Name="OLE_DST Table"
                            TableLock="false">
                            <InputPath OutputPathName="MC Dupe.Table" />
                            <ExternalTableOutput Table="[dbo].[TargetTable]"></ExternalTableOutput>
                        </OleDbDestination>
                    </Transformations>
                </Dataflow>
                <Dataflow Name="DFT View example">
                    <Transformations>
                        <OleDbSource ConnectionName="CM_OLE" Name="OLE_SRC dbo_MyView">
                            <ExternalTableInput Table="dbo.MyView" />
                        </OleDbSource>
                        <DerivedColumns Name="DER Package name">
                            <Columns>
                                <Column DataType="String" Name="SourcePackage" Length="100">"DFT View example"</Column>
                            </Columns>
                        </DerivedColumns>
                        <Multicast Name="MC Dupe">
                            <OutputPaths>
                                <OutputPath Name="FF" />
                                <OutputPath Name="Table" />
                            </OutputPaths>
                        </Multicast>
                        <FlatFileDestination ConnectionName="FF_View" Name="FF_DST view">
                            <InputPath OutputPathName="MC Dupe.FF" />
                        </FlatFileDestination>
                        <OleDbDestination 
                            ConnectionName="CM_OLE" 
                            Name="OLE_DST view"
                            TableLock="false"
                            >
                            <InputPath OutputPathName="MC Dupe.Table" />
                            <ExternalTableOutput Table="[dbo].[TargetTable]"></ExternalTableOutput>
                        </OleDbDestination>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

回答2:

I've run into the same problem using Entity Framework.

Try casting the bit field to a bit.