Problem with running Package from another user using XML file for connections
Hello, i have developed a package, use xml configuration file. On test system if i deploy and run it, this is working fine. But in production, someone else has to deploy it, run this and use same xml config file which i developed. The problem is on another user can deploy it successfully but during execution causing connection Error even it is the same xml file. It is due to other different user? I have to make any change in the package that any user can run it? Or any other reasons behind this? Please help me in this regard, it is urgent. Many Thanks in advance Dani
May 23rd, 2012 9:50am
Hello Dani, I guess the error is due to the incorrect package protection level setting. If you plan on running in using the Agent then you need to change the package to RelyOnServerStorage setting, and if off the file system then you have an option of EncryptSensitiveWithpassword and provide it. See more on how to properly deploy packages: http://decipherinfosys.wordpress.com/2008/09/16/deploying-ssis-packages-in-sql-server-2005/ (SSIS 2005 and 2008 should be good), if 2012 you may want to see http://datachix.com/2011/12/01/sql-university-ssis-2012-deployments-lesson-two-part-a/ instead.Arthur My Blog
May 23rd, 2012 9:58am
Hi, Thanks for your quick reply. in the ProtectionLevel there are some options and i am using DontSaveSensitive. You mean to change it as ServerStorage? I am using sql server agent. Best Regards Dani
May 23rd, 2012 10:14am
Yes, your best option it is. Thing is when you use DontSaveSensitive then a new user needs to provide the password, so this option is not good for deployments in general at all.Arthur My Blog
May 23rd, 2012 10:30am
But the problem is, it is not allowing me to save the package with ServerStorage option. How to solve it?
May 23rd, 2012 10:59am
use this dialog: http://www.bing.com/images/search?q=save+the+package+with+ServerStorage+option&view=detail&id=C2B0C981E76686E104FC65C3686B8BAE919C26B8&first=0&FORM=IDFRIR and make sure you choose it from the drop-down where the pointer isArthur My Blog
May 23rd, 2012 11:01am
Hi, Thanks for this link. To implement it i was just accessing prod. serv. from Package for selecting the folder in integration services. But due to limited rights i cant access the Integ. Services on this server. Without this access it is not possible to select the folder thats why first i have to talk with with Admin. Thanks for your help Best Regards Dani
May 23rd, 2012 12:09pm
Dani, before you deploy, you can set it, too: http://www.google.ca/imgres?q=bids+protection+level&hl=en&safe=off&sa=X&biw=1280&bih=814&gbv=2&tbm=isch&prmd=imvns&tbnid=52TaOLJYqlwkWM:&imgrefurl=http://bi-polar23.blogspot.com/2009/04/ssis-and-package-protectionlevel.html&docid=DohFzxfe0p-PBM&imgurl=http://lh4.ggpht.com/_EUgQcLQ7WNI/SdkasDaOCPI/AAAAAAAAAuE/Bf34QzbXteA/ProtectionLevel00_thumb%25255B1%25255D.png&w=294&h=390&ei=MAy9T5qAGOPO2wWYh6CcDw&zoom=1 ServerStorage it is called (on the package level) propertyArthur My Blog
May 23rd, 2012 12:14pm
Thanks Arthur for this links. But i already set the option DontSaveSensitive during my whole development. As you said i also want to save my package with the option as ServerStorage before i deploy but BIDS not allowing me to save the project with this property. If i stay with DontSaveSensitive and a different user run this package, it expecting password for database and throwing error even this is stored in xml file. What to do in this case?
May 23rd, 2012 5:05pm
Dani, the option DontSaveSensitive is rarely needed. When you say you cannot set it to ServerStorage, do you get an error? Every package that needs to be deployed to SQL can be set to do so. Right click on the MSDB and choose import package, specify at the import time. If you give the package to somebody else, then DontSaveSenstivie is OK, even for running it is BIDS, but not before the new user can fill in the credentials.Arthur My Blog
May 23rd, 2012 5:11pm
Hi, Error when saving as ServerStorage coming due to target server (Oracle DB). This ProtectionLevel is not supported from target server. My source is sql server and target is oracle. All i need is that different users (DBA) allow to run my package with help of xml file (if all the credetials are correct). I still dont know exactly which opiton is suitable in this case: DontSaveSensitive EncryptAllWithPassword EncryptSensitiveWithPassword EncryptSensitiveWithUserKey ServerStorage (Not working in this case) Due to some restrictions i am not allowed to access the Prod. Server and i am delivering three files for every package (xml config, manifest and dtsx). User (DBA) can deploy it successfully but cannot run and facing passwort error. Is it really a big problem in SSIS get this independency? Dani
May 24th, 2012 5:52am
You cannot set ServerStorage in BIDS. (it's not a server) You need to change it to ServerStorage when you import it into the MSDB database using the SSIS service. The fact that your destination is Oracle has nothing to do with it. ServerStorage is used when you save the SSIS package in the MSDB database of your SQL Server. I assume you don't save your SSIS packages in Oracle :) @Arthur: I always use DontSaveSensitive. It forces the developers to use configurations properly, which is a good thing. MCTS, MCITP - Please mark posts as answered where appropriate.
May 24th, 2012 5:57am
You wrote 'I assume you don't save your SSIS packages in Oracle :)' What you mean exactly, Save Package in Oracle?
May 24th, 2012 6:13am
You wrote 'I assume you don't save your SSIS packages in Oracle :)' What you mean exactly, Save Package in Oracle? You said: "Error when saving as ServerStorage coming due to target server (Oracle DB). This ProtectionLevel is not supported from target server." Hence implying that you are trying to save the SSIS package on Oracle.MCTS, MCITP - Please mark posts as answered where appropriate.
May 24th, 2012 7:09am
Sorry my message was not clear enough. I am not storing on Oracle. It shows that there is a problem due to xml file. Here is the Error Message: Failed to apply package protection with error 0xC0014061 "The protection level, ServerStorage, cannot be used when saving to this destination. The system could not verify that the destination supports secure storage capability.". This error occurs when saving to Xml.
May 24th, 2012 7:43am
With destination the error means the server which will store the SSIS package, not the destination in your dataflow. Here's a thread of someone with the same issue: http://social.msdn.microsoft.com/Forums/zh/sqlintegrationservices/thread/40d7b04b-f7e6-49f1-83bb-7b72d8453ab6MCTS, MCITP - Please mark posts as answered where appropriate.
May 24th, 2012 7:49am
Thank for this link. But I am not allow to access the MSDB Folder for export the package because it resides on the Destination Server (Prod).
May 24th, 2012 7:59am
Then I would go with DontSaveSensitive and make sure the passwords for your connections are stored in the XML config file.MCTS, MCITP - Please mark posts as answered where appropriate.
May 24th, 2012 8:06am
This approach i am already using. I developed the package with DontSaveSensitive and using XML config file. As connection manager using ConnectionString for all credential information. Now i got the Information that Prod. Serv. is 64-Bit Server and my development server is 32-Bit. It can be the reason for getting connection error (oracle) 'Destination Server failed with error code 0xC0209302' ??
May 24th, 2012 8:28am
Can you post the entire error? Make sure you have 64-bit Oracle providers installed in the production server. You're not using the Microsoft OLE DB Provider for Oracle, don't you?MCTS, MCITP - Please mark posts as answered where appropriate.
May 24th, 2012 8:31am
I am using Oracle Provider for OLE DB. But i am successfully deploying and running the package and transfering data from sqlserver to oracle. The issue is for another user (like DBA) to run my package. As i am usingin XML config ConnectionString for Connection Manager. It is neccessary to checked the option for Password also? Even all information are stored in connection string in xml file!
May 24th, 2012 8:47am
I am using Oracle Provider for OLE DB. But i am successfully deploying and running the package and transfering data from sqlserver to oracle. The issue is for another user (like DBA) to run my package. As i am usingin XML config ConnectionString for Connection Manager. It is neccessary to checked the option for Password also? Even all information are stored in connection string in xml file! Goh. It's a long time since I configured a package like that (using XML and Oracle). I believe I checked the password box also. Make sure you change it in the config file, because SSIS only saves ***** (or nothing at all). It just makes an entry in the config.MCTS, MCITP - Please mark posts as answered where appropriate.
May 24th, 2012 8:49am
Is this a problem if the package is developed on a 32 Bit Server then deployed and Run on 64 Bit Server? When yes what must be activated for running package under MSDB or in sql server job in the step?
May 24th, 2012 9:48am
Following are the errors when run on a 64-Bit Server: Error: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR. The requested OLE DB provider OraOLEDB.Oracle.1 is not registered. Error code: 0x00000000. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER
May 24th, 2012 10:18am
Please make an effort to verify if the Oracle driver 64 bit is functioning. If yes (which I doubt), then see if its 32 bit counterpart works, if yes, you need to instruct the package to run in the 32 bit mode by setting this: Arthur My Blog
May 24th, 2012 10:22am
Hi Arthur and Koen, Thanks a lot for your help and quick replies. The issue is solved and package is running fine. But functioning as job in sql server agent and not running when DBA run it manualy under MSDB (previous connection error) in integration services. The main thing is other users like DBA now capable to run packages and schedule them successfully as job. XML config file has to be add extra in every step under configuration tab even if the file is present in the right folder which you choose during development for package configuration. I wish you a nice weekend and thanks again. Dani
May 25th, 2012 8:13am