DTS Oracle Connection - Importing Data

Dec 9, 2003

I have SQL Server 2000 and am using DTS to simply import some data located on a remote Oracle DB.





If I have the following information:





TNS Name:


ThisApp.world


(DESCRIPTION =


(ADDRESS_LIST =


(ADDRESS =


(COMMUNITY = tcpxxx.world)


(PROTOCOL = TCP)


(Host = SomeHost)


(Port = 1234)


)


)


(CONNECT_DATA =


(SID = App)


(GLOBAL_NAME = ThisApp.world)


)


)





And I also have the name of the Table.








Using the DTS Wizard, on the Choose a Data Source screen, I pick the following:





Data Source: Microsoft ODBC Drive for Oracle


Server:


Username:


Password:





I don't know what I should put into the other fields, given what I have above, i.e. what to put in for Server, Username, and Password! Or should I use a different procedure to import this Oracle data? I am almost positive I was not supplied the username/password. I was told I could connect given the above TNS information. What do you all recommned?





Your assistance will be greatly appreciated.

View 1 Replies


ADVERTISEMENT

Importing Data From Oracle 10g To SQL Server

Aug 19, 2007

Hi,
I created SQL Server package and schedule the job. SQL Server allows us to connected different database for e.g. for oracle using "Oracle Provide for OLD DB" to retrieve our database. A link is create between this database which move the data to SQL Server. I'm sure there is no issues in the link, as I'm using it to retrieve several oracle database which contains both Arabic and English data.

But after Importation it is showing Junk values. Please advise me what step I should take next.

Regards
Azeem

View 5 Replies View Related

Importing Data From Oracle To Sql Loosing Data After The Decimal Point

Jun 18, 2007

I have created a simple package that uses a sql command to pull data from an oracle database and inserts the data into a sql 2005 table. Some of the data fields that i am pulling from contain two digits after the decimal point, however this data is lost when it gets into sql. I have even tried putting the data into a flat file, and still the data is lost.

In the package I have a ole db source connection which is the oracle database and when i do the preview i see all the data I need. I am very confused and tried a number of things to get the data into sql, but none work. Any ideas would be very helpful.

thanks

View 6 Replies View Related

Importing Unicode Data From Oracle Database

Nov 28, 2005

Hi everybody,

View 13 Replies View Related

Importing Oracle Data In SqlServer Using Express

Nov 8, 2007

OK so there is some data in an Oracle DB that I have to summarize based on grouping info stored in a SqlServer DB. How can I import the Oracle data into a SqlServer temp table using SqlServer Express? Thanks.

View 2 Replies View Related

Oracle 9i OLE DB Data Source Connection

Sep 13, 2006

help - I€™ve been stuck on this for days.

My software vendor limits the sessions to one (1) per log-in to Oracle 9i and I€™m having tons of problems trying to extract data through an SSIS package. Everytime I run the package SSIS already logged in and the OLE DB source fails because of the sessions exceeded error.
Is there any way to limit the connection to just when I need the data to pump across on the OLE DB source?

Thank you very much for any help.

Regads,
Nestor

View 1 Replies View Related

Importing Data From Oracle 10g To Sql Server 2005 Using Linked Server

Jul 30, 2007

Hi,

I am using Windows 2003 server and Sqlserver 2005 by the use of Linked server , I made a connection to Oracle 10g after that I am importing records from Oracle to sqlserver 2005. When I made tnsnames.ora in sql machine , it worked fine but when i am using tnsnames file from oracle server then i fiired importing procedure it returns below maintain error :

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Unspecified error".

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Oracle error occurred, but error message could not be retrieved from Oracle.".

Msg 7311, Level 16, State 2, Line 1

Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS". The provider supports the interface, but returns a failure code when it is used.


Please let me know.

Thanks

View 15 Replies View Related

Importing Data In MS SQL Server 2005 Using Odbc Drivers From A Remote Connection

