Encoded text field in Foxpro truncated with no error

Question

Encoded text field in Foxpro truncated with no error

Jay_Noob

SSCommitted

Points: 1565
More actions
November 24, 2009 at 1:03 pm

#135423

Hello all,
I have run into a snag importing data from a third party Foxpro database. I am importing from a free table file (.dbf), and can get everything but the data in a particular field in some rows. The third party encrypted the data in this field for some records but not all (please don't ask me why). The encoded value includes ASCII special characters like BEL (SELECT CHAR(7)). When the data is imported I'm losing all the characters after the first ASCII special character that is encountered in that field of a given row. e.g. #$a1*%(BEL)......nothing. I know there are more characters in the field, because I can view them when I select from the table in Visual Foxpro.
In the data flow task, I'm using an OLE DB Source with a Visual FoxPro provider. I have tried setting the ouput column data type on the OLE DB source to DT_TEXT and DT_STR, but the data is truncated for both of those types. I attempted to convert the field to DT_BYTE and binary (in the destination) but the field comes in as NULL. SSIS won't allow me to set the output data type to DT_WSTR or DT_NTEXT, and I still get truncation if I convert the field from DT_STR to DT_WSTR with a data conversion data flow transformation.
I can't be certain, but it looks like the truncation is taking place as the dbf is being read by the OLE DB source. Has anyone ever run into a situation like this? Is there a way to read the data in as
P.S. I hate Foxpro

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

I'm with you when it comes to FoxPro. Have you tried doing a conversion at the source? Cast(column as new datatype). This may work. Are you doing anything with the encryted data? Can you decrypt it on your side? If no why not skip it?

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

Jay_Noob SSCommitted Points: 1565 More actions · Answer 2

Thanks for the reply!

I haven't tried converting at the source yet, but I'm looking up the syntax right now. I eventually need to use a SQL command anyway to filter out the Deleted() (dang foxpro) records. I might as well throw a convert to varbinary on the field.

I might end up having to export the data from Foxpro to a csv or something, but I'd rather not add a step to the process if I can avoid it.

I'll update this thread if converting at the source is successful.

Thanks again.

Jack Corbett SSC Guru Points: 184393 More actions · Answer 3

I haven't had an issue with the "Deleted" rows in FoxPro using the OLE DB Driver. At least not that I am aware of. I'll have to double check now.

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

Jay_Noob SSCommitted Points: 1565 More actions · Answer 4

So, here's how it plays out.

In the OLE DB data flow source, rather than using "table or view" as the Data access mode, you have to set it as SQL command. Then you must wrap the field in a CAST() function to cast the field as varbinary.

SELECT CAST(funny_field AS VARBINARY(254))

FROM 'someFile.dbf'

I learned that in Foxpro, 254 is the largest field width you can have for a varbinary.

I haven't tried converting the varbinary data into character data before inserting into SQL Server yet, but once the varbinary data is in SQL Server, casting the varbinary back to varchar in a select seems to work.

SELECT CONVERT(varchar(255), funny_field)

FROM SQLSERVER_TABLE

On a side note, selecting the results to grid gives the impression that the data is still being truncated, but selecting the results to text will display the character string, FYI.

Thanks Jack, for the tip on converting in the source.

Jack Corbett SSC Guru Points: 184393 More actions · Answer 5

Glad I could help. Had to do that with some columns I was importing in one of my processes, so I had experience with it. Hope you don't have any date columns, FoxPro allows pretty much anything in there.

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