none
Bit datatype in SQL SSIS Load

    Question

  • Hello All,

    I have a table in SQL server database, which has four columns, first two and last columns are integer datatype. The third column is BIT datatype.

    I have following flat file from customer to load to above table. I have created a dataflow with "Flat file Source"->"OLE DB Destination"->"Enhanced Error Flow"->finally to "Error Flat file Destination". All rows go directly to error file. In the error file, I get following error for every row...I am just giving only one row of the error file, than all rows to make it simple. But the error same "No status is available" also, error column name is blank. What am I doing wrong? and What should I do to make it work?

    Thanks,

    Suresh


    Error File:

    576635,576635,True,576635,-1071607685,0,,No status is available.

    Data File:

    OBJ_RID,CUR_OBJ_RID,CUR_HAS_RLS,CUR_OBJ_THD
    576635,576635,True,576635
    576636,576636,True,576636
    576637,576637,True,576637
    576638,576638,True,576638
    576639,576639,True,576639
    576640,576640,True,576640
    576641,576641,True,576641
    576642,576642,True,576642
    576643,576643,True,576643
    576644,576644,True,576644
    576645,576645,True,576645
    576646,576646,True,576646
    576647,576647,True,576647
    576647,576647,True,576647
    576649,576649,True,576649
    576650,576650,True,576650
    Monday, December 07, 2009 11:19 PM

Answers

  • Thanks Todd, I am not able to fix it with OLE DB connection, instead I went with OLE DB Command and it worked. I gave value 1 for True and value 0 for false to handle bit datatype and it worked.

    Thanks,

    Suresh
    • Marked as answer by Suresh SSIS Tuesday, December 08, 2009 10:11 PM
    Tuesday, December 08, 2009 10:11 PM

All replies

  • What data types are being read from the flat file?  By default, SSIS configures each column as a string.

    You should convert those columns to the appropriate data types before passing them to SQL Server, because (for example) "True" != 0.  Bit columns in SQL are not true/false boolean columns, although they are frequently treated that way (even by SSMS).  You can convert the values you're reading by using a Derived Column component, and may even be able to do so by using a Data Conversion component.


    Todd McDermid's Blog
    Monday, December 07, 2009 11:29 PM
  • The flat file source mapped the true value column automatically to Boolean [DT_BOOL] Datatype. Do I still need the Derived Column or Data Conversion component? If so, what do I convert too?

    Is it possible to see the Internal Insert statement that SSIS formulates to fire at the SQL server database?

    Thanks,

    Suresh
    Monday, December 07, 2009 11:41 PM
  • Rereading your post, the errors are generated by the OLE DB Destination, not the Flat File Source?  That is a little odd.

    You can see the statements that the destination is generating by using SQL Profiler.  If DT_BOOL isn't working, I'd try converting that column to a DT_UI1.


    Todd McDermid's Blog
    Monday, December 07, 2009 11:44 PM
  • Thanks Todd, I am not able to fix it with OLE DB connection, instead I went with OLE DB Command and it worked. I gave value 1 for True and value 0 for false to handle bit datatype and it worked.

    Thanks,

    Suresh
    • Marked as answer by Suresh SSIS Tuesday, December 08, 2009 10:11 PM
    Tuesday, December 08, 2009 10:11 PM