Nov 28, 2007



hello there! i have a problem importing data from a remote connection using Sybase ASE ODBC driver(the sybase odbc driver is the only way we can access the database from the remote connection, we've already tried this in crystal reports). in the sql server import export wizard i chose the .net framework data provider for odbc. then below the wizard requires the following info: the connection string, DSN and driver. i specified the correct driver and dsn. in the connection string i specified the user id and the server name but it still produces error. the error says:

the operation could not be completed.

additional information:
ERROR[01000][SYBASE][ODBC Sybase driver][Sybase]ct_connect(): user api layer: internal Client Library error: HAFAILOVER: Trying to connect to server.
ERROR[01000][SYBASE][ODBC Sybase driver][Sybase]ct_connect(): user api layer: internal Client Library error: HAFAILOVER: Trying to connect to server.

i need your help guys... thnx

View 2 Replies View Related

Error Importing Data From Oracle Database To An SQL Database

Oct 11, 2005

Hi!

View 36 Replies View Related

Oracle Data Source Connection Problem Using Report Manager

Jan 2, 2007

I have an issue when using Report Manager for viewing reports that use a certain Oracle data source. When viewing the reports in Report Designer the reports display without problems. But when trying to display the reports with Report Manager, after deploying the reports to the Report Server, I now get the following error message:

blablabla....ORA-12705: invalid or unknown NLS parameter value specified

For testing purposes I am running Report Designer, Report Server and Report Manager locally on a PC with Windows XP. I am using Reporting Services 2005, IIS 6, Visual Studio 2005, an Oracle 9.x client and an Oracle 9.x (not 100% sure) database that is hosted on a remote server.

Earlier I got an error message that stated "ORA-12154: TNS: could not resolve the connect identifier specified". Access rights to some Oracle folders for the Network service account did the trick here but then I got the "NLS parameter" error message. What I have tried to do is removing the NLS_LANG register variable and also modified the value of the variable so that it matches the value of the NLS_LANG variable on the server that hosts the Oracle database, but none of them have worked. Any ideas?

Maybe this is an issue that is more appropriate in an Oracle forum but what the heck..the problem could reside in Reporting Services..

Thanks, Stefan

View 1 Replies View Related

Oracle Connection Fail With Microsoft OLEDB Provider For Oracle MSDAORA.1

Feb 22, 2006

Hello,

On my dev server I have working ssis packages that use connections Microsoft OLEDB provider for Oracle MSDAORA.1 and Oracle provider for oledb and OracleClient data provider.

I use one or the other according to my needs.

In anticipation and to prepare for the build of a new production server, I have build a test server from scratch and deployed to it the entire dev.

Almost everything works except Microsoft OLEDB provider for Oracle.

ssis packages on the test machine will return an error

Error at Pull Calendar from One [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "one.oledb" failed with error code 0xC0202009.

Error at Pull Calendar from One [DTS.Pipeline]: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.

[Connection manager "one.oledb"]: 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 error occurred, but error message could not be retrieved from Oracle.".

I have used the same installers for OS, SQL and Oracle SQL*Net on both dev and test machines. The install and then the restore/deployment on Test went fine.

Does anyone could point me to the right direction to solve this issue?

Thanks,

Philippe

View 17 Replies View Related

Importing Data From Oracle 8i/9i To SQL Server 2005 Using SQL Server Import And Export Wizard (AKA DTS Wizard)

Oct 20, 2006

Hi All,

I have become frustrated and I am not finding the answers I expect.

Here's the gist, we support both Oracle and SQL for our product and we would like to migrate our Clients who are willing/requesting to go from Oracle to SQL. Seems easy enough.

So, I create a Database in SQL 2005, right click and select "Import Data", Source is Microsoft OLE DB Provider for Oracle and I setup my connection. so far so good.

I create my Destination for SQL Native Client to the Database that I plan on importing into. Still good

Next, I select "Copy data from one or more tables or views". I move on to the next screen and select all of the Objects from a Schema. These are Tables that only relate to our application or in other words, nothing Oracle System wise.

When I get to the end it progresses to about 20% and then throws this error about 300 or so times:

Could not connect source component.
Warning 0x80202066: Source - AM_ALERTS [1]: Cannot retrieve the column code page info from the OLE DB 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.

So, I'm thinking "Alright, we can search on this error and I'm sure there's an easy fix." I do some checking and indeed find out that there is a property setting called "AlwaysUseDefaultCodePage" in the OLEDB Data Source Properties. Great! I go back and look at the connection in the Import and .... there's nothing with that property!

Back to the drawing board. I Create a new SSIS package and figure out quickly that the AlwaysUseDefaultCodePage is in there. I can transfter information from the Oracle Source Table to the SQL Server 2005 Destination Table, but it appears to be a one to one thing. Programming this, if I get it to work at all, will take me about 150 hours or so.

This make perfect sense if all you are doing is copying a few columns or maybe one or two objects, but I am talking about 600 + objects with upwards of 2 million rows of data in each!!

This generates 2 questions:
1. If the Import Data Wizard cannot handle this operation on the fly, then why can't the AlwaysUseDefaultCodePage property be shown as part of the connection
2. How do I create and SSIS Package that will copy all of the data from Oracle to SQL Server? The source tables have been created and have the same Schema and Object Names as the Source. I don't want to create a Data Flow Task 600 times.

Help!!!

View 8 Replies View Related

Importing From Oracle 10g

Jan 25, 2006

I am trying to import 6M records in a table from an Oracle 10g databaseinto a SQL Server 2000 database.After importing 1.5M+ records I get the following error:Ora-01555: snapshot too old: rollback segment number 129 with name"_SYSSMU129$" too smallHow can I speed up the import process between Oracle and SQL Server?The database box I'm using has 12GB of memory and 1.5 TB of space. Itshouldn't be a hardward problem. :-)Thanks in advance.

View 2 Replies View Related

Importing From Oracle 10g

Mar 7, 2006

I am trying to import a variety of data from an Oracle 10g
database. When I import the data using the OLE DB it takes a long
time (100K records an hour).



How can I improve the throughput of my import?



Is there a better driver I could be using?



Any input will be appreciated.



db55

View 3 Replies View Related

Importing An Oracle .dmp File Into SQL

Sep 4, 2001

Hi all,
Please let me know if it is possible to import an Oracle .dmp binary file into SQL using DTS. DTS has ODBC and OLE DB drivers for Oracle but both require specifying the Oracle database name, user name and password. i.e., it looks like the only way to import the .dmp file into SQL would be to first load the .dmp file into Oracle and then tranfer it from Oracle to SQL using DTS. Is there any way to load the .dmp file directly into SQL?
The .dmp file is a binary file that has been generated from Oracle using the 'Export' utility.

Thanks in advance,
-Praveena

View 2 Replies View Related

Importing From Oracle To An Adp Database

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

Problem Importing From Oracle

Jul 31, 2007

Hello all, I hoping to get some input on how to get around this odd problem. I use the SSIS Import Wizard to create a package that imports data from an Oracle 9 database into SQL 2005. One of the 'columns' selected from Oracle is actually two floating point columns multiplied together.

ie: SELECT one * two FROM table

In the SSIS wizard the data preview for this shows up as it should, with the column data in floating point form. However after actually importing the data, all the numbers get imported as integers even though they are going into a floating point column.

Any ideas how to get around this?

Thanks

View 1 Replies View Related

Importing System Tables From Oracle

Oct 22, 2001

I am trying to import application tables from oracle but they do not show up in the list of tables. I suspect that this is because the tables are owned by System. Is there any way other than changing the owner to get these tables to appear in the import list?

Thanks,
Ken Nicholson

View 3 Replies View Related

Importing Excel File Into 2012 That Is On Oracle VM?

Jun 23, 2015

I am currently trying to migrate a excel file into SQL 2012 database on an Oracle Virtual Box VM. I am new to SQL and this type of IT in general.

View 3 Replies View Related

Data Access :: Accessing Oracle Tables From Server Via Oracle DBLINK?

May 8, 2015

we recently got a scenario that we need to get the data from oracle tables which is installed on third party servers. we have sqlserver installed on ourservers. so they have created a DBLINK in oracle server to our sqlserver and published the DBLINK name.

what are the next steps that i need to follow on my sqlserver in order to access the oracle tables ?

View 2 Replies View Related

Connection To Oracle Db's

Oct 1, 2001

I need a way to connect to an Oracle database and pull the information from that database into a Sql Server Database. An Oracle gateway is not an option.
Is there a way to set up an odbc connection to connect to the database or is there another way to connect?

Thank you,
Anita Ossowski

View 3 Replies View Related

Oracle Connection

Dec 14, 2007

I've created a SQL stored procedure and would like to connect to an Oracle database & query results within the procedure. Is this possible?

View 1 Replies View Related

Oracle Connection

May 19, 2008

I am moving dta afrom an Oracle source to SQL serevr 2005 using SSIS

Used: Provider: Microsoft OLE DDDB provider for Oracle and typed in Server and username with password.

When executed the package with defaul settimngs( Package ProtectionLevel: EncrptSensitiveWith userkey OR DONTSAVESENSITIVE, EncrptAllwithPassword) and Isolationlevel: serializable/ read comitted) the package responded fine.

