Importing Pervasive SQL Into SQL2005 Via SSIS
Aug 28, 2006
In SQL 2000, I had a working DTS package that would import a Pervasive SQL database into SQL 2000 (There is a good reason, provided on request). The column type definitions came over just fine in SQL 2000 with a few minor changes.
In SQL 2005 (SSIS), i create a Data Source via the Connection Manager (Provider: .Net ProvidersOdbc Data Provider) to the Pervasive database (System DSN, <database_odbc>). I then create a Data Destination via the Connection Manager (Provider: Native OLE DBSQL Native Client) to the SQL database. Both databases reside on the same machine.
I've created a DataSource Reader and used the sql command, "select * from ARCustomer" as an example. The issue is with "data types" for the columns. They don't come close to resembling the results that i had in SQL 2000 DTS.
Is there another method or parameter setting that will preserve the "data types" for the columns being imported from the Pervasive database.
This has been a real stumbling block and any help would be truly appreciated. Thanks in advance for your assistance ... Bernie
View 5 Replies
ADVERTISEMENT
Apr 24, 2008
Hi.
There is a "text" file generated by mainframe and it has to be uploaded to SQL Server. I've reproduced the situation with smaller sample. Let the file look like following:
A17 123.17 first row
BB29 493.19 second
ZZ3 18947.1 third row is longer
And in hex format:
00: 41 31 37 20 20 20 20 20 31 32 33 2E 31 37 20 20 A17 123.17
10: 66 69 72 73 74 20 72 6F 77 0D 0A 42 42 32 39 20 first rowͺBB29
20: 20 00 20 34 39 33 2E 31 39 20 20 73 65 63 6F 6E 493.19 secon
30: 64 0D 0A 5A 5A 33 20 20 20 20 20 31 38 39 34 37 dͺZZ3 18947
40: 2E 31 20 74 68 69 72 64 20 72 6F 77 00 69 73 20 .1 third row is
50: 6C 6F 6E 67 65 72 longer
I wrote "text" in quotes because sctrictly it is not pure text file - non-text binary zeros (0x00) happen sometimes instead of spaces (0x20).
The table is:
CREATE TABLE eng (
src varchar (512)
)
When i upload this file into SQL2000 using DTS or Import wizard, the table contains:
select src, substring(src,9,8), len(src) from eng
< src ><substr> <len>
A17 123.17 first row 123.17 25
BB29 493.19 22
ZZ3 18947.1 third row 18947.1 35
As one can see, everything was imported, including binary zeros. And though SELECT * in SSMS truncates strings upon reaching 0x00's, still all information is stored in tables - SUBSTRINGs show that.
When i upload this file into SQL2005 using SSIS or Import wizard the result is following:
< src ><substr> <len>
A17 123.17 first row 123.17 25
BB29 4
ZZ3 18947.1 third row 18947.1 25
This time table is half-empty - all characters behind binary zeros in respective rows are lost.
I stumbled upon this problem while migrating my DTSes to SSIS packages. Do you think there is some workaround, or i need to turn on some checkbox or smth else could help? Please...
View 8 Replies
View Related
Nov 20, 2006
For the life of me I cannot figure out why SSIS will not convert varchar data. instead of using the table to table method, I wrote a SQL query so that I could transform the datatype ntext to varchar 512 understanding that natively MS is going towards all Unicode applications.
The source fields from Access are int, int, int and varchar(512). The same is true of the destination within SQL Server 2005. the field 'Answer' is the varchar field in question....
I get the following error
Validating (Error)
Messages
Error 0xc02020f6: Data Flow Task: Column "Answer" cannot convert between unicode and non-unicode string data types.
(SQL Server Import and Export Wizard)
Error 0xc004706b: Data Flow Task: "component "Destination - Query" (28)" failed validation and returned validation status "VS_ISBROKEN".
(SQL Server Import and Export Wizard)
Error 0xc004700c: Data Flow Task: One or more component failed validation.
(SQL Server Import and Export Wizard)
Error 0xc0024107: Data Flow Task: There were errors during task validation.
(SQL Server Import and Export Wizard)
DTS used to be a very strong tool but a simple import such as this is causing me extreme grief and wondering of SQL2005 is ready for primetime. FYI SP1 is installed. I am running this from a workstation and not on the server if that makes a difference...
Any help would be appreciated.
View 7 Replies
View Related
May 23, 2007
We just upgraded our accounting packages database from Pervasive 8.1 to V9.11. MY SSIS extract packages were working just fine. Now I am getting several erros stating that some columns cannot be found:
For example, in the [sysdtslog90] table, I have these errors:
Column "CreditLimitAmt" cannot be found at the datasource. or
Column "RsrvRptky" cannot be found at the datasource. (from a different pkg)
The table schemas did not change, the columns still exist. The SQL I use also runs just fine in the client interface.
Has anyone experienced this? The bottom line is that now the extract packages are broken.
TIA.
View 7 Replies
View Related
Sep 11, 2006
I understand that SQL 2005 now supports XML documents. I have a rather large file that is in XML format that I need to get into a table in SQL2005. I have tried the import wizard to no avail. I even tried to use DTS packages in SQL 2000 and still no luck.
Does anyone know of an article or information I can obtain on how to import data from and XML file to a SQL 2005 Table?
Here is a basic idea of the data as it is shown on the first row of the XML file.
Code:
<?xml version="1.0" encoding="UTF-8"?><SheetName Version="1.0" Date="2006-09-10">
<SheetNameLine>
<action>A</action>
<Id>1</Id>
<Code>ACCOMPS</Code>
<AddData></AddData>
<DataIssue></DataIssue>
<Type>DATA HERE</Type>
<Serial>123546789</Serial>
<Date>DATA HERE</Date>
<Updated>DATA HERE</Updated>
<Info>DATA HERE</Info>
<AColumn></AColumn>
<BColumn></BColumn>
<CColumn></CColumn>
<DColumn></DColumn>
</SheetNameLine>
This particular file is way too large to import into Excel and then into SQL, so I need to find an alternative way.
Any ideas? Help?
Thanks in advance.............
View 1 Replies
View Related
Dec 5, 2007
Hi,
I tried restoring a sql2000 database into sql2005 but the import failed. Is it possible to do a import like this or do you have to do some kind of db conversion?
View 1 Replies
View Related
Sep 20, 2006
Hi
I have to import data from a number of excel files to corresponding tables in SQL 2005. The excel files are created using excel 4.0. I have created an excel connection manager and provided it with the path of the excel sheet.Next i have added an excel source from the toolbox to the dataflow. I have set the connection manger, data access mode, and the name of the excel sheet (the wizard detects the sheet correctly) in the dialog window i get when i double click the excel source. Every thing goes fine till here. Now when i select the 'columns' in this dialog window or the preview button, i get this error
TITLE: Microsoft Visual Studio------------------------------Error at Data Flow Task [Excel Source [1]]: An OLE DB error has occurred. Error code: 0x80004005.Error at Data Flow Task [Excel Source [1]]: Opening a rowset for "test4$" failed. Check that the object exists in the database.------------------------------ADDITIONAL INFORMATION:Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)------------------------------
Any ideas about why is this happening???
Umer
View 3 Replies
View Related
Nov 6, 2007
I am using the import tool to import a small excell file into SQL.
I am getting the following error
Error 0xc00470fe: Data Flow TAsk: The product level is insufficient for componene "source - Current_customer$" (1)
The file name I am importing is Current_customer, which contains 4 fields
Id
last
first
zip
View 3 Replies
View Related
Jul 19, 2007
I am trying to import an Excel Spreadsheet into SQL2005. There is a column in the spreadsheet that has character values, and numbers. I have formatted the numbers as text on the spreadsheet. I have declared the column on the table as char/varchar/nchar, but whatever I do, the numbers don't get imported into the table, but show up as nulls. Any idea why?
Thanks
Mangala
View 1 Replies
View Related
Jul 11, 2006
W2k3 server, SQL 2005.
@@version = Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Standard Edition on Windows NT 5.2
(Build 3790: Service Pack 1)
I'm trying to set up my first SSIS package to import a flat file to a
database table. The connection manager will not allow a text
qualifier.
My data is comma delimited, with doublequotes around each field. If I
enter " as the text qualifier, then I get the error "The preview sample
contains embedded text qualifiers ("). The flat file parser does not
support embedding text qualifiers in data. Parsing columns that
contain data with text qualifiers will fail at run time." The data in
Columns view shows as just one column rather than multiple columns.
If I leave the text qualifier as None, then the data in Columns view
shows as just one column again.
I've tried with more than one datafile, including a short sample file
that I set up manually. All files get the same error.
Has anyone else seen this problem?
Thanks much for any assistance!
View 14 Replies
View Related
Jul 19, 2001
Hi,
Is it possible to run MS SQL 7 on the same server with Pervasive SQL?
View 1 Replies
View Related
Jun 22, 2007
Hi,
I have tried to install SSIS going steps msdn2 using "mssql setup" but my "ssis" option is not available for installation.
Pls, help!
View 4 Replies
View Related
Nov 5, 2007
Hi guys
I need help, I am building an Intranet for my company, and we have a system here that we use to clock in and clock out, and the data is stored in an Access database. Now the HR manager needs to run a report weekly for all the temps to see what time they clocked in and out. I have designed a web form where he puts in the date from and date to and select the employee, BUT the problem is I'm using SQL Server, I import the Access database to SQL every morning. Now I need to know How can I have Job in SQL that will import the data automatically without me having to do it manually every morning.
How do I do this using SSIS and DTS I'm using SQL Server 2005.
Thanx guys, plz help
ndindi22
View 1 Replies
View Related
Dec 17, 2007
Hi am trying to import data from a excel file into my 2005 DB using a SSIS package.
This first thing i've done is create a Excel source and then a derived column task as i need to format my date, so am using substring to format the date but the expression am using will not work am geting a error on it
the data in the excel file is like 8122007
here is my expression
substring(date,1,1) +"/"+ substring(date,2,2) +"/"+ substring(date,4,7)
Any idea i think it's something got to do with the data type
View 9 Replies
View Related
Jan 22, 2008
I'm trying to import a text file into a table. The table has a nullable bit field. The corresponding field in the file has Y/N rather than 1/0. I'm getting an error on that column "The value could not be converted because of a potential loss of data.". So I'm assuming I need to convert the Y/N to a 1/0 under the "derived columns" step. Is that correct and can someone tell me how to do that exactly?
Thanks
View 1 Replies
View Related
Mar 3, 2006
I can use MS Access to import an XML file and it builds the columns and loads the data. I am trying to use SQL Server 2005 to do the same thing and there doesn't seen to be a way to easily accomplish the same task that works so well in Access.
Has anyone tried this? If so and you were successful, what were the steps to set up the package?
Thanks.
View 6 Replies
View Related
Aug 24, 2007
Dear friends,
I am trying to import dbf files (which were located in one directory) to SQL Server 2005. All are same structure. So I have to import all files into one single table of SQL Server 2005.
ISSUE 1: dBASE is not supporting directly by 2005 .
DETAILS: 2005 is not allowing directly dBASE files to import but one way I found i.e changing extendend properties in connection manager. But this is for single file only. I have to import all files located in directory.
ISSUE2: Same destination table is not allowing by 2005
DETAILS: All dbf files are having same structure. I have to import them into single table of SQL Server 2005. But it is giving the error while I am using SSIS wizard like "same destination is not allowed and destination table must be unique"
So, plz help me to resolve this issue.
seeking for ur reply asap (very urgent need)
View 5 Replies
View Related
Aug 8, 2006
Hello,
How do I convert VBSCRIPT code in a DTS package which is used for data validation to SQL 2005 SSIS?
Thanks,
Michael
View 2 Replies
View Related
Jan 27, 2008
Hi experts.
Challenge: Datapumping. To copy daily production data from N x 100 SQL 2k servers to one central SQL2k5 server.
Sometimes copying task might demand transferring schema objects like temp tables and procedures from sql2005 to sql2000.
Since system organisation is different, what would be the best approach ?
Thanks
Grega
View 3 Replies
View Related
Jul 23, 2007
Hello,
I created ssis package to transfer tables from one DB to another.
However, I don't find the option where I can make that the transfer with the indexes and pks of the tables. in sql2000 I had this option.
appriciate your help.
Kubyustus
View 9 Replies
View Related
Feb 27, 2008
IS this procedure exactly correct?
http://support.microsoft.com/kb/913967
Line 10 does not say go to advanced but that is the only place I see Integration Services to install.
Can't make a Maint. Plan for backups because the server is missing SSIS..
Thanks.
View 1 Replies
View Related
Jul 20, 2005
I am using Maximizer Enterprise 8.0 as my companies CRM solution. Currentlywe are using the included Pervasive SQL that shipps with the product. Isthere someone out that who could tell me if there are any performancebenefits to switching the Pervasive Engine with a Microsoft SQL engine.RegardsJesus
View 5 Replies
View Related
Apr 30, 2014
I am new to SSIS. How to import the below xml in sql server using SSIS?
<?xml version="1.0" encoding="utf-8" ?>
- <Employee>
- <EmployeeData>
<EmployeeID>65938</EmployeeID>
<SID>schauhan</SID>
[Code] ....
View 2 Replies
View Related
Feb 7, 2007
Hi,
i need to import a fixed with text file into several tables in SQL 2005.
The file contains records that should go inte different tables and i would like to know the order of the design..
I also get some errors when trying to convert the text to unicode, even with the derviced column data flow task..?
flat file source -> Data conversation/Derived column -> aggregate?
I have not found the way of importing the file into several different tables in the DB.
Thanks for suggestions..
Regards,
Daniel
View 2 Replies
View Related
May 17, 2006
I can't import a package from the SSIS to the file system of my SQL Server Management Studio. Every time i've tried the following message appears. What should i do to correct this problem? Any ideas?
===================================
Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToDtsServer(Package pPackage, IDTSEvents pEvents, String sPackagePath, String sServerName)
at Microsoft.SqlServer.Dts.ObjectExplorerUI.ImportPackageAsAction.ImportPackage(ImportPackageAsForm dlg)
View 9 Replies
View Related
Aug 8, 2007
Okay... I am now about to pull my hair out: something that worked VERY EASILY in Server 2000 doesn't seem to work at all in 2005. I am trying to pump an Excel table into a 2005 database. I go into the Visual Studio Integration Services Project (this is so much easier... cynicism) and set up a project. I have my data source (Excel), I have my destination (SQL Natve Server, database). I set it up the same way that it worked (perfectly) in DTS and I run it... it grinds away and reports back that all is well... no errors. I go looking for the table... not there. I try with an SA login VS windows authentication... not there. I try with a different table... no there. I try with a different database... not there.
I am certain you can imagine the frustration... that is, if you are a user, not a programmer at Microsoft.
Okay, okay... I won't launch in to abuse here... but hey, how do I make this very complicated process now work?
Thanks...
View 3 Replies
View Related
Sep 25, 2007
I want to read data from a XML into SQL Server database tables "tour" and "stop".
There is a 1:n relation between tour and stop.
(a shortened XML sample, relation Tour : Stop = 1 : n)
<Tour>
<Mandator>mein kunde</Mandator>
<TourNoPlan>TNP_1</TourNoPlan>
<TourNo>1</TourNo>
<Stop>
<StopNo>SN_1</StopNo>
<GPSGeoDec>+8.0000,+48.0000</GPSGeoDec>
</Stop>
<Stop>
<StopNo>SN_2</StopNo>
<GPSGeoDec>+8.3000,+48.5000</GPSGeoDec>
</Stop>
</Tour>
I am able to insert elements from <Tour> into the table "tour" with the data flow in the Integration Services. But I need the values from the tag <TourNoPlan> in the rows for the table stop (it is the foreign key) in the second step. How can I get the values in the SSIS from the <Tour> in the dataflow for the different <stop>? It is a hierarchicle structure - normal for a XML. Is there a sample for reading such a XML into a Database? I have tried it with [Tour::TourNoPlan] or similar, but it was wrong. Second try was setting a UserVariable in the tour dataflow to the actual value of the TourNoPlan and using it in the data flow for the stop tags - but only a setting in a script at PostExecution was possible - to late.
I think a very simple problem and the same for each XML Import. Any ideas ???
Thanks and Best regards
Wolfram
View 1 Replies
View Related
Jun 13, 2007
Im from Russia, sorry if my english is not very good.
Here's the case:
1)-------------------------------
I made a DTS-package in sql2000 that transfers the [sql table] into [dbf file] via jet4.
First i create (in delphi) the empty dbf with the same name and columns same as in sql table.
Second, I run my DTS with variables - source and destination table names
In DTS there is source, destination and transformation . After I send the Variables(table names)
, the transformation "arrow" needs to be "refreshed" to make column names in both tables correspond each other. For that in transformation I chose ActiveXScript Mode and wright VB Script:
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
dim i
For i = 1 To DTSSource.Count
DTSDestination(i) = DTSSource(i)
next
Main = DTSTransformStat_OK
End Function
And it works
2)------------------------------
I want to do same thing in sql2005 SSIS but don't figure out how...
I managed to make a package that recieves (in variables) table names and runs correctly.
But after I change those variable names into any other it crashes -
Description: "component "OLE DB Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
Of cource this happens 'cause I didn't "refresh" the transformatoin (and maybe also source and dest), but I don't know how.
Anyone can help ?!
View 8 Replies
View Related
Dec 11, 2006
My company is trying to determine if they need to upgrade from our current Platinum accounting application that uses pervasive SQL V8 to another version of Platinum that uses SQL server 2000.
My manager wants a in-depth analysis comparing pervasive SQL V8 to SQL server 2000.
I have searched the web extensively but information is scarce.
Are there any articles or case studies that do an indepth analysis comparing both databases? Can someone please help me. It would be greatly appreciated.
View 2 Replies
View Related
Apr 10, 2008
How can i format date in pervasive sql?
The day in my table is stored as "yyyymmdd"
I want to search for records with curdate().
select Curdate() returns 'dd/mm/yyyy'
Any help will be appreciated.
Thanks in advance
View 3 Replies
View Related
Feb 25, 2008
I am trying to write a stored procedure for the first time. I am using pervasive 9.0. I am tying together a secure desktop messaging system with a web application. I will be passing the data that matches what the desktop app needs to fill in the tables.
The issue is that the desktop app uses tow tables to do messaging. One table (PDSMSGC) that stores the relevant information about the message: sender, recipient, notes, phone number, etc. The other table (MEMOS) stores the message text and references back to the "PDSMSGC" table (The memos table in the desktop app is used for more than just the messaging system.)
So both tables have unique IDs that are referenced in both tables. For example:
MEMOS.ParentID = PDSMSGC.MessageID
PDSMSGC.MemoID = MEMOS.MemoID
here are the column definitions:
My question is: Does this seem like a properly formed set of commands and can you think of a better way to do this?
Here is what I have written so far:
CREATE PROCEDURE usp_webmessgaing (in :RecipientID CHAR, in :PatientID UINTEGER, in :PatientName CHAR, in :MemoText LONGVARCHAR, in :PatientPhone DECIMAL);
BEGIN
DECLARE :NewMemoID UINTEGER;
DECLARE :NewMessageID UINTEGER;
INSERT INTO PDSMSGC (ToOper, MsgFrom, LinkID, PrimaryID, Phone, MemoID, DateTaken, TimeTaken, TakeOper, BeenRead) VALUES (:RecipientID, :PatientName, '2', :PatientID, :PatientPhone, :PatientID, CurDate(), CurTime(), 'WEB', '5');
INSERT INTO MEMOS (ParentID, FieldName, MemoText) VALUES (:PatientID, 'Xholder', :MemoText);
SELECT :NewMemoID = MemoID FROM MEMOS WHERE MEMOS.ParentID = :PatientID AND MEMOS.FieldName = 'Xholder' AND MEMOS.MemoText = :MemoText;
SELECT :NewMessageID = MessageID FROM PDSMSGC WHERE PDSMSGC.MsgFrom = :PatientName AND PDSMSGC.PrimaryID = :PatientID AND PDSMSGC.MemoID = :PatientID AND PDSMSGC.TimeTaken = CurDate() AND PDSMSGC.TakeOper = 'WEB' AND PDSMSGC.BeenRead = '5';
UPDATE MEMOS SET ParentID = :NewMessageID, FieldName = 'pmc:MemoID', MemoText = :MemoText WHERE MEMOS.MemoID = :NewMemoID;
UPDATE PDSMSGC SET ToOper = :RecipientID, MsgFrom = :PatientName, MemoID = :NewMemoID, BeenRead = '0' WHERE PDSMSGC.MessageID = :NewMessageID;
END;
View 2 Replies
View Related
Aug 23, 2006
Hi All,Thanks in advance for the help.We are considering a new Project. This project is going to be totallyweb based.Currently my company is using Pervasive SQL 2000i for there ERP needs.Will the existing Pervasive SQL 2000i itself serve the purpose ORshould I consider moving to MS SQL Server.Do assist me in terms of development also i.e. Is it easier to codewith Pervasive SQL 2000i or with MS SQL Server.RegardsSamson
View 1 Replies
View Related
Jul 20, 2005
Hiis anybody know how to lin Pervasiva/bitrive SQL server on MS SQL server ?ThanksMarcin Sobolewski
View 1 Replies
View Related
