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
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
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
Nov 28, 2005
Hi everybody,
View 13 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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