When I try to do 'Package Configuration' and type in password in config file the package doesn't responds. it gives following error.



TITLE: Microsoft Visual Studio
------------------------------

Error at Package2 [Connection manager "DEV04.XYZ"]: An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Error at Data Flow Task [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "DEV04.XYZ" failed with error code 0xC0202009.



------------------------------
ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------
BUTTONS:

OK
------------------------------
I also get this error:

Executed as user: XYZUHSQLServer. ...042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:01:42 PM Error: 2008-05-19 13:01:43.22 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTSassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2008-05-19 13:01:43.96 Code: 0xC0202009 Source:ExtAWMData Connection manager "XYZ ABC" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E4D Description: "ORA-01017: invalid username/password; logon denied ". End Error Error: 2008-05-19 13:01:43.96 Code: 0xC020801C... The package execution fa... The step failed.




Please advice on
1) Selection of provider for Oracle connection
2) ProtectionLevel
3) Isolation Level
4) Package configuration - Connection Strings (MSDAORA.1)
5) Publishing the package on Server best way.

Thnak a lot for all your valuable time and efforts.

AWM

View 6 Replies View Related

64 Bit Oracle Connection

Oct 17, 2007

I've installed 32 and 64 bit oracle drivers. I was testing running the two different dtexec.exe programs the other day, logged in as the sql agent and they both worked. Running the 64 bit as a job did not work. 32 bit does not work, since the agent process is 64 bit, from what I understand.

