locked
SSIS :DT_BOOL loading "FALSE" for empty record RRS feed

  • Question

  • Hi There,

    I am trying to load FoxPro tables to SQL server using SSIS (I am using Visual Studio 2015 for ingratiation services).

    While performing loading task, I got stuck in between with DT_BOOL and DT_NUMERIC datatypes.

    Find the below explanations of the issues I am facing:

    • Issue with DT_BOOL & DT_NUMERIC datatype: Consider I am loading below FoxPro table to SQL server, where field "Name" is [DT_STR], field "Serial_num" is [DT_Numeric] and field "Permission_rev" is [DT_BOOL] datatype. In the below FoxPro table there are EMPTY RECORDS in it(Second row)

    FoxPro table

    Name

    Serial_num

    Permission_Rev

    Mr.Jack

    112

    True

    Mr.Adam

    Mr.Brad

    113

    True

    Mr.Ethan

    114

    False

    I used Data-flow task for loading this table. That is in Dataflow task, I used OLE DB source to extract FoxPro Table and populate the same table to SQL Server using OLE DB destination, when task is finished. Below mentioned table will be populated into SQL Server.

    SQL_Server table

    Name

    Serial_num

    Permission_Rev

    Mr.Jack

    112

    True

    Mr.Adam

    0

    False

    Mr.Brad

    113

    True

    Mr.Ethan

    114

    False

    Here we can see second row in the target table (SQL Server table), where it is Populated with "0" and "False" for empty records.

    My requirement is, it should load as it is in Source table (i.e., EMPTY RECORD to EMPTY RECORD) but unfortunately for DT_BOOL and DT_NUMERIC for empty records it is defaulting the values.

    I also tried using derived column, but my try was unsuccessful, I observed that in OLE DB source itself it is fetching the empty records value to default value. Is it a Driver problem or any connection manager setting problem??

    • Source Connection Manager Setting as below
      Provider :
      microsoft ole db provider for visual foxpro
      Server or File Name: "File path or Folder Location of the DBF files"
      Driver Installed for FoxPro : VFPOLEDB.1

    Did i miss anything more in connection manager?? Kindly provide a solution in such way that I can load empty record to target table for DT_NUMERIC and DT_BOOL datatypes.

    Thanks & regards

    MANJUNATH

    Wednesday, March 21, 2018 3:20 PM

Answers

  • just check for blank conditions and set to NULL as default

    like this

    ([Serial_Num] == "" ? NULL(DT_NUMERIC,<your precision>,<scale>) : [Serial_Num])
    
    
    
    ([Permission_Rev] == "" ? NULL(DT_BOOL) : [Permission_Rev])


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by msmanju99 Thursday, April 26, 2018 3:11 PM
    Wednesday, March 21, 2018 4:08 PM
  • Allow NULLs in 

    Serial_Num

    Permission_rev

    And then just in the Derived Column Transformation override the blank with NULL

    Visakh above gave you the expressions.

    You will not be able to place a space char into a BOOL or NUMERIC


    Arthur

    MyBlog


    Twitter


    • Edited by ArthurZ Thursday, March 22, 2018 1:08 AM
    • Marked as answer by msmanju99 Thursday, April 26, 2018 3:12 PM
    Thursday, March 22, 2018 1:07 AM

