.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SSIS w/Oracle intermittent ORA-03135

Posted By:      Posted Date: September 23, 2010    Points: 0   Category :Sql Server

I'm somewhat new to SSIS and very new to Oracle so any pointers would be helpful.

We're running SSIS 2008 with Oracle 10g source and destinations (using the Attunity connectors).  The SSIS packages run correctly when executed manually and *usually* run on a scheduled basis correctly as well but every several days one of the packages never completes and instead errors out with ORA-03135 (connection lost).

When the package runs correctly it takes about 15 minutes, and on nights it fails it shows as having a duration of over 6 hours culminating in the connection error above.

I haven't seen anything unusual in the alert logs on the Oracle server but I'm not sure if I have all the necessary tracing on - any debugging tips or insight would be greatly appreciated.

View Complete Post

More Related Resource Links

Timeout When Connecting From SSIS to Oracle

Hello Forum I've been tasked with building a replacement SQL Server that extracts data from Oracle and publishes it to a Third Party.  The new Server is of a higher spec than the existing SQL Server and the Server in question is not performing any other role. The Network config between the two SQL Servers is identical; Firewalls are also configured correctly.  Both Servers are using the same Switch. However when I attempt to replicate the DTS Packages and SSIS, i'm getting a Timeout problem when Management Studio is attempting to populate the list of Objects from Oracle.  Is there anything from a SQL Perspective that could be causing the issues? New Server is SQL Server 2005 Standard Edition SP3.  Old Server is SQL Server 2000 Standard Edition SP4.Tony C

SSIS from MS SQL Server 05 to Oracle 10g 250 Tables

Hi Guys, I want to write a neat SSIS to automate the monthly load of 250 MS Sql Server Tables to the Oracle 10g database. Now, I know how it works but I dont want to create 250 data export/import steps. Is there a that the SSIS captures the schema, does the data type conversion and dumping automatically? I know Oracle SQL Developer does this whole "schema capturing" but it lacks automation. Thanks, metalray

Oracle clustered table to MS SQL Server 05 SSIS

Hi Guys, Can the SSIS deal with clustered Oracle tables? Moreoever, what are clustered tables compared to partitioned tables? I cant find much about it. (only sytax how to create them). Thanks, metalray

Adding Oracle table into SSIS project --Oracle error occurred, but error message could not be retrie

Hi all I am new to design SSIS packages,to day I stated creating package SSIS package and adding the Tables,while adding tables,one of the table giving an below error . I verified the table structure it has one "CLOB" datatype. can some body help how to add this table( whole idea of the package is I have Dump those tables with structure and Data to SQL Server 2008 Database)  =================================== Oracle error occurred, but error message could not be retrieved from Oracle. Data type is not supported. (Microsoft Visual Studio) ------------------------------ Program Location: at System.Data.OleDb.OleDbCommand.ProcessResults(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.PrepareCommandText(Int32 expectedExecutionCount) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.DataWarehouse.Design.DataSourceConnection.FillDataSet(DataSet dataSet, String schemaName, String tableName, String tableType) at Microsoft.AnalysisServices.Design.DataSourceDesigner.AddRemoveObjectsFromDSV() Thanks in Advance.   SNIVAS

SSIS Package for Delta Data between Oracle and SQL Server


Hi all

I have BI Server and Tables and Data Populated using SSIS package.Now Data is ready in BI server.Next week Ihave to same excersize to filll the Data(DROP tables,CREATE table and Pump the Data)

Can any body give an idea how to create SSIS package which can pupm the Delta Data Oracle and SQL Server.


Thanks in advance




Problem using SSIS to move DB2 data to Oracle



I will need your advice on a SSIS package which moves IBM DB2 (for z/OS V7) data to Oracle (AIX 10g). Thank you in advance.

Basically, this is a very simple SSIS package (created by SSIS import / export wizard) and just maps column by column. I used IBM OLEDB provider for DB2 to connect the source and Oracle Provider for OLEDB to connect to the destination.

The problem I ran into was that if the DB2 source value contains special (or invisible) characters then this value will be converted to a SPACE (' ') value on Oracle. To be more detailed, take the following example:

On DB2, value  000002D0000002D7 (EBCDIC HEX codes). This is actually a value of a column defined on DB2 as CHAR(8). Please note HEX '00' in EBCDIC stands for NULL and HEX 'D0' stands for character 'K';

On Oracle, this value will be stored as a SPACE (or a string of SPACEs) after data move.

On both DB2 and Oracle this column was defined as CHAR(8).

I guessed this was related to a code page issue, so I tried a few different code page to set for the source property (by setting the DefaultCodePage of the DataFlow source property), I used code page 37, 500, etc... All resulted in the same Oracle value.

The character set on DB2 is EBCDIC and the one on Oracle is ALT32UTF8. I guess it was the source provid

Calling Oracle procedure from SSIS 'Execute SQl task' is not working


Hi ,


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




SSIS 2005 to Oracle


We have a SSIS 2005 environment where we need to connect to an Oracle database. Should it be enough to install the MS OLE DB Provider for Oracle or do you need to install anything else such as Oracle Client Tools?

Error on getting return value from Oracle procedure in SSIS package

In my case, I want to execute a stored procedure in Oracle database using SSIS package and if there has error in Oracle, I want to get the error message from Oracle and pass it to SSIS to log the error.

I can execute the stored procedure through SSIS successfully, but the problem is I cannot get the error message using OUT parameter of procedure in Oracle, the following part is what I have done to test whether SSIS can fulfill my requirement:

1. Oracle client installed, use Microsoft OLE DB Provider for Oracle, Test Connection to Oracle succeeded.

2. The stored procedure created in Oracle database:
    create or replace p1(in1 varchar2, out1 OUT varchar2) as begin
        out1 := 'nice';

3. In the SSIS package, I add a "Execute SQL Task" component:

Variable defined = User::output (String datatype)
ResultSet = Single Row
SQLSourceType = Direct Input
SQLStatement =

        in1 varchar2(10);
        out1 varchar2(10);
        in1 := 'wow';
        out1 := '1a2';

Unable to connect oracle from SSIS management studio


the connection failed because of an error in initiallinzing provider. ora-01017: invalid username/password; logon denied.


but can connect from SQL PLUS.

How to call Oracle Stored Procedure which has an output parameter from SSIS?


I 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);  