Today I'm working on it again and trying the same steps. Running the 32 bit executable dtexec is now returning "not a valid win32 executable". I swear this worked the other day, and works on our other server.

Running the 64bit executable from the agent just hangs up on this log entry:


HRD1.Cardiff (my oracle connection)















ExternalRequest_pre: The object is ready to make the following external request: 'IDataInitialize::GetDataSource'.

Anyone know what is going on?

http://support.microsoft.com/kb/918760/

I read this article, but didn't think it applied,since I chose for protectionLevel = sql to handle permission (I think they ) -
So I created a proxy using my credentials and gave it access to cmdexec and ssis and it worked. Can anyone explain this to me??

View 11 Replies View Related

Connection To Oracle

May 10, 2007

Hi,



I have created a simple report that reads data from Oracle within Visual Studio. I have no problem with previewing the report within Visual Studio. When I deploy the report to the Report Server (same machine) and try to open the report through Report Manager, I get the following error message:



An error has occurred during report processing.

Cannot create a connection to data source 'xxxx'.

System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.



I am aware of the following issue that requires extra security to be assigned to the Oracle directories: http://support.microsoft.com/Default.aspx?kbid=870668



Even after applying the Read/Execute access to the Network Service account that is running the Reporting Service, I still get the error message.



Can anyone help me with the debugging of this issue?



Thanks,

Ben

View 6 Replies View Related

Connection To Oracle

May 9, 2006

I have created reports in SQl Server 2005 Reporting Services using an Oracle Data Sources. When I Preview the report in VS 2005, It runs fine but when I deploy it, it fails to make a coonection to the oracle data source.

Reports off the SQL Server Data Sources arerunning fine.



Can any one out there help me out on this. I will really appreaciate.



Regards,

Sam.

View 8 Replies View Related

SQL Server 6.5 To Oracle DB Connection.

Dec 19, 1999

I am having some difficulty to update a record in a table from SQL Server 6.5 to Oracle 7.3 DB.

Basically I want to write a insert trigger in Sql Server 6.5 table and I want to update the same inserting record to Unix Oracle table. I tried by writing a DLL and use that in SQL Server External Stored Procedure, but I dont know how to pass the parameter for that. Parameter here is record which I want to insert in to Oracle Table.

Is there any easy way to do that? Did any body did that before? Please help me out.

Thanks a lot

View 2 Replies View Related

Best Way For SQL Server To Oracle Connection

Feb 5, 2003

We are in the process of setting up some type of interface between 2 applications that utilize SQL Server(ours) and theirs which utilizes Oracle. These servers will actually sit in the same server room. My thought was to take the simple approach of each DB setting up views needed by the other and assigning a user and security so the other DB can look at the data. We currently do this in house. THeir solution is to use a somewhat sophisticated mesaging system which will pass XML information back and forth between the systems. I hate to take the simpleton approach but why go through all that trouble where a simple view not only provides the same functionality, but is a dynamic as it gets. If anything goes wrong with the messaging system itself or problems with a specific message this needs to be resolved before the data is available. Non of this is a problm with the view. Any input from anyone who has this in place(either solution) appreciated. Or any arguments against my point of view

View 1 Replies View Related

Connection To Oracle Sometimes Hangs

Jan 3, 2006

Hi,For a customer we have implemented a datawarehouse with a connection toOracle. From the beginning of the project the connection to Oraclegives troubles. The next morning is the connection still active andwhen the customer looks in the processes of SQL Server a " Wait type,RESOURCE_SEMAPHORE." is shown. For what i know. SQL Server is waitingfor a resource, in this case Oracle but doesn't tell me anything aboutwhat could be the problem.The customer uses the Oracle driver instead of a microsft oracledriver.What could it be? and how can we tackle this problem? Are there ways tolook from Oracle point of view? Can we monitor something?Greetz,Hennie

View 2 Replies View Related

SSRS Oracle Connection

Apr 18, 2007

Hi,



We are trying to change our current SSRS reports to point to an Oracle 10g database. I can connect to the Oracle db from the report server via the ODBC Administrator and also through the BI 2005 studio. The connections work fine. However, when I publish the reports and try to run them from the web I always get a connection error. When I use the oracle connection it acts as if it doesn't see the oracle client and when I use ODBC it throws a werid 114 error.



Is there anything I am missing? I just don't understand why the report will work in BI Studio, but not when it is published (on the same machine)?



Any thoughts?



S

View 1 Replies View Related

Oracle Connection Trouble

Oct 19, 2007

Hi,

I'm trying to get some developers going with ssis on their desktops running



XP sp 2

Microsoft SQL Server Integration Services Designer
Version 9.00.3042.00

Oracle client drivers 9.2.0.1.0

Oracle Enterprise 8.1.7.4.0 and 9.2.0.1.0

I have the same setup on my machine and it's working fine. I duplicated steps I used to get data on the develoeper machine and got these errors.



Code Block

Pre-Execute
Execute phase is beginning.
Execute phase is beginning.
PrimeOutput will be called on a component. : 1 : OLE DB Source

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-01403: no data found".

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-01403: no data found".

SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.





I'm at a loss of where to go next and I need to get these guys rolling!
Next step is to reinstall drivers...but I'm doubting that is going to have any effect.

Thanks for any help

View 9 Replies View Related

Problem In Oracle Connection Through DTS

Feb 15, 2006

The problem being faced is : I am unable to access the oracle data through DTS Transform Data Task or Data Driven Query Task.

I have added the connection object . The test connection succeeds but when I move to the properties of either TDT or DDQ , It just hangs up

View 1 Replies View Related

Changing DB Connection From SqlServer To Oracle

Jan 23, 2007

Hi

I am trying to switch between oracle and sqlserver databases to read the source data. I have used a parameter file to specify the connection parameters. For SQLserver the connection looks like in the file as below:

<?xml version="1.0"?><DTSConfiguration><DTSConfigurationHeading><DTSConfigurationFileInfo GeneratedBy="abvsh" GeneratedFromPackageName="Package" GeneratedFromPackageID="{8A304BF7-5325-4079-9D92-2B9BBF8793AA}" GeneratedDate="1/23/2007 4:46:08 PM"/></DTSConfigurationHeading><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>Data Source=dl;Initial Catalog=PM_DW;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[Description]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[InitialCatalog]" ValueType="String"><ConfiguredValue>PM_DW</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[Name]" ValueType="String"><ConfiguredValue>dl.PM_DW</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[Password]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[ProtectionLevel]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[RetainSameConnection]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[ServerName]" ValueType="String"><ConfiguredValue>dl</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[UserName]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration></DTSConfiguration>

the pacakge runs fine. But when i change the connection to Oracle, it gives me error.

The config file looks like when i put oracle connection is as follows:

<?xml version="1.0"?><DTSConfiguration><DTSConfigurationHeading><DTSConfigurationFileInfo GeneratedBy="I2vshrivas" GeneratedFromPackageName="Package" GeneratedFromPackageID="{8A304BF7-5325-4079-9D92-2B9BBF8793AA}" GeneratedDate="1/23/2007 4:46:08 PM"/></DTSConfigurationHeading><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>Data Source=pm62;User ID=pcm_62;Provider=OraOLEDB.Oracle.1;Persist Security Info=True;</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[Description]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[InitialCatalog]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[Name]" ValueType="String"><ConfiguredValue>dl.PM_DW</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[Password]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[ProtectionLevel]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[RetainSameConnection]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[ServerName]" ValueType="String"><ConfiguredValue>i2pm62</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="Package.Connections[dl.PM_DW].Properties[UserName]" ValueType="String"><ConfiguredValue>pcm_62</ConfiguredValue></Configuration></DTSConfiguration>



The error which i get when running from oracle source is this:

Information: 0x40016041 at Package: The package is attempting to configure from the XML file "C:oraTOsql-DataTXIntegration Services Project1Integration Services Project1Integration Services Project1 estConfig.dtsConfig".
SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Error: 0xC0202009 at Package, Connection manager "dl.PM_DW": An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Error: 0xC020801C at Data Flow Task, OLE DB Source [1]: The AcquireConnection method call to the connection manager "dl.PM_DW" failed with error code 0xC0202009.
Error: 0xC0047017 at Data Flow Task, DTS.Pipeline: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Data Flow Task, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
SSIS package "Package.dtsx" finished: Failure.
The program '[7264] Package.dtsx: DTS' has exited with code 0 (0x0).


Please let me know if you guys have any suggestion in what i am doing wrong.

Thanks,

Vipul

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved