Problem In SCD Transformation Using Oracle DataBase
Sep 25, 2006
Hi,
I am using SCD Transformation my source & destination is oracle database .while doing insert or update I am getting the following error.
Does SCD support Oracle ?
[Slowly Changing Dimension [58]] Error: An OLE DB error has occurred. Error code: 0x80040E5D. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E5D Description: "Parameter name is unrecognized.".
please anyone help on this. Parameter name is unrecognized means What exactly we can do.
Thanks & Regards,
M.Jeyakumar
View 1 Replies
ADVERTISEMENT
May 28, 2007
Hi,
I am trying to use a lookup in a package and check for some conditions. On the advanced tab, I am trying to modify the condition from = to <=. But the same doesnt work when the target is on oracle, but the same works fine on SQL Server and DB2.
Any idea regarding the same?
Thanks,
Manish Singh
View 3 Replies
View Related
Dec 19, 2006
I have to come up with a SSIS package to tranform data from oracle database to our sql server database. This oracle db is managed by a third party , I have been given 10 views from oracle so I can extarct information I need. How should I design my SSIS package? Do I have to have 10 different data flows or there is an eaier way to run my 10 select statements from these views?
Is there any article that can give me some ideas on how to design SSIS packages to extract information from oracle?
Thanks a lot
View 11 Replies
View Related
Apr 30, 2008
So this has got to be considered a major, major flaw in how SSRS interacts with Oracle. I'm using the "Oracle" data provider, but I've also tried using Microsoft's OLE DB data source, and some others, and in no case does SSRS hand off to Oracle a query that does NOT have bind variables. In other words, typically query parameters get passed off to Oracle as bind variables.
The incredibly major problem that this causes is that it disallows Oracle's use of star transformation queries which is the primary method by which to get fast responses to a data warehouse/star schema, in fact a prime authority on this subject (Bert Scalzo, Oracle DBA Guide to Data Warehouse and Star Schemas, p.86 -- obvioulsy not using Oracle 7x was the first) lists it as in effect the #1 consideration.
So what gives? In effect SSRS cannot be used against large scale Oracle data warehouses? I've had success with Business Objects being able to access Oracle star transformations.
So a guess my question is how the heck can use SSRS in a big, Oracle-based data warehouse?
http://www.dba-oracle.com/oracle10g_tuning/t_star_transformations_sql.htm
For star_transformation join plans, the following parameters must also be considered: ... No BIND VARIABLE in SELECT statement
http://www.orafaq.com/usenet/comp.databases.oracle.server/2003/09/28/2305.htm
Star transformation is not supported for tables with any of the following characteristics:
* Queries that contain bind variables
View 1 Replies
View Related
Jun 5, 2006
Hi,
If you have two synchronous transformation components and the input of the second is connected to the output of the first, does the first transformation process (loop through) all rows in the buffer before outputting these rows to the second transformation? Or does the first transformation output each individual row to the second transormation as soon as it has finished processing it?
Thanks in advance,
Lawrie.
View 5 Replies
View Related
Apr 22, 2015
tell me the difference between Audit transformation and rowcount transformation.
Because audit and rowcount transformation will provide the environment variables.
Only difference i am finding is rowcount returns the count of rows its updating .
Apart from these is there any other difference?
Tell me the scenario where i need to use the audit transformation.
View 3 Replies
View Related
Nov 26, 1999
I have come across a problem having developed my DTS packages on one database (dev server) which has a different name to the database on the Test/Live servers. I had planned for this in that I was changing the server and database source before executing the package. When I tested this it turns out that the file import transformation that references the table to import into specifies the whole database.owner.table name and the database portion has not changed and therefore the package fails. I've dumped the package using the dts utility to view the vb script and have found the property DestinationObjectName (part of the custom task object for the relevant step...) which looks like the one to change but is this really what has to be done?
regards
Ken
View 2 Replies
View Related
Feb 6, 2007
Hi,
My scenario:
I have 4 different flat files types each having different no. of column, order of columns etc. I want to upload all the 4 types into the same destination table in the SQL database. Before uploading I need to apply transformation to each column in the flat files. The transformations could be like
1) Multipying the source column by 100
2) Put an if condition for 2 source columns and then select one column to be copied into the destination.
I have the flat files schema with me and also all the transformations that are required.
Question:
Can SSIS provide me with a component that can read the flat file schema and the transformations from the database and apply them to the source data and then upload it to the constant destination table? Can derived column transformation be provided with the input columns list and the transformation to be done on each dynamically?
Why I want this way?
In future there can be an addition of extra flat file formats and we want to keep the changes to the SSIS packages to he mininum. Just entereing the addiional schema and transformation details in the database should run the package on the new flat file successfully.
Thanks for your time.
Regards,
$wapnil
View 15 Replies
View Related
Jul 20, 2005
Hello,I need to copy a table from an 8i oracle database to a sqlserver 2000 database.Is it possible to use the command "COPY FROM ... TO ..." ?So, what is the correct syntax ?Thanks for your helpCyril
View 1 Replies
View Related
Oct 15, 2001
Ho do I convert Oracle 8 database into SQL server 7.0 database ? Is it possible ? Please let me know at skbhaduri@rediffmail.com
View 1 Replies
View Related
Jun 10, 2008
the following query cannot be executed using oracle database
quote:
select
participant.participant_id,
sum(case participant.participant_id when log.donor_id then 1 else 0 end) as [port_in],
sum(case participant.participant_id when log.receipient_id then 1 else 0 end) as [port_out]
from log log, (
select distinct donor.donor_id as [participant_id]
from log donor
union
select distinct receipient.receipient_id as [participant_id]
from log receipient
) participant
group by participant.participant_id
can anyone please help me?
View 6 Replies
View Related
Mar 29, 2006
hi,
this is my current set up:
sql server 2005 express edition,
sql server management studio express,
oracle tables (original source data currently linked through odbc)
adp database file (new db where i'm trying to import the above oracle tables)
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.
i have also 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 14, 2006
The last few months I learn something about SQL by learning Oracle database 10g XE?
Yesterday I downloaded a SQL server express from MS, but we I installed it, together with management studio, I got stucked, I don't know how to start, even the most simple thing, how to create tables, views, and users, and all these simple things.
Am I need more softwares for creating this things?
Any good idea to give a layman like me, something to read??
Really Thanks!!
View 1 Replies
View Related
Dec 15, 2004
I'm trying to set up an Oracle database as a linked server in SQL server 7 but having some problems.
The client software is installed and seems to be working fine (Used access to link some tables). I set the product name to Oracle 8.1 and the Datasource to the system dns name. I left provider string, location and catalog blank. Under the security tab I had it set to map to the oracle account and also tried the impersonate option, but always got the same error.
Error: 7399 OLE DB provider 'MSDASQL' reported an error. Driver's SQLSetConnectAttr failed]
ORA-12640: Authentication Adapter initialization failed]
This is my first time trying to set up and Oracle database as a linked server, any help is appreciated.
View 4 Replies
View Related
Jan 13, 2006
Hi all,
Well, I have created an applicatin in asp.net and it connect to MSSQL database, and it work fine.
There is another application which was developed in Oracle and the database is oracle 8i.
Well, wat i want is whenever a form is submitted from the asp.net
application, the data is inserted in the MSSQL server database and the
Oracle8i database also.
Can anyone help me how to proceed with it.
Thnks in advance for your help.
Regards
Vinay
View 1 Replies
View Related
Sep 17, 2001
i am migrating data and schema from oracle server to sql server., when i try to do the dts it gives me an error saying could not resolve tnsnames.ora. iam not sure what is the problem.
My oracle server host is dbserver.hari.com.edu
and its name is Prod (name of the machine)
i tried both it still gives me the same error., but when i install an oracle client on the machine and configure the same it is working great.
My question is, is it necessary to install oracle client to migrate database from oracle to sql server. If not how do i do the same...what is the process to do it. since this is a prodction machine we don't want more of applications running on the same machine.
Your help is appreciated, thanks in advance.
Kavitha
View 1 Replies
View Related
Jul 30, 2004
Anybody can give me a reference about how to move database from Oracle to Sql Server.
View 2 Replies
View Related
Feb 27, 2004
Hi there,
I have a problem of exporting the SQL Server 2000 database to Oracle 8i. Can anyone help regarding this??
Please help....
Thanking you in anticipation
RGDS
Amit
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
Apr 29, 2007
The Topics Covered In This Book Are: Understanding the OracleEnvironment; Understanding the Oracle Instance; Understanding theworking of Oracle Instance; Understanding the Oracle Database; Oracle9i Software Installation; Oracle 9i Database Design using DBCA;Enabling other computers to access Oracle Server; Oracle EnterpriseManger Oracle Backup & Recovery -Simple Technique; Oracle PerformanceTuning. Everything in this book is covered in a step by step mannerbyfirst building reader's concept using analogies, real world examplesand then taking him/her to the steps of design implementation. Thebook covers Oracle 9i Database Server. The concepts and most of thestuff discussed in this book are equally good for other Oracleversions including 10g. For advance user please check Oracle DatabaseAdministration Concepts & Implementation Made Simple, ISBN:0977073904.The link is provided below:http://www.lulu.com/takveen
View 1 Replies
View Related
Jul 20, 2005
Hi,In our environment a database view is based on columns in a table from anoracle database.This is a linked server I believe.I am told that since we use this construction no where clause is possible.So we get to many rows in our database which we have to filter later on.How can we do this differently ?? Since it takes a lot of time to go throughthis process.Bye,Arno de Jong, The Netherlands.
View 2 Replies
View Related
Jan 11, 2008
I've worked with PL/SQL in the past, but am new to SQL 2005 and SSIS packages. I'm trying to read an Oracle table and insert rows into SQL 2005.I have entered my command in the OLE DB Source editor and click preview, and everything works fine. When I try to execute the Data flow, I keep getting an OLE DB connection error.
The error message I'm receiving is:
ora-01017: invalid username/password; logon denied
I've tried editing the connection string associated to my entry for the Oracle server, but everytime I add 'pwd=xxx', SQL 2005 removes it. I've also tried to edit the connection for the database thru connection manager, but it does not want to save the password I provide.
Thanks for your help,
Curt
View 1 Replies
View Related
Apr 27, 2007
Hi,
I used OLE DB for oracle connection provider, it takes only Server name, User id, Password but along with it,
I want to give it Database name like in OLE DB for Sql Server.
Is it possible to provide it in some other way, so that i can put DataBase name directly also.
Thanks
View 12 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
Mar 6, 2007
Hi,
I have a package connecting to oracle database ,this was developed on 32 bit Windows OS now i moved the same to 64 bit OS and it fails to connect to oracle database.
I have created system DSN and tested it was successfull.
In my package i am using ado.net connection object ,I am using ODBC data provider.I select the Data source in the drop down .provide user name and password and when i hit on test connection it fails with the following message
Test connection failed because of error in initilizing the provider error[08004]
[oracle][odbc][ora]ORA 12154 : TNS could not resolve the connect identifier specified
[Error]IM006[Microsoft][ODBC driver manager] drivers SQL setconnectattr failed
Any help on this will be appreciated
Note:- even though the OS is 64 bit i am using 32 bit ODBC as BI studio can only access 32 bit odbc
View 2 Replies
View Related
Dec 26, 2003
Hi i have a sql server instance on my system and it is linking into an oracle database on another server. When i run queries against this other server...it takes forever...
However, when i use access, and link the table and run the same query against the oracle database...it runs immediatly.
I am very confused as to why there would be such a performance difference and why sql server would run so slow.
I am wondering if it has something to do with the way i configured the linked server. there are several options that I didn't know what they meant.
collation compatible (not selected)
Data access (selected)
RPC (not selected)
RPC Out (not selected)
collation name
connection timeout
query timeout
View 1 Replies
View Related
Aug 29, 2000
Hi all,
I hope someone can help me with linked servers.
In one of my applications running on a MSSQL database, I need to issue a select call to an Oracle database to pull information into my application.
To accomplish this,
1. the ORacle Admin has created a view of the Oracle database for me.
2. I have installed the Oracle client on the SQl server box.
3. A friend has told me that one way to accomplish this is create a linked server from SQL to use OLE/DB for Oracle to connect to the Oracle database
4. So I create the linked server successfully; create the server login sp_addlinkedsrvlogin successfully
5. When I go to query the database, I get the error below:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: The Oracle(tm) 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 greater) client software installation.
You will be unable to use this provider until these components have been installed.]
Anybody understand this?
Thanks in advance,
Faustina
View 4 Replies
View Related
Dec 12, 2005
I have two databases one running of MS SQL Server and other on Oracle..
My requirement is such that, I want an trigger to be written on table in MS SQL Server such that it inserts / updates values of tables in Oracle Database tooo..is there any methodology for the same..
regards.
View 5 Replies
View Related
May 29, 2015
I just wanted to know what the easiest way would be to migrate a SQL Server 2012 database to Oracle?
Apart from the tables and views, the database also uses a number of stored procedures and functions which will also need to be migrated over onto Oracle.
I guess the problem with generating scripts for the database (right clicking database > tasks > generate scripts) is that the TSQL scripts will not run in Oracle which uses PSQL? Is this correct?
I have also considered using the import/export wizard. Do I need to install a feature to use this to export to Oracle?
View 2 Replies
View Related
Jul 6, 2007
Learn Oracle Database Administration in 10 Minutes. No kidding. Checkit yourself,http://www.takveen.comOnly good analogy makes complex concepts simple!
View 2 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
Nov 28, 2005
Hi everybody,
View 13 Replies
View Related