Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
delta1186
Starting Member
10 Posts |
 Posted - 2009-10-13 : 18:42:18
|
| I have a couple of questions. First, is there an easy way to import your entire Pervasive database? Or, do you have to creat a seperate data flow object for each table?Currently I am hung up on importing just one table. I am using the ADO.Net connection to connect through ODBC to the DSN I created to the SQL Server to my Timberline Database. I have created a new SSIS package in BIDS. I have a DataReader source and an a SQL Destination. I am creating a new table through the DataReader source that is connected to my Timberline database. The destination table gets created without an issue, but the data will not import. It fails when importing a blank field from Timberline to SQL. How do I get around this? FYI, Keep identity and keep nulls are checked for the destination...tried both ways without success.[drTimberline [1]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "drTimberline" (1)" failed because error code 0x80131937 occurred, and the error row disposition on "output column "Address_2" (19314)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. I know it has something to do with the blank field because I tested just importing two columns that did not contain any empty fields and it imported the records fine.Also when I get this finally setup, what is usually the best practice for integrating data like this. If I run the package twice it is going to duplicate all of the data. Should I design the SSIS package to delete the tables first and run or is it possible to just update/delete/insert changes? Which way is faster/easier/etc? The data would also be following just one way. |
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2009-10-14 : 07:53:19
|
quote:
First, is there an easy way to import your entire Pervasive database? Or, do you have to creat a seperate data flow object for each table?
Use the SQL Server Import/Export Wizard to import multiple tables. It's in the SSMS. Right-click the database and select Tasks --> Import Data...You can then save it as a package. Try it with a couple of tables and see if it helps you. |
 |
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2009-10-14 : 08:02:43
|
| Regarding the issue with loading the data, check the new table definition in SQL Server:- Does that column allow nulls?- Is the IDENTITY column defined correctly and are the source values valid identity values?- What datatype are these columns?I have successfully imported data from Pervasive in the past, by using the Pervasive OLEDB provider. |
|
|
|
delta1186
Starting Member
10 Posts |
Posted - 2009-10-14 : 09:49:28
|
quote:
Originally posted by YellowBug
Regarding the issue with loading the data, check the new table definition in SQL Server:- Does that column allow nulls?- Is the IDENTITY column defined correctly and are the source values valid identity values?- What datatype are these columns?I have successfully imported data from Pervasive in the past, by using the Pervasive OLEDB provider.
Yes all of the SQL fields are set to accept nulls. Currently there is not an identity column, but if I were to add one it would be the first column which does not contain dups or nulls. The text datatypes are nvarchar. I am guessing that is because the Pervasive data is unicode.I have not been able to get the OLE DB provider to connect. I put the unc for the data source, nothing for location, and username and password. It keeps prompting me for the database name which to be honest I am not sure where that is listed in Pervasive. |
|
|
|
delta1186
Starting Member
10 Posts |
Posted - 2009-10-14 : 09:56:43
|
quote:
Originally posted by YellowBugUse the SQL Server Import/Export Wizard to import multiple tables. It's in the SSMS. Right-click the database and select Tasks --> Import Data...You can then save it as a package. Try it with a couple of tables and see if it helps you.
I tried that first. I had a hard time getting connected to the db when using the wizard. I was going to try it this morning, but I cannot get the .net provider for odbc to connect and it will not accept a custom connection string. Just asks for DSN and Source Driver which I put but it fails because the usid and pwd are not provided. However the wizard will not let me put that part of the string in manually. I would use the OLE DB but I cannot figure out how to configure the connection as per the post above. If I ever get this working I am wonder which provider will be faster, because I will be copying most of the database once a day into SQL server. |
|
|
|
|
|
|
|
|
Importing Timberline (Pervasive) to SQL