Encoded text field in Foxpro truncated with no error

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Encoded text field in Foxpro truncated with...

Encoded text field in Foxpro truncated with no error

Rate Topic

Display Mode

Topic Options

Author

Message

Jay_Noob

Posted Tuesday, November 24, 2009 1:03 PM

Valued Member

Group: General Forum Members
Last Login: Monday, July 23, 2012 7:54 AM
Points: 59, Visits: 236

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

Post #824099

Jack Corbett

Posted Tuesday, November 24, 2009 1:11 PM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #824108

Jay_Noob

Posted Tuesday, November 24, 2009 1:19 PM

Valued Member

Group: General Forum Members
Last Login: Monday, July 23, 2012 7:54 AM
Points: 59, Visits: 236

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.

Post #824114

Jack Corbett

Posted Tuesday, November 24, 2009 1:46 PM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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.

Post #824129

Jay_Noob

Posted Tuesday, November 24, 2009 2:19 PM

Valued Member

Group: General Forum Members
Last Login: Monday, July 23, 2012 7:54 AM
Points: 59, Visits: 236

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.

Post #824147

Jack Corbett

Posted Tuesday, November 24, 2009 2:32 PM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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.

Post #824151

« Prev Topic | Next Topic »

Permissions