Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help importing Paradox data


Need help importing Paradox data

Author
Message
Leon Chalnick
Leon Chalnick
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 39
Howdy,

I've not had much success importing Paradox data to SQL Server 2008 (to which I'm quite new). Seems to be a problem with importing memo fields.

I have created and ODBC data source on my client machine using "Driver do Microsoft Paradox (*.db)."

Using the Import / Export Wizard, I tell it to use the .NET Framework data provider for Odbc and the SQL Server Native Client 10. I tell the wizard to "copy data from one or more tables..."

I'm able to preview the data. When I look at the Wizard's mappings, it wants to turn the Paradox Memo type fields into SQL Server text fields. I'd like to tell it it to use varchar(max) but it's not clear how to do that in the wizard.

I get this message "[Source Information] Cannot locate the mapping file to map the provider types to SSIS types" as the next step in the Wizard. When I look in this directory:

C:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles

I see that there is indeed no mapping file that mentions paradox in its name. Could this be part of the problem? I can probably copy one of the existing mapping files, rename it and tweak it...but how do I get the Wizard to recognize that the new mapping file as the one to use?

Any other clues would be appreciated...
Mary Mathias
Mary Mathias
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 165
I see that it has been over a month since your post and see no replies to this. Have you had any success in this area? I ask because I am about to dive head first into altering the MappingFiles and the DtwTypeConversion.xml file for SSIS.

I have come across only a few articles on this topic that are "weedy enough"
and am including below the two that have been most informative for me, (so far at least):

http://www.simple-talk.com/sql/ssis/sql-server-2008-ssis-cribsheet/
(look under "Data Integration" ...especially look immediately above "Date/Time Data Types"... for the DtwTypeConversion.xml info.)

and

http://support.microsoft.com/kb/2152728

Both of these refer to the Wizard picking up the data types for change options during a Wizard driven SSIS import/export.

I've only just begun looking into this myself so please forgive me if I am under-informed or missing the point completely. (and please do let me know if that is the case!)

Currently, I find the data in the two above articles informative and helpful, but perhaps not fully applicable when applied across the board for the various levels of the BI Stack.

What I'm really wanting is to alter the "look here first" capability of SSIS internals to alleviate the constant "Gotta Convert" pick and choose that takes up so much time for one-time data grabs AND through the BIDS environment too. For me, it seems that 9.9 times out of 10, I am constantly having to transform because the data types 'cannot be converted' seamlessly, or are being converted to a data type I do not want. I would love to create a set of mapping files that would bring up my own preferred mapping scenarios as the top-choice default so that a "no explicit conversion needed" data type mapping is the true default both environments, with or without the Wizard. (but I'd certainly settle for just improving the "Wizard experience" if possible!)

If anyone else out there is having grins and giggles with this, I'd love to know about it!

Thanks!
~ MJ Mathias
Leon Chalnick
Leon Chalnick
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 39
No, made no progress on this per se. What I wound up doing was importing the Paradox data into an old copy of MS Access 2003. From there, I was indeed able to import it into SQL Server with only minor issues.

Access (and SQL Server, IIRC) is a little bit fussier than Paradox regarding date values. So I encountered some user-entered dates in Paradox data that caused some problems. (As I recall, they were out of range for smallDateTime...something like that) but this wasn't too hard to deal with.

Yes, it is a real PITA that you cannot (or at least I couldn't) import the Paradox data directly and had to resort to this intermediate step...but it least it got the job done, inelegant though it may be ;-)
Mary Mathias
Mary Mathias
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 165
I'm sorry to say that I am not surprised at your solution. More and more I find work-arounds resorting to Access. Not an optimal solution when in an Enterprise Data environment yet the functionality it can seamlessly perform is still lacking in the Enterprise apps. Integrated date formatting is indeed a major sore point between SQL Server, SSIS, and the Microsoft Office applications.
If I ever get the hack I have in mind to work, I will be sure to let you know. Deal?
~ MJ
Leon Chalnick
Leon Chalnick
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 39
Sounds good. Cool
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