SSIS :DT_BOOL loading "FALSE" for empty record

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

 

Answer 6

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

Answer 7

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

Answer 8

Don't you get this pattern:

Mr.Adam

0

False

?

Arthur

MyBlog

Twitter

Answer 9

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

 

Answer 10

Yes, I do get this pattern.

Regards

MANJUNATH

Answer 11

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

Answer 12

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