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.. Let us TRY this | My Blog ::
Free Windows Admin Tool Kit Click here and download it now
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: 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
Free Windows Admin Tool Kit Click here and download it now
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.
Free Windows Admin Tool Kit Click here and download it now
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...
Free Windows Admin Tool Kit Click here and download it now
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...
Free Windows Admin Tool Kit Click here and download it now
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...
Free Windows Admin Tool Kit Click here and download it now
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...
Free Windows Admin Tool Kit Click here and download it now
December 8th, 2010 5:50am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics