SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Encoded text field in Foxpro truncated with no error


Encoded text field in Foxpro truncated with no error

Author
Message
Jay_Noob
Jay_Noob
SSChasing Mays
SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)

Group: General Forum Members
Points: 637 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
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)

Group: General Forum Members
Points: 78606 Visits: 14969
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Jay_Noob
Jay_Noob
SSChasing Mays
SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)

Group: General Forum Members
Points: 637 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.
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)

Group: General Forum Members
Points: 78606 Visits: 14969
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Jay_Noob
Jay_Noob
SSChasing Mays
SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)

Group: General Forum Members
Points: 637 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.
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)

Group: General Forum Members
Points: 78606 Visits: 14969
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search