October 8, 2009 at 4:58 pm
I am trying to use Pervasive 10 as one of the sources for a datawarehouse. I am having difficulty figuring out how to transfer data from the Pervasive 10 source tables to SQL Server 2005 using SSIS. When creating an SSIS package using the wizard, the options I see for data source provider are "Pervasive.SQL OLE DB Provider" and ".Net Framework for ODBC". There are others I have not tried because they do not seem to to have anything to do with Pervasive. When I use the Pervasive provider I am prompted for a location, datasource, id, and pw. I believe I am entering all the parameters correctly but am getting the following error after running the package:
************* ERROR ************************
- Setting Source Connection (Error)
Messages
Error 0xc0202009: Source - AP1099 [1]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.
(SQL Server Import and Export Wizard)
Error 0xc02020e8: Source - AP1099 [1]: Opening a rowset for "pfwCACOPYAP1099" failed. Check that the object exists in the database.
(SQL Server Import and Export Wizard)
Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)
************* ERROR ************************
Any help on figuring this out would be greatly appreciated.
Thank you,
Bill Handelman
847-989-1758
October 8, 2009 at 10:12 pm
I don't recall having connected SSIS to a Pervasive system before. I've put a request out to the SQL folks on Twitter to see if anyone can help you out with this.
Tim
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
October 9, 2009 at 7:39 am
I really don't know anything about using Pervasive, but maybe this willl help with the connection string settings, http://ww1.pervasive.com/library/docs/PSQL/950/prog_gde/prog_gde-04-3.html.
You might want to try a simple .NET app or a Script Component/Task to see if you can get the connection there. It might be easier to debug.
Jack Corbett
Consultant - Straight Path Solutions
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
October 9, 2009 at 11:45 am
Thank you for your helpful suggestions.
October 9, 2009 at 4:05 pm
We've been doing this for a couple years currently on v9.5
I use the OLE provider for pervasive. Setup with the location being the server name or IP and the server or file name being the server side DSN that is setup on the Pervasive Server. On the pervasive server there are 2 drivers a server and client. Use the Pervasive server and create a DSN, you will need to specify the DDF file and data file locations.
October 9, 2009 at 5:19 pm
tvanharp,
Thank you for your reply. When i try to copy a table in SQL Server using the OLE DB provider i get the error "Opening a rowset for "PFWCACOPYAPALTADD" failed. Check that the object exists in the database."
Following are the steps i took and the text of the error message. Any thoughts?
1) Selected the import data wizard in SQL Server
2) selected OLE DB provider
3) clicked properties
4) entered data source (PFWCACOPY) and location (SagePFW)
5) selected "use a specific user name and password"
6) selected "allow saving password"
7) entered the server administrator id and password
8) I clicked the dropdown for "select the initial catalog to use" and received the error "login failed. Catalog information can not be retrieved." If i do not click the dropdown but just click OK, the properties box closes.
9) on the destination page I selected SQL Native Client provider, the server, and db
10) on the next page I selected "Copy data from one or more tables or views" and clicked next
11) on the "select sorce tables and views" page i selected a single table (PFWCACOPYAPALTADD). (NOTE: the list of source tables shows the tables as ServerNameTablename. Is this the way it should be???)
12) clicked "edit mappings". on the "column mappings" window i found two date fields that did not have a type showing so changed them to "datetime" and clicked OK
13) selected to save the package to the file system, selected a file name, and clicked finish.
14) the package started running but then i received the following error:
************************************************
- Setting Source Connection (Error)
Messages
Error 0xc0202009: Source - APALTADD [1]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.
(SQL Server Import and Export Wizard)
Error 0xc02020e8: Source - APALTADD [1]: Opening a rowset for "PFWCACOPYAPALTADD" failed. Check that the object exists in the database.
(SQL Server Import and Export Wizard)
Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)
************************************************
October 9, 2009 at 6:52 pm
I have not done this using the import wizard, but I would think it's the same setup. You mentioned the data source on the OLE setup. I'm not sure what that refers to. Although you are on v10.
I'm guessing data source is the server DSN i referred to, but not sure.
Can you get on the pervasive control center and see the "database", this is basically the server side DSN, expand it and view the tables. I will admit the Pervasive setup was less than pleasant.
Another issue I ran into was the code page settings for the OLE provider. In my SSIS package OLE source components it's set to use default code page with 1252 set as the default.
don't feel I am being much help here. Need to get into the wizard and give it try myself.
tom
October 9, 2009 at 7:11 pm
Yes the data source i entered is the server dsn and the location is the servername. In the control center I am able to see the tables and they show up as just the table names (ex. APALTADD). When i select a table in the import wizard in SQL Server the tables appear to be ServerNameTableName (ex. PFWCACOPYAPALTADD). I wonder if this is part of the problem. I am not familiar with code page.
Let me know if you find anything. Thank you,
Bill
October 10, 2009 at 5:25 am
Bill,
Running through the wizard now. First off I was not able to test the connection getting a strange error about unable to un-initialize since the object is open (after some messing around I did get a successful test). Anyway clicked next and the source object list was populated like you said with datasourceTablename. Picked a table from the list and had to update the mapping of a couple columns since it could not determine the correct type. Also the destination table didn't exist so it's creating one on the fly. Clicked next on the Edit Mapping screen and now going to execute. And.... it failed. Failed on Cant Aquire connection manager from ....... Oh yeah, now I remember why I can't stand pervasive.
Now there may be some parameters the perv connection that were not set correctly, not sure. I do use the OLE DB connection in SSIS and like I said it should be the same damn thing.
Ok, here is option 2:
Go into ODBC and create a client DSN on the machine running the data transfer.
Launch the wizard and pick .Net for ODBC, specify your DSN. You will need to write a query to get your data. the wizard can create a table on the fly, that is what I did. Ran successfully and transfered the data.
Maybe this will get you by for now. I am going to look into the settings for the OLE DB connection I use in SSIS and compare the setup.
October 10, 2009 at 5:48 am
Bill,
here is a screen shot of All the properties from my pervasive OLE connection manager in SSIS. The interface is different but the properties are the same. First off I noticed that local identifier (code page) is 0 by default in the SQL wizard and I have that set to 1252. Anyway check out the properties. Also for our setup there is no pervasive login used, it's integrated security based on the server permissions.
October 10, 2009 at 6:22 am
I did get data to transfer using the OLEDB connection. It wasn't pretty but the data made it.
On the ole db connection click properties and go to the "ALL" tab then for each property double click to edit the value then click the reset button. This clears the field. I did this for all properties except the location and data source. I did enter the locale identifier as 1252 but it switched back to 0 later on its own.
When I was done all fields were blank except the 3 mentioned.
Moving on I selected sql statement instead of pick table from list. Wrote a simple select statement. and executed ino a new table. The job was successful with a few warnings. These warnings were in regards to can't get default code page blah, blah. Not sure why it didn't just use the value 1252 like I entered. Anyway I did run into the rowset error you mentioned when picking a table from the list.
The only other advice I have is to go to the pervasive site and find what ever you can on the OLE Provider.
Good Luck
tom
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy