December 21, 2009 at 5:29 pm
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...
February 4, 2011 at 4:30 pm
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
February 4, 2011 at 4:37 pm
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 😉
February 5, 2011 at 7:09 pm
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
February 5, 2011 at 9:29 pm
Sounds good. 😎
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply