Connecting To Oracle Datasource For Import
Nov 29, 2006
Now this is the first time i have tried this with SQL 05 I am trying to Import data from an Oracle DB. I have the connection info:
Xtx20xxx = (DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST =urlpart1.part2.part3.com)(PORT = 1521)))(CONNECT_DATA =(SID = Xtx20xxx ))
)
I used the Microsoft OLEDB provider for oracle from the dropdown,
The username and password I have also. I tried to enter in the Xtx20xxx as the server name, clicked "Properties"
put in the username and password I can given and clicked "Test Connection". No matter what I get the infamous ORA-12154: TNS:could not resolve service name error message. I am sure I am missing something obvious here but I have absolutely no clue and all the googling in the world has not given me any help. I do have the Oracle 10g client tools installed on this workstation (running Windows XP Prof SP2 and SQL 2005 STD).
View 5 Replies
ADVERTISEMENT
Sep 12, 2007
I have an issue using parameterised reports connecting to Oracle using "ODBC" and "Microsoft OLE DB Provider for Oracle" using parameteried reports. The following error is generated "ORA-01008 not all variables bound (Microsoft OLE DB Provider for Oracle)" and a similiar one for ODBC. It works fine for simple reports. Do these 2 drivers have issues passing parameters for a remote Oracle query?
Thanks.
View 4 Replies
View Related
Dec 15, 2007
I am new to SQL Reporting services and learning from website tutorials. I could easily connect to Adventure Works database and could create some reports. Now I want to create one report from Oracle database in my network on one of my server (Network computer). I could not connect to database as do not know what to write (is path like Server name or IP address 152.168.1.25 Oracle database name) is required in Server name field? Server Type should be OLE DB or Oracle and OLEDB Provider should be Microsoft OLE DB Provider for Oracle? Then Server of filename?
Is there any tutorial or material available, which can help me in establishing this connectivity? Any help is greatly appreciated.Do I have to install any thing on my local computer for oracle ( as read in one of forum for Oracle Client or something like that)
View 11 Replies
View Related
Nov 17, 2006
Hi
I've been using the new features with ASP.Net 2 for some months now. I use the gridview control a lot with the sqldatasource. I recently started a new job and am having problems connecting a sqldatasource control to a sql server 7 database. At my old job we used sql server 2000.
I get the error: "SQL server version needs be 2000 or higher" when I try to set up a new connection.. I read some documentation that said you can use asp.net 2 datasource controls with sql server 7. Does anyone know how to get round this issue. I don't want to have to go back to 1.x methods of connecting data controls to databases using code.
Thanks
View 1 Replies
View Related
Jan 22, 2007
Everything I have read says that this is possible in Oracle 9 and greater versions, but I can't get it to work...
Here is my query:
select * from employee where employee_id in ('111','222')
This works fine in the data tab until I replace the values with a parameter. I get the pop-up to enter the parameter values but no matter how I enter them, I don't get any rows returned. I've tried entering them as '111','222' and as 111,222 and changing the parameter type from a string to an int, but nothing seems to work.
I am running this on SQL Server RS 2005 and my Oracle driver (Oracle in OraHome92) is version 9.02.
View 6 Replies
View Related
Feb 4, 2008
Hi all,
I have been reading some topics on "slow Oracle connections", but I have not found an answer for my question yet.
The problem is that I am extracting data from a Oracle database to Raw files (to import into our DWH later on).
For one table this takes more that 1 hour! I find this to be very slow, but maybe I am expecting too much speed?
Can anyone tell me how long it should take to download 10 million rows from Oracle to Raw file (no transformations)?
Some extra information:
- The table has 10 million rows and a little over 200 columns
- The datatypes in the Oracle database are varchar, number and date
- I tried to tune the buffer size in the data flow and found that I could fit about 1900 rows in a DefaultBuffersize of 100 MB
- I use the "Native OLE DBMicrosoft OLE OB Provider for Oracle" (Provider=MSDAORA.1)
Can I do anything to get a better performance or is 1 hour for a table like this the best performance we can get?
(Changing the Oracle database is unfortunately not an option...)
Thanks and regards,
Ellen
View 4 Replies
View Related
Nov 18, 2006
created an Oracle datasource (tried both ole db & oracle client) in Visual Studio 2005 report project - works fine connecting and preview of report within ide , deployed to Reporting Services web server, received the following error when attempting to run the report on web server:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'oracle'. (rsErrorOpeningConnection)
System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.
web server has oracle client 9i client installed and connecting to db ok through sql plus (this server also is loaded with VS2005 and the SqlServer2005 db)
any thoughts or direction greatly appreciated
View 4 Replies
View Related
Oct 17, 2007
Hello,
I have to develop a package to get some data from a 10g Oracle database and load it to a SQL Server 2005 database.
In my design I used Oracle Provider for OledDB as my source provider following Microsoft recommendations found here : http://msdn2.microsoft.com/en-us/library/bb332055.aspx
As I have to use a parametrized query against my Oracle database I create a variable with my query that I override at runtime with a script task to update the parameters values.
One of the return column is of numeric datatype.
To handle this known issue with Oracle sources I edited through the advanced editor my Oledb source component to manually correct the precision.
Everything works fine with one of my package.
So I repeat the whole process while designing an other one with the same purposes.
As for the previous one I first unit test every components and everything works. But when running the full package it raised an error code 0x80004005 Error while validating post executing phase of the component (sorry for the poor translation from french to english ...)
I open my dataflow where the component is and found no warnings or errors. I edited through the advanced editor my Oledb source component and found that there's an error on the precision scale of the numeric return column. The message warn me that the precision must be between 1 and 38.
I checked this column and in the "OLE DB Source output / External Columns" the precision is 0 though I previously manually corrected it.
I upadted again for a 18 precision as it is for this column in "OLE DB Source output / Output Columns".
But it never keeps my changes ......
I delete the component and recreate it to force the metadata to be updated but always the same thing, the precision scale for this column in the "OLE DB Source output / External Columns" never keeps my 18 value.
Does someone have any clues to help me ?
Thanks a lot.
Bertrand
View 4 Replies
View Related
Apr 8, 2008
Hi All,
I have created database and OLAP cube in Analysis services using SSAS.In SSAS I have used a datasource which is using SQL tables to populate OLAP cube.Now when I added some more data to my SQL tables and trying to deploy cube,the newly added is not getting populated in the cube.So i want run SSIS package which will import data from SQL tables to this OLAP cube.
Can you please help me how to write this SSIS package to import data from SQL tables to OLAP cube.(Very urgent issue)
Thanks in Advance,
Swapna
View 4 Replies
View Related
Jun 28, 2006
Hi,
I want to connect to Oracle from MS SQL Server.
Is it possible?
If yes, please guide me in this issue.
SALIH KM
View 2 Replies
View Related
Jan 28, 2005
Hi, i need to build some way of connecting Oracle to SQL Server in order to pull data off Oracle and house it temporarily on SQL 2000 for searching.
Basically we've built an intranet using ASP and SQL 2000 and which also pulls data off Oracle, however our connection to Oracle is very slow so an idea to speed it up would be to have SQL store a temporary snapshot of this data and the ASP site query off the fast connection it has to SQL rather than use our slow connection to Oracle.
This temporary snapshot of Oracle data would be stored on SQL 2000 and updated on a scheduled basis.
Any ideas how to do this, or a better way to work this?
Thing is I tried installing the Official Oracle ODBC & Client software on our SQL 2000 server before and it just took out SQL 2000 so would really like to hear other options.
Thanks in advance,
:confused:
View 2 Replies
View Related
Jan 19, 2006
Hi,
I am new to using SSIS (after using DTS on SQL Server 2000 for many years).
I am trying for the first time to connect to an Oracle 9.2 database and export data from it into SQL Server 2005.
I used the Microsoft OLE DB Oracle Provider to connect to the Oracle instance. The test connection works fine, but when I try and preview a table or copy data from Oracle I get the error....
The component reported the following errors
Cannot retrieve column code page
Has anyone got any ideas how I fix this?
Many thanks
Pete
View 11 Replies
View Related
Oct 24, 2006
Hi Gurus,
I am trying to build a Data Warehouse using SSIS in SQL Server 2005. My source data is in Oracle RDB. Now when I am trying to connect to oracle RDB using the OLE DB Source component, I am getting the following error.
Test connection failed because of an error in initializing provider. Oracle client and networking componenets were not found. These components are supplied by Oracle Corporation and part of the Oracle Version 7.3.3 or late client software installtion.
Provider is unable to funtion until these components are installed.
I did install the Oracle RDB client software .Apparently it looks like Microsoft OLE DB for Oracle supports Oracle 7.3.3 and above. At the same time I tried to use ODBC for Oracle RDB driver from ODBC data sources. But the OLE DB Source Component in SSIS can't recognize ODBC. So now I am stuck on how to load the data from Oracle RDB to SQL Server 2005 staging area.
Any thoughts on this would be really appreciated. Ideally we are looking to port the data directly from Oracle RDB to SQL Server without any intermediate flat files (flat files etc)
Thanks,
SK
View 8 Replies
View Related
Jul 7, 2006
Can anybody point me how to connect to an Oracle DB using SSIS? I use the ole DB Connection Manager and pick the Microsoft OLE DB Provider for Oracle but it doesn't seem to work. Any suggestions are greatly apprecaited.
Thanks.
Sam.
View 9 Replies
View Related
Jan 11, 2006
Hello all,
I'm using a OLE DB Connection Manager to Oracle (I've installed the client) and using Oracle Native OLE DB Provider.
The problem was the view In Oracle I'm returning a column that uses a fuction on a numeric field in the the select part of the statement, and by this, the Precition is not displayed when I'm trying to look at the data types of the fields in the view.
The Precision is shown as 0 in the external field part of the component.
I have read the previous post concerning the problem connecting to Oracle Using the Oracle provider, and solved it by using the Microsoft provider, but still I have a question about the oracle provider:
I changed the precision on the External column (from 0 to 15) and also an the Output column, but now I'm getting an error on about error output not matching:
Error 6 Validation error. Data Flow Task: OLE DB Source 1 [8970]: The output column "UPDATE_TIME_NUM" (9333) on the non-error output has no corresponding output column on the error output. Dim_registered_user.dtsx 0 0
I cannot change the error output datatype, cannot delete it or do anything at all.
Is there a way to fix the problem?
View 2 Replies
View Related
Aug 18, 2007
Hello Everyone,
I€™m getting a problem in SCD connecting to oracle DB.
I had set the OLE DB Input Output Properties Tab
And mapped the columns in Column Mappings Tab
Every thing is taken care according to Type 2 load
But every time I run the job its inserting the records rather than updating it or ignoring if not find any new or changed records from source.
Can any one help me in sorting this issue.
Please.
View 1 Replies
View Related
Dec 7, 2007
Could anyone please guide me in detail to connect Oracle Forms 9i/10g with MS SQL Server database?
To connect with Oracle Database, we need to copy and paste the tnsnames.ora file from the Oracle Database folder to the Oracle Forms folder.
But how do i connect with other databases like SQL Server, MS Access, and so on?
Thanks in advance.
View 6 Replies
View Related
Dec 7, 2007
Could anyone please guide me in detail to connect Oracle Forms 9i/10g with MS SQL Server database?
To connect with Oracle Database, we need to copy and paste the tnsnames.ora file from the Oracle Database folder to the Oracle Forms folder.
But how do i connect with other databases like SQL Server, MS Access, and so on?
Thanks in advance.
View 3 Replies
View Related
Mar 18, 2008
Help, I'm attempting to connect to an Oracle 8 database using the microsoft OLD DB for oracle driver.
It works fine connecting to a 9i database but when I attempt to connect to the 8 database I get ORA-12537 TNS Connection Closed.
I can connect to the database fine using sqlplus on the machine.
Any ideas would be appreciated.
Thanks
Stapsey
View 1 Replies
View Related
Feb 6, 2007
While upgrading several packages from DTS 2000 to SSIS I came across a couple of packages where the source data comes from an Oracle DB. Until now I have not migrated data from an Oracle source.
When I tried to create a new connection I got the error:
"System.Data.Oracle client requires Oracle client software version 8.1.7 or greater."
This is being developed on a machine that would not have any oracle drivers before now, so I THOUGHT it would be as simple as downloading the drivers, but the resolution seems to be much more complex than that. When I researched it, it appeared that I needed to download the Oracle 9i software. The installation is about 10 GB and this seemed like overkill to just be able to connect to a database.
Is this in fact the correct solution? If so, or even if not, can I get a quick run down of the steps involved, and anyone's experience (problems, etc..) on the topic?
View 5 Replies
View Related
Nov 4, 2007
Hi,
I have installed MS SQL Server 2005 and Service Pack 2 on a new Windows 32-bit environment. I also installed the 32-bit 10g client (the Administrator option). The tnsnames.ora file is configured properly and I can tnsping to the Oracle database without any issues. I have created a package in Visual Studio and it runs successfully when I execute the job manually. I saved the package to the MS SQL Server and when I log into Management Studio to create a job for this package, I receive the following error:
Message
Executed as user: xxxSYSTEM. ...rsion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:42:37 AM Error: 2007-10-10 10:42:37.28 Code: 0xC0202009 Source: x Connection manager "x.x" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "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-10 10:42:37.28 Code: 0xC020801C Source: Data Flow Task OLE DB Source [18] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method ca... The package execution fa... The step failed.
I have created the package and logged into the server with the same ID to set up the job. And I have set the ProtectionLevel property to "Don't Save Sensitive Data" since I know this has been an issue in the past when I tried automating the job.
View 2 Replies
View Related
Nov 20, 2007
Hi all,
I need to connect with the Oracle Database for the Reports application. But i am unable to do the same. I am getting errors like ORA-12514,ORA-06401 while trying it.
(1) Please tell me about any extra settings need to be done in the listener.ora,tnsnames.ora for getting SSRS connected with the Oracle DB.
(2) Tell me the format of the connection string to be used for the connection with the Oracle database.
(3) Also which datasource has to used.. Oracle / Microsft OLEDB provider for Oracle for the Same.
Please help me with these issues.
With Thanks
M.Mahendra
View 4 Replies
View Related
Jan 10, 2006
Hello everyone,
I am working on a project for a professor. The project entails
designing and creating a database. We
were given individual space on the schools database server and we were given
SSH secure shell to connect and manage our respective databases. I took it upon myself to try out SQL Server
Express. Using the management studio I
am trying to connect to my school database and can€™t seem to figure it
out. Can someone please give a noob some
simple and quick instructions on how to achieve this?
Thank you,
Your Friendly Neighborhood Programmer,
This is the SSH product in case any one was interested, ftp://ftp.ssh.com/pub/ssh/SSHSecureShellClient-3.2.9.exe
View 1 Replies
View Related
Jun 22, 2007
Are there generalized best practices with regards to which method/provider to use when accessing an Oracle database? I have used both the "Native OLE DBMicrosoft OLE DB Provider for Oracle" and the "Native OLE DBOracle Provider for OLE DB" and both seem to have their own quirks (requirement to convert to Unicode, etc) but I also have heard that I shouldn't be using an "OLE DB" source at all, but to set it up as an ADO .Net connection.
We are just beginning to implement SSIS, and are trying to establish Best Practices/Standards etc.
Are there any gotchas - performance and/or otherwise I should know about?
Thanks in advance!
View 1 Replies
View Related
Feb 29, 2008
I'm using Reporting Services 2005 and I can view the report in Visual Studio using the preview tab. After I deploy the report and datasource, when I try to view the report on the report server is says that I must have Oracle Client version 8.xx or higher.
I have the same Oracle Client Version on my local machine and the Report Server with the same DataSource name set up for Oracle on both machines.
Any ideas why this would work on my local machine but not the Report Server?
View 1 Replies
View Related
Jan 23, 2007
I am trying to pull data from an Oracle 9i database to my SQL Server 2005 sp1 database on Windows 2003 64bit Itanium server. Each time I try to use the import wizard, select the Microsoft OLE DB driver for Oracle, I get an error message telling me that the Oracle client tools are not installed. This happened after I had already installed the 9i tools, tested the connection using TNSPING and confirmed the tnsnames file was correct. So, I uninstalled the 9i tools and then installed the 10g client tools. Tested everything after the install and then tried the import again selecting the MS OLE DB driver for Oracle. I again got the error message that the Oracle tools were not installed.
has anyone ran into this problem and if so, were you able to fix it?
View 2 Replies
View Related
Nov 14, 2006
Hi,
I have my datasource in Oracle9i and destination is Sql2005. I am connecting to Oracle 9i through OLEDb provider and when I connect to my DataSource using OLE DB Data Source, I get the following error:
Warning at "guid code": Cannot retreive the column code page info from the oledb 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.
Could anyone please help me trouble shoot this problem.
Regards,
Sudhakara.T.P.
sudhakaratp@hotmail.com
View 3 Replies
View Related
Jul 20, 2005
I have created a DTS package that pulls data in from Oracle into SQLServer. When I run it directly on the server (from EnterpriseManager), it works fine. When I run it from Enterprise Manager on aclient machine that does not have the Oracle client software, it doesnot run, giving me the error: "The Oracle client and networkingcomponents were not found...". I was hoping I wouldn't need to havethe client software installed on a machine other than the server whereSql Server is running. The problem here is that there are a number ofmachines from where I would like to execute this DTS package that I donot want to install/configure the Oracle client software. I don'tquite understand why the configuration of the client is importanthere. In my mind, when I am using enterprise manager from a clientmachine, I am using it sort of like a terminal services client toconnect to the server. I guess there is a lot more happening in thebackground.Thanks for any feedback,Marcus
View 2 Replies
View Related
Sep 7, 2006
I 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
View 6 Replies
View Related
Mar 29, 2006
i am using sql server express edition.
i need to import (or make copies) on a weekly/daily basis, of several oracle tables into a new adp database.
what is the fastest option? some of these tables hold over 1m records.
so ideally i would like to schedule a refresh or update job outside busy hours.
I have heard of "linked servers" and "dts", but would like some experts advice before starting looking into this.
many thanks
luzippu
View 3 Replies
View Related
Dec 7, 2007
Hi All,
I'm trying to import from Oracle Rdb database into SQL server 2005 via ODBC without much success.
I've installed the correct Oracle Rdb driver and verified it all works ok by importing in to Access.
Using the import wizard within SQL mgt studio and the following connection strings I get a password error
Dsn=XRS;Driver={Oracle RDB Driver};sid=system
TITLE: SQL Server Import and Export Wizard
------------------------------
The operation could not be completed.
------------------------------
ADDITIONAL INFORMATION:
ERROR [HY000] [Oracle][ODBC][Rdb]%SQLSRV-F-GETACCINF, Oracle SQL/Services authorization failed
ERROR [HY000] [Oracle][ODBC][Rdb]%SQLSRV-F-GETACCINF, Oracle SQL/Services authorization failed
------------------------------
BUTTONS:
OK
------------------------------
I know the username/password combination to be correct.
Is SSIS the way to go. I've had a quick look at this and setup the ODBC connection but the column
mappings box reports the following...
error at data flow task [datareader source[1]; cannot aquire a managed connection
from the run-time connection manager.
Anyone point me towards any examples of reading in data via ODBC and storing in a SQL server database.
Should point out that the source is not Oracle RDBMS (7,8 or 9) but Oracle Rdb which is a
completely seperate product.
Thanks in advance
Dave
View 10 Replies
View Related
May 2, 2007
I am using Import and Export wizard and seeing a very weird behaviour.
I am able to choose 'Microsoft OLE DB Provider for Oralce' and provide server name, username/ password. The test connection results in a success. However when I try to move to next window/page by clicking next I get invalid oracle error ora-01017 - invalid username/ password.
what is going on here?
View 1 Replies
View Related
