Package Configuration causing problems with Oracle Connection
Hi All, Scenario: We need to import the data from Oracle 10g database to SQL Server 2005 database. Solution Picked: In order to accomplish the above, we used SQL Server Integration Services along with Business Intelligence Developer Studio. Details: Base Details: There are around 17 packages, each retrieving data from Oracle and storing into SQL Server based on current date. Following are the steps used: Get current date from SQL Server [Execute SQL Task]. Create Query based on date obtained from step (1) [Script Task]. Based on query obtained from step (2). Inside a [Data Flow Task] following sub actions are performed: Actions performed in [Data Flow Task] Retrieve data from Oracle database using query through [OLE DB Source]. Perform any data modifications to correct the data [Derived Column]. Store data in SQL Server [OLE DB Destination]. Rejections are saved in text file [Flat File Destination]. Current Solution: Currently we have the database settings built inside the Package. It is working fine even after creating the job and schedulingit. But since the current scenario is in Test environment therefore while deploying to lactual production environment we will have to change the database configurations. There might be a possibility that we might miss some place and thus cause problems later on (we are only human). Therefore the Package Configurations were created for different database connections. Each in separate XML file. We have got the SQL Server connection working. But are facing an issue with Oracle connection. Problem: The Oracle connection is established using the "MSDAORA.1" provider. The configuration details provided in the XML file are Username, Password, Server Name, Connection String. But whhen executing the package through BIDS we are getting the following error: ------------------------------------------------------------ [Connection manager "<Connection Name>"] Error: 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.". ------------------------------------------------------------ Please note that the Protection Level of Package is set to "DontSaveSensitive". Would request your assistance in resolving the issue. Saqib Mustafa Abbasi
October 26th, 2010 4:11am
please check the following link.. http://xlinesoft.com/dss/ViewKBArticleT.asp?aid=127 Let us TRY this | My Blog :: http://quest4gen.blogspot.com/
October 26th, 2010 4:41am
Hi, The package is working fine before saving the configurations in XML file, therefore the data shouldn't be an issue. What we are guessing is that the problem is somewhere in the Connection itself, I have checked the following links as well: http://support.microsoft.com/kb/269495 Can anyone tell me what is the CLSID for "MSDAORA.1" because I can't seem to find it :( Thanks and Regards, Saqib Mustafa Abbasi
October 26th, 2010 4:48am
Hi, One more thing I want to add is that this error is showing in the designer as well. SMA
October 26th, 2010 4:52am
In the configuration file add only connection stirng instead of all the properties you have mentioned in the original post. Also, you have to manually enter the password in the config file in case you want to read it from the config file (Don'SaveSensitive as package proetction level).Nitesh Rai- Please mark the post as answered if it answers your question
October 26th, 2010 4:58am
I do exactly what you are doing but I use an ODBC connection using the Oracle Client drivers. I then configure the package using an Indirect configuration. I have had no issues using this channel.
October 26th, 2010 5:19am
Hi Nitesh, We have added the password manually into the configuration file, and we have provided both Connection String as well as the separate details. SMA...
October 26th, 2010 5:28am
Hi XcX, Since the ODBC connection is not supported with [OLE DB Source] therefore kindly inform which component was used in your case. Did you use [Data Reader Source]. Regards, SMA...
October 26th, 2010 5:55am
Hi Saqib Yes I use the [Data Reader Source] . You need to create a connection manager which uses the ODBC data provider. You need to set up a DSN in the ODBC Administrator that uses the Oracle Driver. Let me know if you wanna try this, and I will assist you. Regards
October 26th, 2010 6:16am
Hi XcX, Since we need to pass the date as parameter to the query in OLE DB Source which was being done in current scenario achieved using the Script Task. Kindly assist how we can use the query in a variable with the DataReader Source. SMA...
October 26th, 2010 6:42am
Hi SMA You can use a package variable for the query. You can then build the query using your script task and assign the built query to a variable. You can then use 'Expressions' in the [Data Flow Task] to assign the variable to the SQLCommand of the [DataReader Source].
October 26th, 2010 7:34am
hi XcX, Due to urgency we have opted to hard code the Oracle connection, will check the approach you have specified. Thanks for the time and help. SMA...
October 27th, 2010 5:52am
Hi Saqib Ok no problem Let me know once you have tried my approach. Cheers
October 27th, 2010 10:52am
hi XcX, I have tried the ODBC approach and it has worked for test now we will use it for live. Many thanks for the approach sorry for the delay response. SMA...
December 8th, 2010 5:50am