Answered by:
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
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 -
Thursday, March 22, 2018 1:07 AM
All replies
-
-
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 PageWednesday, 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 -
-
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 -
-
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 :
- DataFlow task, i will use 'OLE DB Source' to pull this FoxPro table.
- 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 -
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