datatype tranformation issue in oracle to sql 2008 migration using SSIS


Hello friends

I am doing oracle to SQL2008 R2 migration and i want to use SSIS.

Oracle has mainly three datatypes DATE, VARCHAR2 and number. 

I tried without using the transformation it worked but the datatypes like date did not changed to datetime in sql server, even the number datatype remain same.

secondly while doing very first time i got very weired name of the table on sql side. 

Instead of going to dbo schema it shows the name like tom\myname.A_RETURNS.

So how can i resolve both the datatype and naming issue?

Thanks in advance..


Error while oracle to sql migration with SSIS


Hello frnds 

i am getting these errors in the SSIS package which is importing  data from oracle to SQL SERVER.

1)[OLE DB Destination [22]] Error: 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 SQL Server"  Hresult: 0x80004005  Description: "Invalid date format".

2)[OLE DB Destination [22]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "OLE DB Destination Input" (35)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (35)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

3)[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "OLE DB Destination" (22) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (35). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. &n

date format conversion from oracle to sql using SSIS


HEllo friends

I am migration oracle tables to sql using SSIS. While doing this i am trying to use Derived column transfrom to convert Date format in oracle to sql. But i do not have any clue that how should i use this. In oracle side date is in mm/dd/yy format and in sql i want that in yy/mm/dd format. 

Please give me directions ASAP?

Thanks in advance


SSIS connect to oracle database


In datasource designer for adding a new data source, I am attempting to connect to an oracle server. I then enter credentials and click 'test connection', and get this error.

Test connection failed because of an error in initializing provider. ORA-12154: TNS:could not resolve the connect indentifier specified.

I am using Oracle 11g as my framework.

The strange thing is if i use enterprise manager using 10.2.1 oracle framework, I cannot see any tables. The only way I can see tables if I go and click on "other  users" and then click on the specific user previous employees have used as their login credentials to generate an SSIS package.

I have already set up the access using enterprise manager, but ssis does not follow? Or something?

Thank you in advance.

Using Conditional Split data Transfer in SSIS 2008

This article uses the Integration Services Conditional Split Data Transformation element to filter and transfer data from a set of flat text files to SQL Server database table. The concept can be easily extended to apply to any other source or destination such as Microsoft Excel. This scenario is useful in creating denormalized database tables in a reporting and analysis situation.

Need Oracle Data Provider .CS File for Oracle 10g Database connection !



I need a 'Wrapper.cs' file which takes care of the Database connection ( Oracle 10g) where

i can just call the method with my SQL Query


Gridview1.DataSource = SampleWrapper.ExecuteDatatable("THE SQL QUERY");


Plz Post the link if there is any open source !    

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend