Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help importing Paradox data Expand / Collapse
Author
Message
Posted Monday, December 21, 2009 5:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 20, 2014 10:10 AM
Points: 5, Visits: 31
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...
Post #837668
Posted Friday, February 4, 2011 4:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 2:27 PM
Points: 6, Visits: 115
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
Post #1059082
Posted Friday, February 4, 2011 4:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 20, 2014 10:10 AM
Points: 5, Visits: 31
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
Post #1059086
Posted Saturday, February 5, 2011 7:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 2:27 PM
Points: 6, Visits: 115
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
Post #1059189
Posted Saturday, February 5, 2011 9:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 20, 2014 10:10 AM
Points: 5, Visits: 31
Sounds good.
Post #1059195
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse