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

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

Oracle clustered table to MS SQL Server 05 SSIS

Posted By:      Posted Date: September 15, 2010    Points: 0   Category :Sql Server
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

View Complete Post

More Related Resource Links

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

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

How to deploy and configure ssis packages using sql server table in testing server and production en

Hi We have three environments (development, testing and production ) I have to configure and deploy the ssis packages using sqlserver table in different environments(development, testing and production).   Three environments database structures  are same But database names are different like (DEVSERVER,SITSERVER,UATSERVER) We have 14 packages and one master package. In master package we called all 14 packages question1 First time i will deploy all (master and 14 packages). If i modify one package we need to deploy all the packages or single package deployment is enough    Please help me thanks in advance          

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




Clustered SQL 2008 R2 x64, Oracle linked server works on one node, fails on the other


Juicy bits:

- SQL Server 2008 R2 x64

- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) 

- Active/passive cluster

- Oracle client 11gR1

- "Allow Inprocess" provider option is enabled


I'm stumped.  Our development, QA and UAT environments are all running the same versions of Windows, SQL, and Oracle client.  Linked server between SQL and Oracle works just fine.


Production is clustered.  The linked server works just fine on one node of the cluster, but if we failover to the other node, attempts to use the linked server in any way, OPENQUERY, GUI, etc..., cause the following error:

OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE" returned message "ORA-12634: Memory allocation failed".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE".


What am I missing?  I've reinstalled the Oracle client on the defective node, verified that the SQL Server service account has full rights to the Oracle client dir

Creating a SSIS package to get data from free DBF (Visual FoxPro table) in 64 bit SQL Server


Hi everybody,

I originally posted in the wrong forum MSDN Thread

I'm trying to create (for test purposes) a package to load data from VFP table into SQL Server 64 bit. So far I can not figure out how to do it - I created DSN as outlined in Todd's blog, but I don't know what to do next.

Can someone please give me step by step instructions so I will know if it's possible or not? I was under impression it's not possible, but Todd's blog seems to suggest otherwise.

Thanks in advance.

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Can't update table oracle using a liked server on sql server


I have a stored procedure in SQL2005 that updates data base oracle's tables by a linked server. When run into a transaction I get the following error message:

Msg 7391, Level 16, State 2, Procedure CSGICONTABILINS, Line 69

The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "LS_JBSQLT01_SGI_GLHI" was unable to begin a distributed transaction.


Can't update table oracle using a liked server on sql server


I have a stored procedure in SQL2005 that updates data base oracle's tables by a linked server. When run into a transaction I get the following error message:

Msg 7391, Level 16, State 2, Procedure CSGICONTABILINS, Line 69

The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "LS_JBSQLT01_SGI_GLHI" was unable to begin a distributed transaction.


SSIS Package retains designer setting, not Using Configuration From SQL Server Table.


Hello all,

I am using package configurations to set SSIS package variables from the SQL server Database [SSIS Configurations] table.   I have an environment variable set as my first configuration, to hold my database connection, and then the subsequent configurations are SQL configuration entries and held in the [SSIS Configurations] table.

When testing in BIDs, the connection is OK and the designer shows the correct values have been picked up.  All good!

But, when we change the database value in the [SSIS Configurations] table, the old configuration is still being used in BIDs.

I have checked using Profiler that BIDS is calling and retrieving the correct value from the table but the modified value is not being used in BIDs, instead it is retaining the old value when the package was designed.

How do I force the SSIS package to use the configuration specified in the SQL table?

Thanks, Helen


How to Use SQL Server to Link to a Table in Oracle?


How to Use SQL Server to Link to a Table in Oracle?

I have an Access DB that links to Oracle in pulls in the data I need.  I know Access quite well.  I am now trying to learn how to use SQL Server.  As a self-motivated training exercise, I am trying to get SQL Server to do exactly the same thing that my Access DB does.  I googled around for a solution on the web, and amazingly I didn’t find anything useful.  I thought there would be dozens of tutorials out there about this knid of thing.  Can someone please send me a link to a nice step-by-step tutorial for using SQL Server to link to Oracle?  Or, can someone give me step-by-step instructions about what to do?

I am using SQL Server Management Studio Express.

Thanks so much!!!

copy data (table) SQL server to Oracle


I want to copy or transfer data SQL server to Oracle 10 G. any body can help me.


Temporary tables in SQL Server vs. table variables

When writing T-SQL code, you often need a table in which to store data temporarily when it comes time to execute that code. You have four table options: normal tables, local temporary tables, global temporary tables and table variables. I'll discuss the differences between using temporary tables in SQL Server versus table variables.

Bulk Copy From Datatable To SQL Server Table


 I have read the data of a excle file and captured the data into the dataset to a datatable, now that data is to be inserted into a SQL Server table using bulk copy option. I am using -

 public bool BulkEnterData(DataTable dt, string tblName)
        SqlBulkCopy bulk = new SqlBulkCopy(con);
        bulk.DestinationTableName = tblName;

        return true;

ERROR Getting-
A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.) 

How to resolve the above problem or any other solution is available.


Under the Table: Programming with FileStreams in SQL Server 2008


There's always been disagreement about whether large blobs, such as document and multimedia items, should be stored in the database or file system. In SQL Server 2008 you don't have to choose; filestream storage provides the best of both approaches.

Bob Beauchemin

MSDN Magazine May 2009

Under The Table: Spatial Data Support In SQL Server 2008


New spatial data support in SQL Server 2008 opens the door to mapping and querying geometric and geographic data, allowing you to build exciting new applications.

Bob Beauchemin

MSDN Magazine February 2009

generate create script of table using c# and SQL server 7.0, can anyone help me?


I want to generate create table script using c#.net, I want to connect sql server 7.0 and generate table create script. Its urgent kindly help me urgently.

Linq with two databases (oracle and sql server)


i have a problem to bring the data from the oracle (external database) and the sql server together (with similar data). The linq entities are linked to each other. The structure is like different countries with different cities and so on. To display these "non original linq objects" i need to put them into entities (linked to each other). Now i add some of them to my sql database. But for every entity i must check the connection to the other objects i don't want to add and delete them (otherwise linq will add all the connected objects, even those I don't want to add). If this goes wrong the whole input-method crashes. Is there any other possibility, except from checking every single entity and removing unwanted connections, to handle this procedure?
Thanks a lot, preg

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