All replies

  • Hi msmanju99,

    Looks like you need a Data Conversion Task to take care of the blank values, you may want to set them to NULL.


    Arthur

    MyBlog


    Twitter

    Wednesday, March 21, 2018 3:51 PM
  • just check for blank conditions and set to NULL as default

    like this

    ([Serial_Num] == "" ? NULL(DT_NUMERIC,<your precision>,<scale>) : [Serial_Num])
    
    
    
    ([Permission_Rev] == "" ? NULL(DT_BOOL) : [Permission_Rev])


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by msmanju99 Thursday, April 26, 2018 3:11 PM
    Wednesday, March 21, 2018 4:08 PM
  • Hi Visakh,

    Thanks for the reply. 

    In my case, the empty records are getting filled with default values in OLE DB source itself.

    i.e., Once i Select FoxPro file in OLE DB Source editor, if i do preview in Query builder I can See default values filled in empty records (for DT_BOOL & DT_NUMERIC only).

    where in case of other DataTypes for empty records it is parsing empty only then i can use derived column and then i can write expression mentioned by you for NULL.

    but for DT_BOOL & DT_NUMERIC datatypes in the source editor itself EMPTY RECORDS are filled with "0" for DT_NUMERIC and "FALSE" for DT_BOOL. 

    Hence, i am suspecting foxpro driver settings!! 

    please share your views on this.

    Regrads

    MANJUNATH

    Wednesday, March 21, 2018 6:49 PM
  • Hi Visakh,

    Thanks for the reply. 

    In my case, the empty records are getting filled with default values in OLE DB source itself.

    i.e., Once i Select FoxPro file in OLE DB Source editor, if i do preview in Query builder I can See default values filled in empty records (for DT_BOOL & DT_NUMERIC only).

    where in case of other DataTypes for empty records it is parsing empty only then i can use derived column and then i can write expression mentioned by you for NULL.

    but for DT_BOOL & DT_NUMERIC datatypes in the source editor itself EMPTY RECORDS are filled with "0" for DT_NUMERIC and "FALSE" for DT_BOOL. 

    Hence, i am suspecting foxpro driver settings!! 

    please share your views on this.

    Regrads

    MANJUNATH

    which driver are you using?

    Are you doing any explicit casting in your source query?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, March 21, 2018 6:56 PM
  • Hi Vishak,

    I have installed VfpOleDB.dll driver to extract DBF files(FoxPro file) into SSIS.  

    and am not doing any explicit cast in source query, i am just using select * from table name (Which will come by default when we choose "Build Query").

    I tried uploading screen shot of my connection manager used for Source, unfortunately it is not uploading.

     Thanks

    Manjunath

     


    Wednesday, March 21, 2018 7:52 PM
  • I think instead of writing directly to the target table, write to staging, and then from the staging you can use SQL to set those 0 - FALSE inserted in the final destination as NULL NULL 

    Arthur

    MyBlog


    Twitter

    Wednesday, March 21, 2018 8:35 PM
  • HI Arthur,

    If I write to staging also it will load default values into Empty records,then how to identify between "real values" and "default value"? It will be challenging then!!

    Thanks

    MANJUNATHA

    Wednesday, March 21, 2018 8:52 PM
  • Don't you get this pattern:

    Mr.Adam

    0

    False

    ?

    Arthur

    MyBlog


    Twitter


    • Edited by ArthurZ Wednesday, March 21, 2018 9:15 PM
    Wednesday, March 21, 2018 9:14 PM
  • Hi Arthur,

    Consider the below FoxPro Table, in which for SI_NUM '3' and '5' there are empty records in the fields 'Serial_Num'(DT_NUMERIC) and 'Permission_rev'(DT_BOOL). This is how it looks in FoxPro with SI_NUM '3'&'5' with empty records.

    SI_NUM

    Name

    Serial_Num

    Permission_rev

    1

    Ram

    1

    True

    2

    Adam

    1

    True

    3

    Eric

    0

    False

    3

    Stev

    4

    Brad

    0

    False

    5

    Ethan

    6

    Will

    1

    True

    now, my task is to load this foxpro table to SQL Server as it is. for this i am using SSIS.

    In SSIS :

    1. DataFlow task, i will use 'OLE DB Source' to pull this FoxPro table.
    2. In OLE DB Source, i use 'Build Query' so that i can select this foxpro table and there is 'view' option in build query. if i view the table, i will get below as result. I still not loaded to destination or i did not do any casting, i am just viewing the table.

    SI_NUM

    Name

    Serial_Num

    Permission_rev

    1

    Ram

    1

    True

    2

    Adam

    1

    True

    3

    Eric

    0

    False

    3

    Stev

    0

    False

    4

    Brad

    0

    False

    5

    Ethan

    0

    False

    6

    Will

    1

    True

    it is defaulting records in SI_NUM 3 & 5 for fields 'Serial_Num'(DT_NUMERIC) and 'Permission_rev'(DT_BOOL).

    In this case, it is very hard to identify between 'Real value' and 'default value' - considering table with one million records.

    kindly suggest!!

    Thanks

    MANJUNATH

     

    Wednesday, March 21, 2018 9:27 PM
  • Yes, I do get this pattern.

    Regards

    MANJUNATH

    Wednesday, March 21, 2018 9:42 PM
  • Allow NULLs in 

    Serial_Num

    Permission_rev

    And then just in the Derived Column Transformation override the blank with NULL

    Visakh above gave you the expressions.

    You will not be able to place a space char into a BOOL or NUMERIC


    Arthur

    MyBlog


    Twitter


    • Edited by ArthurZ Thursday, March 22, 2018 1:08 AM
    • Marked as answer by msmanju99 Thursday, April 26, 2018 3:12 PM
    Thursday, March 22, 2018 1:07 AM
  • We have option "retain null values from the source as null values in the data flow" in only 'Flat File Source editor'.

    But i am using 'OLE DB Source editor', in which this option is not there. Since i am pulling FoxPro .DBF files i should use 'OLE DB Source editor'.

    find the below link for the details, i have marked the DEFAULT VALUES

    https://social.msdn.microsoft.com/Forums/getfile/1243023

    Regards

    MANJUNATH

    Thursday, March 22, 2018 3:28 AM