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..
I've created a SSIS Solution and have created Data Sources. I have two packages. One was created before the Data Sources, and one was created after. The package that was created after is using connections from the Data Sources. I want to change the package before the Data Soruces were created to use them, but when I right click in the Connection Managers pane "New Connection From Data Source.." is not an option.
I have few reports under different folders in Report Manager. Is there a way to set the data source for the whole folder rather than each report individually? There is also a folder where reports generate dynamically. Since the report project doesn't exist anymore, I can't set the data source through code. How can I set the data source for the whole folder?
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?
For seting up the connection should we use Connection Manager or New Connection From Data Source?
The advantage of using Data Source is that we have the connection avalilable at project level but I've studied it somewhere that Data Source is used for SSAS not SSIS.
But I don't have enough explanation to give it to my team, that why I am not using Data Source.
We are working on VSS, do we have to reset the connection managers if want to test our packages on some other machine(else than the one on which it was developed)?
I don't really understand the difference between the "data source" and the "connection manager" in a SSIS solution.
I tried to create a data source (if I understood correctly, I will ba able to share that connection between all packages in the solution), then I create a new connection manager inside a package. But when I make a modification in the data source, nothing change in the connection manager ?!!?
What I think is that when I'm making a new connection manager, what's really happen is a copy of the data source, is that correct ? If it is I dont't understand the avantage of making a new data source ....
I changed one data source settings in my SSIS project to use Windows Authentication instead of SQL Authentication. This data source is used to create connection managers in all SSIS packages. Soon after the data source was changed, when I opened any package using the connection manager that is based on this changed data source, I got an alert message box like the one given below showing different connection strings in data source and package connection manager. That's understandable (why can't VS 2005 do this silently or give an option to let this happen silently? that would be nice.).
Here is the problem. Even after both data source and connection manager are synchronized, still the same message box keeps on coming when ever I open any affected package (about 20 packages!). It is very annoying as you can see that old and new connection strings ARE IDENTICAL, still VS 2005 is not able to recognize this. I installed VS2005 SP1 even before I ran into this problem. I asked my friends to open this project and check the behavior on his machine. Same thing. Is this a known bug? How to fix this problem? Thanks for your help.
=====
Message box "Synchronize Connection Strings" (VS 2005 status bar shows "Performing post package load operations"):
This package contains at least one connection which is based on a datasource. The connection string for conenctions and data sources listed below are currently not identical. Connection strings of connections will be updated to reflect those on the datasource.
Connection: SqlServer.NET.HBI_DW.hbiuser
Data source: SqlServer.NET.HBI_DW.hbiuser
Old Connection String: Data Source=SDAL1060DB;Initial Catalog=HBI_DW;Integrated Security=True;
New Connection String: Data Source=SDAL1060DB;Initial Catalog=HBI_DW;Integrated Security=True;
I want to use Import/Export wizard. In "Choose a data Source" screen, I can't see "Flat File" in "Data Source" List. Please help me how to insatall (or find) flat file connection manager
In SSIS in Sql task we have option to pass parameter or variable..But in Data Flow Task when we use Data Reader Source using ADO.NET connection..There is no option to pass parameter or variable Or no option to receive a parameter or variable .
I am having a query were it need to pass a parameter.in sql task ...And Data Reader Source have to receive this parameter from sql task .
Sql Task finds a value of parameter and pass to DataReader Source in DataFlow Task .. ...
Please can any one help me to solve this problem of Receiving parameter or variable in DataReader Source using DAO.Net connection in DataFlow Task..thank you dilsa
We are going insane trying to start Report Manager on a SQL Reporting Services 2000 installation. The programmer/admin who originally set this up for us is gone.
We recently upgraded a database/application server to a new server, causing the data source being used by reports in reporting services to no longer be valid. Unfortunately, we do not have access to the original report project to 're-deploy' with the corrected data source.
We desperately need to get the reports that are installed to retrieve their data from the new database location/machine. We understand this can be done by specifying a new data source in Report Manager. (To clarify, we have NOT moved the report servier installation or database, these remain in place - it's just the deployed reports that no longer point to the correct data source.)
For some reason, our Report Manager will no longer run - when we try to launch it, we get a windows login dialog - nothing will work here. We've tried both local and domain admins and constantly get ACCESS IS DENIED 401.3 error messages that we do not have permission/problems with ACL's.
We've gone so far as to allow EVERYONE read/write access to the ReportManager and ReportServer folders and the RS virtual directories, but nothing seems to help.
Can anyone help with this? Ideas on how to change our data source, or how to get back into Report Manager?
Since we are somewhat technical, but not experts, and don't have much more time to waste, we are willing to pay $500 for this project to someone willing to access the server and fix the problem so that the reports point to the correct database and restore access to Report Manager.
We have a new installation of sql server 2005 reporting services. we can deploy reports without a problem users can connect to report server but when they try to run reports they get this error msg.
An error has occurred during report processing. Cannot create a connection to data source 'xxxxx'. For more information about this error navigate to the report server on the local server machine, or enable remote errors
in reporting services the datasource credentials are saved on the server
Problem: ColA (Source) Rounding error to PARTY_NO (Destination) I have a field of text of in a flat file that the flat file connection manager Source picks up correctly €œ70000893€? However when it gets the OLE DB Connection Destination the data has changed to 70000896. That€™s before its even Written to the database. The only clue that something is wrong in the middle is the great Data viewer shows the number as 7.000009E+07 Other clues looking at the data it appears there is a rounding error on only the number that dont end in 00 ColA (Source) PARTY_NO (Destination) 71167300 71167296 70329000 70329000 70410000 70410000 Any ideas people? Thanks in advance Dave
My source files are in C:Process folder. File names are in the format proc*.csv
I am using foreach loop container to iterate over the files in folder "C:Process"
I have a user variable "filename"
The order of my tasks
ForEach - Data Flow task - File System Task
In Data Flow task, I have ole db source which should take the dynamic filename from user variable "filename". whats happening is when i give fixed filename, without foreach loop container the package is working fine.
with dynamic filename, I am not able to run the package because of ole db source Error - cannot aquire connection from connection manager.
Hi, I dont know if I should post this question here, or on oracle forums, but here goes ...
I made a simple package that performs 2 Excecute SQL Tasks, one task does a select query on a SQL Server DB Table, the other task does a select query on an Oracle DB Table.
The package runs fine in BIDS when debugging, but when I deployed the package to a file system and added it to an sql server agent Job, it all went wrong ...
I tried the ms oledb dataprovider (ole db) for oracle but also the oracleclient data provider (ado.net) but the package always fails.
message Failed to acquire connection "aaa.bbb.oracleClient". Connection may not be configured correctly or you may not have the right permissions on this connection.
the OnError logrow for the package itself says almost the same.
Now my question is: What do I need to configure to get it all working? I think I need to configure the oracle db no? What should I configure? ...
1. Err: Test connection failed because of an error in initializing provider. Oracle Client and networking components were not found Sol: I installeed the Oracle Client tools
2. Err: ORA -12154: TNS: Could not resolve teh connect identifier specified Sol: Then I point out the TNS file
3. Now I coonect the Oracle db . I go to Connection Manager . Specifies the provide as: Native OLE DB Oracle Provide for OLE DB, Select my DB, provide teh user name and password. and check if the connection passed. Then in my data Flow Task when I add this source and check the Colmns its gives me an error./ sort of warning
The component reported the following warnings:
Warning at {C947B.......} [Ole DB Source[1]]: Cannot retrieve the column code page info from the OLE DB provider. if the components supports teh "DefaultCodePage" property, the code page from that property will be used.....
Will this warning has an significance. i dont see any error in my output data. but is there something I m doing wrong.
4. When I set my ProtectionLevel under Security in package property to "Dont Save Sensitive" in order to easily configure the package I m getting this error: failed Validation. The default one was EncryptSensitiveWithUserKey then its running smoothly. Can somebody advice me on this?
I addded the all the errors and solution for 1 and 2 becuase in that way this will be helpful in future.
I'm trying to implement a configurable way of executing a group of SQL statements using either an Oracle or SQL database as the source for the data.
I'm currently building a connection string with a Script Task and then assigning it to a package variable so that it can be used by a connection manager but this only works with a SQL OLEDB connection.
I'm holding all the connection details in a database table, hence the need for the scripting but would it be possible to extend the task to reuse a single connection manager by changing it's properties?
Hi, I want to import data from Oracle database programmatically using ODBC connection manager. I was able to import data by creating an ODBC DSN and then using it BIDS with ADO.NET connection using ODBC data provider for Oracle. Now I want to do the same programmatically. How should I create a connection manager? I tried using code below
But I get an error when acquiring connection ErrorCode=-1071611874
The connection manager "__" is an incorrect type. The type required is "__". The type available to the component is "__". Which one of the connection managers given by Microsoft here should be used? http://msdn2.microsoft.com/en-us/library/ms136093.aspx Has anyone come across similar scenario?
I am using the Oracle Provider for OLE DB (32 bit) to connect to an Oracle 10g database. I enter all the connection parameters and test the data source and I it says that the connection is successful. I then open the connection manager for the data source and see that the password has disappeared.
After reading entries on this forum, I tried to setting the package permission to UserKey, Password and others. Still no luck. Then I created an XML configuration file and set the connection string and password there as well. Still no luck.
Below is the message I am getting:
Error: 0xC0202009 at Package1, Connection manager "******": SSIS Error Code DTS_E_OLEDBERROR. 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: 0xC00291EC at Execute SQL Task, Execute SQL Task: Failed to acquire connection "****". Connection may not be configured correctly or you may not have the right permissions on this connection.
[One day I will come back here and answer questions, rather than only ask.]
I have a custom connection manager that returns an OleDbConnection. The only thing "custom" about the connection manager is how it determines its connection string. The object returned in AcquireConnection is an OleDbConnection.
OleDbConnection oleDbConnection = new OleDbConnection(this.connectionString); oleDbConnection.Open();
return oleDbConnection; }
Some of the objects within SSIS only work with an OleDbConnection, but mine is not recognized as such. Is there any way to have my connection recognized as a valid connection?
I have set up a new connection as a connection from data source, but I cannot see how to use this connection to create my Data Flow Source. I have tried using an OLE DB connection, but this is painfully slow! The process of loading 10,000 rows takes 14 - 15 minutes. The same process in Access using SQL on a linked table via DSN takes 45 seconds.
Have I missed something in my set up of the OLE DB source / connection? Will a DSN source be faster?
Currently, I am working SSIS project that its data source from ORACLE 9i. I installed full SSIS in server and also ORACLE. So, I have three connections available in server. They are the following below.
Oracle Client Microsoft OLE DB Provider for Oracle Oracle Provider for OLE DB
The problem is the three component can not recognize parameterization and ORACLE store procedures.
Finally, I executed the store procedures by ORACLE package from command prompt (bat files). So, the command execute from SSIS package.
We met a problem when we move on Oracle 10g from Oracle 9i in the loading process. The scenario is that SQL Server as Data Warehouse, SSIS as ETL tool. But the length of string column as data source table in SSIS grows up 4 times when using Oracle 10g. For example, ABC table x varchar2(8) --> x varchar(32) -->x varchar(8) --> x varchar(8) {Oracle 10g SSIS(source) SSIS(target) SQL Server}
This doesn€™t influence the loaded result (I did not find any problem until now), but that influence loading performance and we worry about potential problems. Do you have any idea or have you met the same problem before?
I have the 180 day trial set up on my computer. I can publish reports to the report server, connect to the report server, etc. When I try to connect to the report manager, I get the following error:
The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version.
I have viewed other solutions to this hand have checked some of the typical problems, but to no avail. What should I look for?
Hi, I'm new to SSRS but have a problem connecting to Oracle. I have placed my reports upon a reporting server but the shared data connection isn't working and I'm confused as to why. I have specified the name, connection type as "Oracle", set the correct user id and password for the credentials and the connection string as for example 'Data Source=oracleExample;Unicode="True"'. I also have set the correct entry in the tnsnames.ora file for this datasource, example: "oracleExample = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 12.23.45.23)(PORT = 3425)) ) (CONNECT_DATA = (SID = oracle) ) )" Unfortunately I receive the following error when trying to connect. " An error has occurred during report processing. Cannot create a connection to data source 'oracleDataSource'. For more information about this error navigate to the report server on the local server machine, or enable remote errors " I'm mystified as to what the issue is. Any ideas? Would restarting the report server pick up the tnsnames.ora file? I have this working locally using report designer. Any help, much appreciated.
I'm attempting to use SSRS (2005) with my Oracle RDB database (old DEC RDB), using an ODBC driver. I am able to create a report, and view data, but I need to add some report parameters to filter the data. When I attempt to use parameters in the query, I either get a syntax error, or a message that my odbc connection cannot use "named" parameters, and that I should use "unnamed" parameters.
The "Tickler_Date" field is a binary date field. The above syntax is pulled from a Crystal Report, which I am considering moving over to SSRS. Ideally, I would like the user to enter a single RunDate parameter, and have the query do the following:
Where (Application_Notes.Tickler_Date >= @RunDate and Application_Notes.Tickler_Date < (@RunDate + 1 day) )
If the user has to enter two dates, that will be fine, but I can't seem to get past the errors.
I have several oracle reports which running in SharePoint Integrated mode. They were working well when the SharePoint server and report server in same machine. But after that the report server move out to another machine, we got the problem with connection to oracle databasen. Reports with Sql server as data source has no problem at all.
We have installa both oracle client and odp.net in both machines, and tried to give Network Service full access right in Bin catalog in oracle client but nothing helps. Please help!!!!!
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'Ellentst'. (rsErrorOpeningConnection)
For more information about this error navigate to the report server on the local server machine, or enable remote errors
In log file:
Cannot create a connection to data source 'DatabasName'. ---> System.Exception: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.
We have installed oracle client version 10.2.0 in both machine.
I am using SSIS 2005 on Windows 2003 server. Using Excel Source to dump the data for staging database. I am getting following error while I execute it through BI studio's execute button.
I have deployed my packages into Sql Server and I am using Configuration File. As my Data Source is Excel, I have changed the connection string during deployment with Server Path. But I am getting the following errors. Actually the File Exist in Path. May I know What is cause of the issue? Do I need to give any permission to execute the package.
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
component "Excel Source Service Contract Upload" (1) failed validation and returned error code 0xC020801C.
One or more component failed validation.
There were errors during task validation.
DTS_E_OLEDBERROR, Error Code: 0x80004005 Source: "MS JET DB Engine" Description : Path is not valid
Today I was making a few reports. When I tested the reports in Visual Studio, they worked great: I got the expected result. But when I deployed the reports to our reportserver the problem started. When I click on the directory in which my reports are deployed, I got my 4 reports. Till now everything worked correct. But when I click on a report to view the results it went wrong. I got an error: "Cannot create a connection to data source 'Live'" (Live is the name of our data source).
We are using the Windows Logons and I am sure that I have all the rights on the server, I gave myself 'sysadmin' rights, so it should work. I also have tried it with all the roles assigned on my account, but then it still won't work.
When I modify the data source, and set it to another server en database it works. The datasource 'Live' exists on a x64 MsSQL server, en the other datasource is on a x86 MsSQL server. Maybe that is the problem?
