August 3, 2009 at 9:26 pm
novice_coder (8/3/2009)
Jack Corbett (8/3/2009)
Why are you using the JET provider when there is a FoxPro OLE DB driver? This could be your problem.hmm Beacsue I read somewhere on the internet that JET provider will work just fine for importing data. And me being lazy didnt try installing the FoxPro driver 🙁
Lemme install it and see what happens with it. Thanks for the tip
I am still getting the same results after installing the Visual FoxPro OLE DB driver. I have tried both SSIS and Import export wizard. I am not sure if this driver works for FoxPro 2.6.
I saw something interesting while I was using Import/Export wizard. After picking the source and destination, when I click Edit Mapping button and check the check boxes for Nullable for output table fields, In the bottom part of the window I see
Source Column: intfield F (3) NOT NULL and
Source Column: bitfield L NOT NULL
I wonder why its showing these two fields of the source FoxPro table as NOT NULL. I entered null values in these fields. BTW, I dont mention NULL or NOT NULL for any field when I create my FoxPro table. To create a FoxPro table I click File->New->table and then Name and type of fields.
As my name says, I am a novice in FoxPro and MS SQL server. Please let me know if you think that I am doing something wrong here.
Thanks for the help.
August 4, 2009 at 7:17 am
Your destination table will not accept a null value, that's what NOT NULL means in a table definition. So if you explicitly try to insert a NULL value then the insert will fail. Why it is not failing I don't understand, unless the data is not really a NULL when being brought across from FoxPro. One way to check is to put a DataViewer on the connector between your source and destination and view the data in BIDS\Visual Studio to see if the FoxPro NULL's are being interpreted as NULL by SSIS.
If you really want to allow NULL's in the bit column then you need to change the definition.
Alter Table [TableName]
Alter Column [ColumnName] [DataType] NULL
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 4, 2009 at 12:39 pm
Jack Corbett (8/4/2009)
Your destination table will not accept a null value, that's what NOT NULL means in a table definition. So if you explicitly try to insert a NULL value then the insert will fail. Why it is not failing I don't understand, unless the data is not really a NULL when being brought across from FoxPro. One way to check is to put a DataViewer on the connector between your source and destination and view the data in BIDS\Visual Studio to see if the FoxPro NULL's are being interpreted as NULL by SSIS.If you really want to allow NULL's in the bit column then you need to change the definition.
Alter Table [TableName]
Alter Column [ColumnName] [DataType] NULL
I wonder why would they show properties of destination column under Source Column heading. Also the type of the bit field is shown as L(for logical) and not bit. So the whole line under edit mapping windows reads Source Column: bitvar L NOT NULL. So I think they really mean source column here and not the destination table column. As far as creating NULL value fields in the destination table is concerned, I am creating the table as you mentioned. After clicking ok on edit mapping window, when i click preview, I see False for NULL bit field and 0 for null int field. I have tried using the Grid Data viewer and I am seeing same values in that too. I am using FoxPro2.6, I dont know if thats the problem.
August 4, 2009 at 12:48 pm
August 4, 2009 at 1:39 pm
rmechaber (8/4/2009)
Looks like says that FoxPro 2.6 uses blanks, not a NULL token, and that later versions of Visual FoxPro have columns whose NULL values will import in SQL as NULLs (as long as your column definitions permit it, as a previous poster mentioned).That may be your problem.
EXACTLY. And http://support.microsoft.com/kb/99095 elaborates on it. Basically there is nothing like null for int and bit fields in FoxPro2.6. Blank command sets resets everything but int and logical fields to null. It sets them to 0 and False. And since the null value for int and bit field is 0 and false(which isnt really nulll), thats what I am getting in my SQL table.
I love Microsoft.
August 4, 2009 at 1:53 pm
I don't know if you can do this with FP 2.6, but using the OLEDB provider you may be able to do a select statement as your source and explicitly cast the values to NULL, something like:
Select
IF(Empty(column), Cast(Null as DataType), column) as column
From
table
I know this works with later versions of FP.
Or you can use a derived column transform and do the change in SSIS.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 4, 2009 at 2:41 pm
Jack Corbett (8/4/2009)
I don't know if you can do this with FP 2.6, but using the OLEDB provider you may be able to do a select statement as your source and explicitly cast the values to NULL, something like:
Select
IF(Empty(column), Cast(Null as DataType), column) as column
From
table
I know this works with later versions of FP.
Or you can use a derived column transform and do the change in SSIS.
what expression do I need to write in SSIS Column transformation for this?
August 4, 2009 at 2:55 pm
It would be something like this (I might not have it perfect):
[bit_val]==False ? Null(DT_BOOL) : [bit_val]
If the datatype of the bit column is bool you may have to do a conversion to string first in order to compare to an empty string.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 4, 2009 at 4:03 pm
Jack Corbett (8/4/2009)
It would be something like this (I might not have it perfect):
[bit_val]==False ? Null(DT_BOOL) : [bit_val]If the datatype of the bit column is bool you may have to do a conversion to string first in order to compare to an empty string.
But wouldnt this convert all False values to NULL. With this not only blank field will be converted to NULL but all the fields that have false value in them. I just want the field values that are blank in my FoxPro 2.6 table to be converted to NULL and the field values that have False value in them to remain as it is.
August 4, 2009 at 4:09 pm
Note, the last sentence of my last post:
If the datatype of the bit column is bool you may have to do a conversion to string first in order to compare to an empty string.
That means if SSIS has interpreted the column as boolean you may need to convert it to string so you can compare to an empty string.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 6, 2009 at 7:00 am
Hello,
Thanks for the help but I am giving up on this. Its already taken a lot of my time. I will try to clean up the data on FoxPro side
Thanks
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply