We're having issues with Microsoft's 64 bit Oracle OLE-DB driver... when we escalated to Microsoft PSS they recommended that we go with Oracle's OLE-DB driver since Microsoft's hasn't been modified in a few years.
I've seen a lot of differing opinions on this topic (almost everyone says do not use Oracle's driver) and am wondering if there's a definitive answer here.
Larry
we recently got a scenario that we need to get the data from oracle tables which is installed on third party servers. we have sqlserver installed on ourservers. so they have created a DBLINK in oracle server to our sqlserver and published the DBLINK name.
what are the next steps that i need to follow on my sqlserver in order to access the oracle tables ?
When I try to create a model in Report Builder based on a connection to Oracle 10g database I get the following error.
ORA-02179: valid options: ISOLATION LEVEL { SERIALIZABLE | READ COMMITTED }
Is this a bug in the CTP version or am I missing something?
Hi All
My manager told me to link SQL Server database tables to access so that he can access the tables in MSAccess to do his SQL queries.
I am thinking of linking server but I am not sure about that.
Is someone can tell me what to do and explain me how.
Thanks in advance
Sincerely.
David
I have a requirement to set up an Oracle linked server, Ive never done this before and in fact never touched Oracle so I dont really know what I am doing.
View 4 Replies View RelatedAnyone have a recommendation on SSIS classes (providers and instructors)? It looks like Brian Knight's classes must be at the top of the list. His classes are always full!
Hi,
I believe this question has been covered quite a bit, but none the less I wanted to ask it as I still have no resolution at the moment. I am consuming a table from Oracle which has a data type of NUMBER. When I use a .Net Provider to consume the data, the SCALE defaults to 0 for these select columns and thus we lose any decimal point information for these columns. I have tried using both the .Net Provider and the .Net for OLEDB providers for ORACLE. Both seem to suffer from this. The SSIS server itself is a Win 03' server on x64 hardware architecture. Also, when trying to use the Oracle Provider for OLEDB it throws a design-time error stating "Error in Initializing Provider".
Thanks!
Derek
Hi,
This might seems a little 'out there', but has anyone tried doing ETL from an Oracle 10g ODS into an Oracle 10g DW, and from there into SSAS2005 cubes?
Any caveats houghtscomments on doing this?
Thanks,
JGP
Access Connection
create a new Connection Manager by right-clicking in the Connection Managers section of the design area of the screen. Select New OLE DB Connection to bring up the Configure OLE DB Connection Manager dialog box. Click New to open the Connection Manager. In the Provider drop-down list, choose the Microsoft Jet 4.0 OLE DB Provider and click OK.
Browse to the Access database file and connection set up---all good!!!
Dataflow task
Add an OLE DB Source component
Double-click the icon to open the OLE DB Source Editor. Set the OLE DB Connection Manager property to the Connection Manager that I created . Select Table from the Data Access Mode drop-down list.
I cannot see the tables set up as set up as pass-through table types to a Oracle 9i db
Any ideas please help
thanks in advance
Dave
Ok, we have built a data mart using SSIS etc...for transformations and loading.
Our biggest single problem we have currently is loading data from an Oracle server to our SQL server. Some tables from oracle run fine when retrieving the data but there is one particular table that just doesn't load fast enough (9 million records take over 12 hours). It seems that we are idling alot and its not always running.
Can anyone help with this problem?
I am getting this error when connecting to Oracle db. I tried using Microsoft OLEDB provider for Oracle it give me error and tells me the error could not be retrieved from Oracle. When I try the Native OLDDB provider for Oracle I get
Warning at {0F67F2FA-E3F8-4F44-93EC-47D513A34FD4} [Orcale Database WPHP2 [1]]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
Error at Copy DSS_SJV_Volume_History [DTS.Pipeline]: The "output column "COMP_UNIQUE_ID" (2007)" has a precision that is not valid. The precision must be between 1 and 38.
What do I do !!!!!!!!!!
Thanks
Shabnam
Im getting the following error when I try to export from Oracle 9i and import into SQL Server 2005 using Oracle and SQL OLE DB Provider's respectively.
Cannot retrieve the column code page infor from the OLE DB Provider. If the component supports the "DefaultCodePage" property, the code page from that property value will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
I know there is a way to change the property settings for the OLE DB Provider in the Data flow section of the development studio BUT ...
1) Is there a way to change this outside of the development studio?
2) I can't create the package to setup in the studio because even when I uncheck execute immediately and check save package, it still fails and never creates anything.
Any help is much appreciated
Thanks
Scott
We have a win2003 cluster with sql 2000 with 16 gig of memory. Should you leave more memory for the OS versus if this was a stand-alone server
View 3 Replies View RelatedI'm on the IS team of a medium-sized non-profit with internationalreach. We're trying to make some decisions regarding our Web serverand database server as we expand our web site to have more dynamiccontent. Currently the database server houses all data pertinent tothe organization (membership data, events, products, etc) in onedatabase (~2.2 GB) as well as the web site content in a separatedatabase (~40 MB). The web site pulls from both databases but hits thecontent database more often.In a nutshell, our database server appears to be struggling duringperformance testing of the new Web site. We are trying to determinewhether we simply need new hardware, or if there are things we can doto help MS SQL make better use of the resources we have. The hardwareis a COMPAQ ML370, 1266mhz Pentium III, 1gb RAM, RAID 5 with 3 HD(10,000rpm) and a COMPAQ Smart Array 5i SCSI controller. The OS isWindows 2000 (standard) running Microsoft SQL 2000, SP 3a. The Webserver is a 2.8ghz Pentium IV with 2.5gb RAM, RAID 5 with 3 HD (15,000rpm) running Windows 2000 standard and IIS 5.0. While stress testingour web site under a moderate load (simulating approximately 20simultaneous users), the database server processor tends to max outand stay that way for the duration of the test. Memory and disk accessappear to remain fairly stable -- there isn't a lot of paging goingon, and the disk queue doesn't escalate much if any. The Web servershows spikes in processor use, but appears to be coping well. However,under a heavy load, a sql-heavy page can take as long as 90 seconds toload! We've been assuming that the network is not the issue, as theservers are communicating over a gigibit backbone and while we'veidentified aspects of the ASP code that we can optimize, the databaseserver seems to be a large part of the problem.We've reviewed our SQL configuration settings, and they appear toalign with the best practices, which in our case are the defaultsettings for SQL 2000. We have rebuilt our indexes, and havedefragmented the hard disks on both the database and Web servers.This, along with changes to the structure of the Web pages themselves,has led to improvements, but the processor on the database serverseems to be groaning under the strain, and pages are still taking anunacceptable amount of time to load.What else should we be looking at? Are there steps we could take tominimize the load generated by client/server and Web-related traffic,or specific performance counters that would help us to identify theproblem? Do we just need to look at getting some new hardware? If newhardware is unavoidable, is there anyone running a similar environmentwho could suggest what minimum requirements we should be looking for?Any suggestions would be much appreciated!
View 5 Replies View RelatedI am trying to connect to a Oracle 9i Server to execute a sql task, but the connection when tested seems to fail with the following error from the Integration Services Project:
Test connection failed because of an error in initializing provider. ERROR [NA000][Microsoft][ODBC driver for Oracle][Oracle]ORA-12541: TNS:no listener
ERROR[IM006][Microsoft ODBC Driver Manager] Driver's SQLSetConnectAttr failed
ERROR[0100][Microsoft ODBC Driver Manager] The driver does not support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).
The connection was configured and tested from Oracle's SQLPlus session. Please note that the port that the TNS Server is listening is not the default. Is there somewhere I can specify the port in the SSIS connection manager to resolve the issue.
I have tried OLEDB connection and that does not work either.
Any inputs will be appreciated.
Thanks,
Monisha
Hi,
I have SSIS package which is created to pull data from oracle server. The package is running fine when executing from BI studio. But failing when i execute as a job. I am getting teh below error message.
Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed.". End Error Error: 2007-10-15 16:04:15.60 Code: 0xC020801C Source: Data Flow Task O... The package execution fa... The step failed.
Appreciate if somebody can provide some inputs on this .
We're planning to migrate our db to new and more disk drives, fasterRAID levels and more dedicated disk usage(e.g. placing the translog ondedicated disks). The db server runs on Win2003.Right now we're thinking about what file system to use on the newdrives. We opt for performance, but expect reliability as well.(Goeswithout saying, IMHO ;-))
Quote:
We have a custom web C#/SQL2K8R2 workflow application that I need to pull Oracle data into a varchar(max) field as an XML DOM document. I have no problem pulling the Oracle data using OLEDB, but I'm not sure how to create the XML DOM doc. Once I get it into the DOM doc, I then need to assign metadata about the XML DOM doc and insert it all into a staging table:
CREATE TABLE [stg].[EtlImports](
[EtlImportId] [int] IDENTITY(1,1) NOT NULL,
[EtlSource] [varchar](50) NOT NULL,
[EtlType] [varchar](50) NULL,
[EtlDefn] [varchar](max) NULL, --Either a SQL statement or path to file on disk
[EtlData] [varchar](max) NULL, --BLOB field to hold the XML data or FILESTREAM path to file on disk
[EtlDateLanded] [datetime] NOT NULL,
[EtlDateProcessed] [datetime] NULL,
[EtlStatus] [varchar](50) NULL,
[Comments] [varchar](4000) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
I will have a separate SSIS package to pull the [XML/File] field and process the data into the workflow tables. Is there a wasy I can use the ADO Record-set Destination task to accomplish this, or do I have to create a custom C# script to create the XML DOM Doc?
Hello All,
I have a unique problem while connecting to oracle source with a 64 bit processor. I can connect to the oracle from the command prompt in the 64 bit processor but not from SSIS.
The acutal problem is, when check the properties of the connection manager and provide a provider for oracle, and then provide username and password and click on test connection. I get the following error:
"Test Connection failed because of an error in initializing provider.ORA-06413: Connection not open"
Regards,
Raju
I am trying to copy the data from Oracle to SQL, it is taking 10 mins to load only 50K records of data. I am using only one DFT task.
In the DFT task I am using 2 tasks oracle Source and OLEDB destination .
what can I do to improve the ETL process and reduce the load time ?
Hi,
I have a table in SQL Server 2005 which has [Id] and [Name] as its columns.
I also have a Oracle database which has a similar table.
What I want to do is as follows:
In a SSIS package, I want to pick up details from SQL Server and update the Oracle table. And then should be done without using a linked server connection.
Can someone guide me as to how I can specify a update statement in the destination dataflow.
Thanks.
I am working on a project to write SSIS packages to update a SQL Server 2005 database with data from an Oracle 10g database. Unfortunately, our development ETL box is running 32 bit Windows Server 2003 while the production ETL box is running 64 bit Itanium Windows Server 2003. We have created SQL Server Agent jobs to execute all of our packages. All of our jobs run without error on the 32 bit development box, but in our preproduction tests they are failing in the 64 bit box.
In my testing, I've found that I can successfully pull character data from Oracle. For example, I can write a package that retrieves the data for the following query:
SELECT 'test' FROM dual
However, the following fails:
SELECT 1 FROM dual
I have logging turned on in my package. When the package fails, I typically see informational messages in the log up to the point of failure, but no actual error event logs are generated. The only error information I have is in the SQL Server Agent job history log, which contains the following:
Executed as user: SERVERNAMESYSTEM. The return value was unknown. The process exit code was
-2147483646. The step failed.
I have also tried using the 64 bit version of the DTS Wizard to create a sample package, and observe the same behavior. When I run the DTS Wizard, queries that return numeric data cause DTSWizard.exe to exit and produce the following message in the application event log:
Event
Type: Error
Event
Source: .NET Runtime 2.0 Error Reporting
Event
Category: None
Event
ID: 1000
Date:
2/1/2007
Time:
12:36:13 PM
User:
N/A
Computer: SERVERNAME
Description:
Faulting
application dtswizard.exe, version 9.0.2047.0, stamp 443f5e50, faulting module
oracore10.dll, version 10.2.0.1, stamp 435841b0, debug? 0, fault address
0x00000000001e4910.Has anyone else experienced similar behavior, and know of a solution aside of wrapping all retrieval of numeric data in to_chars in our Oracle queries?
As painful as it is proving out to be, I am trying to create a single package that is vendor neutral between SQL Server and Oracle.
At first, I thought that as long as I was going OLEDB in and out, and I ensured that my SQL syntax was generic enough, I'd be OK.
Well, not exactly. I am finding there is more to this. Specifically, metadata gets generated differently based on the source/targets.
For example, on an OLE DB Source task, datatypes will vary (i.e. NUMBER in Oracle vs Int in SQL). The job here is to pick the common denominator for the SSIS specific type. Time consuming, but doable, I think.
Another issue is on an OLE DB Desitnation. If you choose Table for Data Access Mode, this value gets written to the dtsx XML file. So, even when both RDBMS have the same schema (which is an obvious prereq) if choosing SQL Server "dbo.DimTable" will get written to the file and for Oracle "SCHEMA.DIMTABLE" will get written.
So, I am am wondering, what is the best way to address this?
My inital thought was using a dtsConfig file (which I am already using extensively) and set the target table names accordingly. This approach would have the added benefit of allowing for post-deployment configuration changes should a table name change, for example.
This section of the dtsx file shows the values of interest:
Code Snippet
<component id="138" name="OLE_DST_DimTable" componentClassID="{E2568105-9550-4F71-A638-B7FE42E66922}" description="OLE DB Destination" localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="4" pipelineVersion="0" contactInfo="OLE DB Destination;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;4">
<properties>
<property'>http://www.microsoft.com/sql/support;4">http://www.microsoft.com/sql/support;4">
<properties>
<property id="139" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates an infinite time-out." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</property>
<property id="140" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">"ORASCHEMA"."DIMTABLE"</property>
<!-- More Properies -->
</component>
Ideally, I'd like configuration time control over the name attribute of the component element (highlighted) so that I can set the value of the property element with the OpenRowset attribute type (also highlighted). This way, presumambly as long as datatypes were generic enough, the mapping would just work.
But, in walking through the dtsConfig tree, I don't see these elements or attributes exposed.
I would sincerely appreciate any suggestions on how to accomplish this.
Rick
We have an NT 4.0 sp4 server with Sql 6.5, sp4 and its server name needs to be changed. This server will also be upgraded to Sql 7.0 sp1 too.
Is changing the server name easier/more efficient etc in 65 or 70?
Does anyone have any recommendations and or tips to follow in avoiding pitfalls, headaches etc?
Additional info: Replication is not involved here. If we upgrade to 70 first, it will be a "One-computer upgrade" process followed. BOL for 70 doesn't mention anything about a Server Name change. BOL for 65 does have "How to change to the current server name in the 6.x master database..." I was hoping to find specific details on the 'how to...' but was surprised that 70 doesn't cover this.
I have also seen a Jan 15 '99 posting by Sharon Dooley under "Machine Rename" which raises concerns that MS missed the boat on Server Name changes.
Any help would be appreciated.
Thank you,
TW
What up-to-date books on this topic can people here recommend? Thanks.- Bob
View 1 Replies View RelatedIm trying to export data from Access into SQL Server using SQL Native Client for the import. I can run the exp/imp just fine when I select maybe 30 tables but when I select 200 the process errors out. Is there a registry setting I could set for the Access for a bigger bugger size???
Messages
Error 0xc0202009: {9E29C0D7-23D0-4BFD-8459-843D3755A05B}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)
Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)
Scott
Dear All,
Sorry for writing little lengthy question,thought can give a clear
information.
Environment:
I am looking to automate a data migration from MS Access 2003 to Sql
Server 2000, Client version I have Sql 2005, So am using Business
Intelligence development studio.
Brief:
Just to give a broader picture of the task , I am going to have a
Asp.net page in which user will provide the DB name and in a click of
a button I need to call the SSIS package and migrate all data from
Sql Server to MS Access.
Current situation:
I am totally new to SQL server and for SSIS too.For now I am able to
migrate data but partially (10%).Rest showing error of length in the
Columns.I am afraid am I using the rite componets ? .Am using "OLE DB
Source" for getting data from Sql server , then connecting it
to "Data Conversion" and from that to "OLE DB Destination".
Is this correct ? and so I need to use these 3 for ever table to be
migrated ? .Problems in this are I need to DB (MS Access) with the
table in place before execution,which is not according to requirement
too.
Kindly help me on this .
Thanks and regards
Raj
Does anyone know if I can have a MS Access project with Oracle backend ?
I know it's a breeze with MS SQL backend !
Thanks in advance !
Ivan
Hi all,
SQL Server 2005 SP2
Windows XP Pro SP2
Oracle 10g Client 10.2
Oracle Data Access Components 10.2 VB6
Oracle Rdb 7.1 (not Oracle RDBMS).
I trying to access a remote Oracle Rdb database via a linked server in SQL Server 2005 management studio but having no luck. The remote database is accessed via an Oracle alias in TNSNAMES.ORA called my_oledb (see below) and I know it works ok becuase I'm able to access the remote database via VB6 (ADODB) using a very simple app whose connection string looks like this...
con.ConnectionString = "Provider=OraOLEDB.Oracle.1;User ID=someusername;
Password=topsecret;Data Source=my_oledb"
I got this example VB appication off the net but have to admit I'm not sure of the difference between
OraOLEDB.Oracle.1 & OraOLEDB.Oracle.
The alias in TNSNAMES looks like this...
my_oledb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (Host = spike) (Port = 1527))
)
(CONNECT_DATA = (SERVICE_NAME = oci_oledb)
)
)
I can also TNSPING this alias without any probems.
My SQL server linked server is setup as follows.
exec sp_addlinkedserver
@server = 'MFP2',
@srvproduct='Oracle',
@provider = 'OraOLEDB.Oracle.1',
@datasrc = 'my_oledb'
exec sp_addlinkedsrvlogin
@rmtsrvname='MFP2',
@useself='false',
@locallogin='sa',
@rmtuser='******',
@rmtpassword='******'
Using both openquery and four part naming (?) I get the following errors
select job_title from mfp2...jobs
OLE DB provider "OraOLEDB.Oracle.1" for linked server "mfp2" returned message "ORA-01017: invalid username/password; logon denied".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle.1" for linked server "mfp2".
There are no schemas in the Oracle database so I've just used three dots between the linked server and the table name.
Again I know this syntax works becuase I have used it sucessfully with ODBC drivers.
select * from openquery(mfp2,'select * from jobs')
OLE DB provider "OraOLEDB.Oracle.1" for linked server "mfp2" returned message "ORA-01017: invalid username/password; logon denied".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle.1" for linked server "mfp2".
The passwrods are correct on the Oracle database as these are the ones used in the VB6 application.
Trying to expand the catalogue also causes management studio to hang.
Anyone spot the obvious mistake?
I did read on the net a while ago that registry entries might be rquired on the PC/server, is this still necessary?
Thanks in advance
Dave
Can I access SQL Server 2000 tables via SSIS/SQL Server 2005?
View 1 Replies View RelatedIs there any step by step help sites for setting up SQL 2005 linked (oracle 10) server?
I find MSDN articles but they referance winNT and 2000, I'm not getting very far and I'm not a DBA but need to get this working asap.
Hi--
I am running SQL Server 2005 on Win2k3:
Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 6.0.3790.1830
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790
I have the OraOLEDB.Oracle provider installed to the (C:oraclexe) directory.
I am having problems querying from linked oracle server. When i setup oracle as a linked server and purposely enter an incorrect password the query i run tells me i have an incorrect password. So it at least knows that. when i set the correct password and run a query I get this error:
(i replaced the real server name with "someServer".)
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer".
This is how I set up my Linked server:
Provider: "Oracle Provider for OLE DB"
Product Name: SomeServer
Data Source: SomeServer
Provider String: "Provider=OraOLEDB.Oracle;Data Source=SomeServer;User Id=MyLogin;Password=MyPassword"
The query I run is:
Select * from [Someserver].[schema or database]..[tbl_name]
Any help??? What am i missing?
I am trying to develop a package using SSIS to get rid of Informatica.
But I am not able to connect to the SSIS from the SQL Server Management Studio. The error message is 'Connect to SSIS Service on machine XXXXX failed: Access denied'. I am using Windows authentication and I am in the DTSADMIN role. I have 'dbo' privileges in all the databases in the Server where the SSIS Service is running (Yes, I checked and the SSIS Service is running). The SSIS is installed in the same Server as my SQL Server 2005 Standard Edition is running.
I can see that the same issue is posted by many people and no answer is posted againt it. It will be greatly appreciated by many beginners to SSIS if the answer to this question is available generally.
I have another question too. How do I transfer data between two databases in two SQL Server 2000 machines using SSIS? I really want to use something like the BCP OUT and BCP IN with memory pipes to avoid the intermediate data landing. How do I achieve it? I do not want to use the linkedserver here because of the excessive logging for the huge data volume I have.
Any answer to the above questions are greatly appreciated.
Thanks in advance...
Ganga Palakkattil
gpalakattil@telephia.com