Execute Oracle Procedure From SSIS
Sep 18, 2007
is it possible to execute Oracle procedure from within SSIS?
thanks
ADVERTISEMENT
Calling Oracle Procedure From SSIS 'Execute SQl Task' Is Not Working
Nov 6, 2007Hi ,
Iam using 'Execute SQl task' which calls a stored procedure located in sql server database.The task's SQL source type is variable and the variable has the follwoing expression "EXEC PROC_SEL_MBO_REPORT "+@[User::V_SP_Job_Date]after evaluation it is like EXEC PROC_SEL_MBO_REPORT '01/NOV/2007'.It is working fine
Now the procedure is changed to Oracle.So I have changed it to "BEGIN PROC_SEL_MBO_REPORT " + "("+ @[User::V_SP_Job_Date]+")"+"; END"+";" after evaluation it is like BEGIN PROC_SEL_MBO_REPORT ('01/NOV/2007') END;.It is sucessfully executing from the task but no data is loaded into the tables which are used by the procedure internally.
Executing 'execute BEGIN PROC_SEL_MBO_REPORT ('01/NOV/2007') END;' is perfectly alright from SQl developer or sql plus.
Please help me.. thanks in advance
Regards,
GK
How To Execute An Oracle Stored Procedure From DTS
Apr 14, 2006In SQL Server 2000 DTS
How do I call and oracle stored procedure?
I've tried using the Execute SQL task with the
Exec <my procedure name> ;
and it errors.
I've been searching for the answer on how to execute Oracle Stored procedures from DTS without any luck.
The stored procedure creates the table and data that I want to pump into SQL Server.
Any help would be greatly appreciated.
Thanks!
Execute Oracle Stored Procedure From SQLDataSource
Jun 11, 2008Hi, I'm trying to do a straight forward call to an Oracle Stored Procedure from a GridView Web Control using UpdateQuery.
I created a simple procedure (then tried a package) with no parameters (then with a parameter) and tried to call it from UpdateQuery.
I either get a Internal .Net Framework Data Provider error 30 with a parameter or Encountered the symbol "UARF_FUNCTIONAL_UPDATE" when expecting one of the following: := . ( @ % ; without using a paremeter.
My updatequery on the sqldatasource control is: exec uarf_functional_update;
I'm sure I'm missing something very simple (syntax or something), but I can't find the right direction in my help material. Any assistance would be greatly appreciated.
Thanks,
E.
How Do You Execute An Oracle Stored Procedure From A SQL Server DTS Package?
Jul 23, 2005I've tried several different way to execute a oracle storedprocedure from a DTS package but to no avail.I have a Linked Server setup which does bring back Oracle tables from theserver when I click on the Tables icon.Here's my DTS statement:exec omsd..OMS_TECO.SP_Callback_Update_Pkg(116);omsd is the linked serveroms_teco is the owner of the oracle stored procedureSP_Callback_Update_Pkg is the oracle stored procedure(116) is the parameter passed to the oracle stored procedureI put the above exec statement in a DTS Execute SQL Task using a Connectionthat I tried using several OLE and ODBC Data Sources. I can't seem to findthe right combination.Please Help!!!!!!!!--Message posted via http://www.sqlmonster.com
View 1 Replies View RelatedProblem Executing A Oracle Procedure Using Execute Sql Task
Oct 18, 2006Hi All,
I am trying to execute a Oracle Procedure through Execute Sql Task but it keeps throwing back an error ORA:00900. Is it possible to execute the procedure from this object? Do I need to use the script task to execute this. If yes then how?
Any help would be appreciated.
Thanks in advance.
Amol
Problem While Trying To Execute Oracle Parameterized Stored Procedure
May 31, 2007Hi,
My name is Iram Levinger.
I'm trying to build ssis package that select rows from SQL Server table and
according to Conditional Split cube results I should execute oracle stored
procedure with in and out parameters.
The in parameters should come from the SQL select and the output parameters
should be inline parameter that I should declare on in the OleDB Command
selecet statement.
Here is the sql statement I wrote in the OleDB Command:
declare @out_return_status as int
declare @out_return_msg as varchar (1000)
begin
exec clickace.cns_clk_sst_iw_pkg.update_instal_warranty_dates ?,?,?,?,?,
@out_return_status,@out_return_msg
end
When I click on the Refresh button in the ssis GUI I get the following error:
An OLE DB error has occured. Error Code : 0x80040E51.
An OLE DB record is available. Source: OraOLEDB Hresult : 0x80040E51.
Description :"provider cannot provide parameter information and
SetParameterInfo has not been called."
Is someone can help with it?
Thanks
SSIS Invoke Procedure In Oracle Package
Feb 6, 2008Hello there,
I'am asking you people if it's possible to invoke procedure, functions in package in Oracle database > 9.X with SSIS package...
Thx for your incoming answers
Regards,
KiK2k1
Calling Oracle Stored Procedure In SSIS
May 30, 2008Hi,
I'm using Execute SQL Task to call an Oracle stored procedure. The following is the error that I get.
Error: 0xC002F210 at Validate and Transfer Actuals, Execute SQL Task: Executing the query "{call RS2_RealProject_ETL.TransformLoadAction}
" failed with the following error: "ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'TRANSFORMLOADACTION'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
SQL statement syntax: {call RS2_RealProject_ETL.TransformLoadAction}
Per my review of the Forums, I'm entering the SQL syntax correct that is as follow and also use parameter names in Oracle in ordinal method starting from 0.
I have three "input" and one "output" parameter for the stored procedure that they all are of type "NUMBER" in oracle and I've defined them in "Parameter Mapping" window of Excute SQL Task as "NUMERIC".
I'd appreciate your help.
Thanks,
Reza
Executing An Oracle Stored Procedure From SSIS
Feb 21, 2006Hello,
Is there an oracle provider out there that will let me invoke a parameterless stored procedure that is in a package in my Oracle source?
Better could that stored proc receive a prameter?
Still better, could I use a stored proc in a OLEDB source component and get the resutls from its only out variable (ref cursor) into my SSIS dataflow?
I haven't been able to get any of these basic functionalities working with either the Oracle OLEDB or the Microsoft OLEDB for Oracle provider...
If not, are there any plans to enahnce the MS provider to handle that?
A more tricky question :
Why does the ReportingService data processing extension for Oracle sources allow such things and not the .NET provider in SSIS?
Thanks
How Do I Execute A Stored Procedure Using SSIS
Oct 17, 2007Please anyone help me with my question
How do I execute a stored Procedure using SSIS ?
I have a stored procedure in SQL SERVER 2005 database that I need to execute using a SSIS package using Execute SQL Task from Toolbox in Visual studio 2005
Thanks,
George
? Execute SSIS Pkg From Stored Procedure
Feb 2, 2007Is there a programatic way to execute an SSIS package (passing the appropriate parameters) from within a stored procedure. I've seen some examples using xp_CmdShell, but would rather not go that route if possible
View 1 Replies View RelatedError While Calling Oracle Stored Procedure From SSIS !
Mar 19, 2008Hi pals,
I am facing problems while calling Oracle stored procedure which has no parameters.
I have used Microsoft OLE DB provider for Oracle.
I have taken one Execute SQL task and set the SQLStatement as
call procedurename
I also set the IsStoredProcedure property to True for Execute SQL task.
Is there any synatx problem?
I am getting an error saying
Error: 0xC002F210 at Call Sp, Execute SQL Task: Executing the query "(call sp_procname)" failed with the following error: "ORA-00928: missing SELECT keyword.
I removed the curly braces for the SQL statement and again ran the Package, i am getting the below error
Executing the query "call sp_procname" failed with the following error: "ORA-06576: not a valid function or procedure name
Can anyone help me out on this regard!
Thanks in advance.
SSIS Not Detecting Oracle Stored Procedure Failure
May 29, 2006Hi,
I figured out a way to execute an Oracle Stored Procedure from an Execute SQL Task by using
Declare
Begin
SomeStoredProc(?,?,?);
End;
with an OLE DB connection using the Oracle Provider for OLE DB.
The parameters are getting passed in and the procedure executes but if for some reason it fails SSIS is painting the task green and keeps processing. I'm guessing that's because the outer Declare/End statement completed sucessfully.
I couldn't get it to work as a function with a return value. :(
Is there another way to execute an Oracle stored procedure that I missed?
Can you call an Oracle stored procedure from a Script Task and then fail it on parameter value?
Thanks
John Colaizzi
How To Execute Ssis Package From Stored Procedure
Jul 22, 2005how to excute ssis package from stored procedure and get the parameters back from ssis into the stored procedure.
View 23 Replies View RelatedHow To Call Oracle Stored Procedure Which Has An Output Parameter From SSIS?
Jun 15, 2007I will really appreciate if someone can post step by step process to call an Oracle Stored Proc from SSIS. Here is the Stored Proc Spec:
PROCEDURE Interface_Begin
(p_from_dttm OUT varchar2,
p_error_code OUT number,
p_error_text OUT varchar2,
p_proc_name OUT varchar2);
Call Oracle Stored Procedure With Output Refcursor From Ssis
Nov 6, 2007
I would like to call a oracle stored procedure which looks like this
Create procedure in oracle sql plus
CREATE OR REPLACE PACKAGE GroupsPackage
AS
TYPE CURSOR_TYPE IS REF CURSOR;
PROCEDURE usp_SelectGroups (results_cursor OUT CURSOR_TYPE);
END;
/
CREATE OR REPLACE PACKAGE BODY GroupsPackage
AS
PROCEDURE usp_SelectGroups (results_cursor OUT CURSOR_TYPE)
AS
BEGIN
OPEN results_cursor FOR
SELECT GroupID, GroupName, GroupDescription, LastUpdateDate
FROM Groups
ORDER BY GroupName;
END;
END;
/
Execute procedure in oracle sql plus
set autoprint on;
var x refcursor;
groupspackage.usp_selectgroups(:x);
end;
/
This works in sql plus. But when I try to run the same (or tried everything) in the execute statement in ssis using execute sql task, I am not able to make it work. I am getting an error message. Please advice, what am I doing wrong. Or is this possible or is there any other way to get this working.
SQL 2012 :: SSIS - Execute Stored Procedure With Parameters Does Not Work
Jun 9, 2015Using the following:
SQL Server: SQL Server 2012
Visual Studio 2012
I have created an SSIS package where I have added an Execute SQL Task to run an existing stored procedure in my SQL database.
General:
Result Set: None
Connection Type: OLE DB
SourceType: Direct Input
IsQueryStoredProcedure: False (this is greyed out and cannot be changed)
Bypass Prepare: True
When I use the following execute statement where I am "Hard Coding" in the parameters, the stored procedure runs successfully and it places the data into the table per the stored procedure.
SQLStatement: dbo.sp_ml_location_load @system_cd='03', @location_type_cd=Store;
However, the @system_cd parameter can change, so I wanted to set these parameters up as variables and use the parameter mapping in the Execute SQL Task.
I have set this up as follows and it runs the package successfully but it does not put the data into the table. The only thing I can figure is either I have the variables set up incorrectly or the parameter mapping set up incorrectly.
Stored procedure variables:
ALTER PROCEDURE [dbo].[sp_ml_location_load]
(@system_cd nvarchar(10), @location_type_cd nvarchar(10))
AS
BEGIN .....................
Here is my set up, what is wrong here:
I Created these Variables:
Name Scope Data Type Value
system_cd Locations String '03'
location_type_cd Locations String Store
I added these parameter mappings in the Execute SQL Task
Variable Name Direction Data TypeParameter NameParameter Size
User::system_cd Input NVARCHAR@system_cd -1
User::location_type_cd Input NVARCHAR@location_type_cd -1
I used this SQLStatement: EXEC dbo.sp_ml_location_load ?,
It runs the package successfully but it does not put the data into the table.
Integration Services :: Oracle Procedure Is Not Returning Results When Executing From Script Task On SSIS
Jul 13, 2015I'm executing Oracle procedure, which has three OUTPUT parameters and returns results in table type variable. i should not use ODBC, MSDAORA providers to call the procedure. So I'm planning to using Oracle OLEDB provider. I'm able to execute the procedure successfully, but when i do check (while dr.Read()) its not returning any records. But I know as per stored procedure results, it should return 66 records.
Dim conn As New OleDbConnection
Dim cmd As New OleDbCommand
Dim dr As OleDbDataReader
Dim QSQL As String
[code]...
Execute A Stored Procedure Which Calls SSIS Packages Using A Proxy Account For Non Sysadmins In Sql Server 2005
Sep 18, 2007
Hi all,
I have a problem while i create a proxy account.The situation is like this...There is a user who has an login in to the server.He has a stored procedure which calls some on the SSIS packages and XP_cmdshell...so this stored procedure basically load some data in to the tables .So for the login in order to execute the stored procedure as he is not a Sys admin I have created a proxy account in my account as Iam an SA and then in the proxies and in principals I selected his login name and this way I have created a credential and a proxy account.
Now the problem is if he logins with his id and password and try to execute the stored procedure it gives an error message
Server: Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.
....so this mean the login is not able to see the proxy account.So what I did is I created a job and then in the job owner tab I have selected his login and then created a step with the type operating system (CmdExec) as I need to just execute the stored procedure and used the proxy account that I have created.
so I gave the command -- exec <stored procedure> --.
But this job fails and gives the error message as
[298] SQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. [SQLSTATE 42000]....
So now ....first My question is am I doing in a right way....if its right then why Iam not able to execute the stored procedure.
If there is any other way through which I can execute the stored procedure using a proxy account for the logins who are not sys admins....please do let me know.....
Thanks
Raja.V
SSIS Hard Time Getting Back XML Return Data From Stored Procedure Call Executed By Execute SQL Task
Aug 9, 2006I'm having a hard time to getting back an xml data back from a stored procedure executed by an Execute SQL task.
I'm passing in an XML data as a parameter and getting back resulting XML data as a parameter. The Execute SQL task is using ADO connection to do this job. The two parameters(in/out) are type of "string" and mapped as string.
When I execute the task, I get the following error message.
[Execute SQL Task] Error: Executing the query "dbo.PromissorPLEDataUpload" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 2 ("@LogXML"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I also tried mapping the parameter as XML type, but that didn't work either.
If anyone knows what's going on or how to fix this problem please let me know. All I want to do is save returning XML data in the parameter to a local package variable.
Thanks
Help! The Transaction Log Is Full Error In SSIS Execute SQL Task When I Execute A DELETE SQL Query
Dec 6, 2006Dear all:
I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :
Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".
So this confused me, any one has any experience on this?
Many thanks,
Tomorrow
SSIS Execute Package With Execute Out Of Process = True Causes ProductLevelToLow Error
Mar 6, 2008
Hi.
I have a master package, which executes child packages that are located on a SQL Server. The Child packages execute other child packages which are also located on the SQL server.
Everything works fine when I execute in process. But when I set the parameter in the mater package ExecutePackageTask to ExecuteOutOfProcess = True, I get the following error
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Row Count" (5349).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "SCR Custom Split" (6399).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "SCR Data Source" (5100).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "DST_SCR Load Data" (6149).
The child packages all run fine when executed directly, and the master package runs fine if Execute Out of Process is False.
Any help would be greatly appreciated.
Thanks
Geoff.
Using SSIS For ETL From Oracle 10g ODS Into Oracle DW And From There Into SSAS Cubes
May 2, 2007Hi,
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
First Time User. How Do I Execute SQL Like @ In Oracle
Mar 26, 2001In order to execute sql in Oracle, I use @sample.sql.
In SQL server, how can I execute it?
Reporting Services :: How To Execute Oracle 12c Procedures From SSRS
Oct 8, 2015I am trying to generate a report using SSRS ( SQL server 2012 & Visual studio 2013). when I try to execute stored procedures I am getting below error. Below are the details of enviroment.
1. SQl serverr 2012
2.Oracle 12c client
3.Oracle Server 12c
4.Visual studio 2013
[code]....
Execute SSIS Package Without SSIS Console
Jul 25, 2005Hello,
View 13 Replies View RelatedConnect To Oracle Stored Procedure From SQL Server Stored Procedure...and Vice Versa.
Sep 19, 2006I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.
How do I do that? Articles, code samples, etc???
Oracle && SSIS On 64 Bit...
Nov 9, 2007Hi All,
We have 64 bit win 2003 & sql2005 EE sp2.. I managed how to trick VS2005 with Oracle connections and have converted a lot of packages already from sql 2000 to sql 2005(using Oracle connetions as well),
now I stack with one oracle connection manager...
So just to clarify, we have SSIS on same SQL server installed, as well have this one Oracle server set as linked server(using Oracle client isntalled), I'm working with BIDS on this server as well..So,
when I created connection manager and test connection - it succeeded.. Now after creating rest in ssis and trying to debug.. I'm getting :
[Connection manager "PRD02.genericuser"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-12154: TNS:could not resolve the connect identifier specified".
What could be a reason of this error, if test connection is working fine, as well preview of data....???
Just in case I tried to create connection manager by using Oracle provider for OLE DB as well as Microsoft OLE DB Provider for Oracle..same things, connection is tested fine in both cases, preview is Okay, when start to debugging - problems...
SSIS 64bit To Oracle 10G
Mar 4, 2007Dose any one have a simple workaround to implement a connection fromSQL 2005 SSIS 64 bit to Oracle 10 G
View 3 Replies View RelatedSSIS - Connecting To Oracle
Jan 19, 2006Hi,
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
SSIS On X64 Oracle Client X64
Feb 27, 2008I have tried everything I can find online to resolve an ongoing issue with a SSIS package that pulls data from Oracle. I have other servers that can function just fine connecting to the same Oracle database. I have the 10g x64 client installed and I can TNSPing the database from the command prompt. I get varying errors depending on what I am doing. If I execute the package stored in SSIS MSDB from SSMS on my computer, it runs just fine. If I run it from the server I get:
Info: 2008-02-26 23:02:42.59
Code: 0x4004300A
Source: Data Flow Task RT_ADDRESS DTS.Pipeline
Description: Validation phase is beginning.
End Info
Progress: 2008-02-26 23:02:42.59
Source: Data Flow Task RT_ADDRESS
Validating: 0% complete
End Progress
Error: 2008-02-26 23:02:42.65
Code: 0xC0202009
Source: ROCKDBA DATA UPDATE (NEW) Connection manager "Rock"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80040154.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" H
result: 0x80040154 Description: "Class not registered".
End Error
Error: 2008-02-26 23:02:42.67
Code: 0xC020801C
Source: Data Flow Task RT_ADDRESS OLE DB Source [1]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAG
ER. The AcquireConnection method call to the connection manager "Rock" failed w
ith error code 0xC0202009. There may be error messages posted before this with
more information on why the AcquireConnection method call failed.
End Error
Error: 2008-02-26 23:02:42.67
Code: 0xC0047017
Source: Data Flow Task RT_ADDRESS DTS.Pipeline
Description: component "OLE DB Source" (1) failed validation and returned err
or code 0xC020801C.
End Error
Progress: 2008-02-26 23:02:42.67
Source: Data Flow Task RT_ADDRESS
Validating: 50% complete
End Progress
Error: 2008-02-26 23:02:42.68
Code: 0xC004700C
Source: Data Flow Task RT_ADDRESS DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2008-02-26 23:02:42.68
Code: 0xC0024107
Source: Data Flow Task RT_ADDRESS
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 11:02:35 PM
Finished: 11:02:42 PM
Elapsed: 7.156 seconds
I tried uninstalling and reinstalling Oracle on this server. I tried the 32bit Oracle Client as well as the 64bit client. I rebuilt the package to use Data Flow Tasks from scratch, and I have the same issues. If I try and build the package on the server itself in BIDS, I get TNS errors in BIDS trying to create connection managers. I am missing something, but I have no idea what it is. If I try and use a DataReader Source, I get:
System.InvalidOperationException: Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed. ---> System.BadImageFormatException: An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B)
at System.Data.Common.UnsafeNativeMethods.OCILobCopy2(IntPtr svchp, IntPtr errhp, IntPtr dst_locp, IntPtr src_locp, UInt64 amount, UInt64 dst_offset, UInt64 src_offset)
at System.Data.OracleClient.OCI.DetermineClientVersion()
--- End of inner exception stack trace ---
at System.Data.OracleClient.OCI.DetermineClientVersion()
at System.Data.OracleClient.OracleInternalConnection.OpenOnLocalTransaction(String userName, String password, String serverName, Boolean integratedSecurity, Boolean unicode, Boolean omitOracleConnectionName)
at System.Data.OracleClient.OracleInternalConnection..ctor(OracleConnectionString connectionOptions)
at System.Data.OracleClient.OracleConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OracleClient.OracleConnection.Open()
at STPSendMailData.BaseData.SetConnectionAndTransaction()
It just doesn't add up. I get this exception with the 64 bit client installed. Anyone with ideas, I am getting near to the point of migrating backwards to 32 bit SQL on this server to be rid of this problem.
Connecting To Oracle RDB From SSIS
Oct 24, 2006Hi 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