May 1, 2012 at 10:45 am
I have packagages that transport data from SAP using ADO.net connection which requires password to remote SQL box. I created packages using my local host because i have authentication to both. Now I want to test one of the packages as job in sql server agent. It keeps failing. The history doesn't say much. IS pacakage configuations a must in deployment? Do i have to supply ADO.net connection password again somewhere?
May 1, 2012 at 11:43 pm
Either you supply the password with package configurations (which I prefer), or you type them in into the connection manager and you set the Protection Level to one of the following:
* ServerStorage (if you deploy to MSDB)
* EncryptSensitiveWithPassword
* EncryptAllWithPassword
The last two introduce yet another password, so I would just stick with configurations.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 3, 2012 at 1:01 pm
Koen Verbeeck (5/1/2012)
Either you supply the password with package configurations (which I prefer), or you type them in into the connection manager and you set the Protection Level to one of the following:* ServerStorage (if you deploy to MSDB)
* EncryptSensitiveWithPassword
* EncryptAllWithPassword
The last two introduce yet another password, so I would just stick with configurations.
Thanks Koen.How do you supply password through configuration file. When I edit the config file i can't locate where i can change/add passwords for connection strings. Additionally, my job ran well yesterday but the same job failed this morning (there is no problem with source or destination because it runs well in BIDS).Also is it a good practice use proxy to run jobs given that user accounts will keep changing? What would be the best alternative? TIA.
May 9, 2012 at 6:09 am
If you would like to pass connection strings through configuration files, you could see like below in the file...here you can edit passwords
<Configuration ConfiguredType="Property" Path="\Package.Connections[Target].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>Data Source=.;User ID=sa;Pwd=*****;Initial Catalog=DataBaseName;Provider=SQLNCLI10.1;Auto Translate=False;Application Name=SSIS-Package-{1FFCBB06-7C2F-497A-82A8-20C1CBEF3BA5}LocalHost.DatabseName.sa;</ConfiguredValue></Configuration>
Thanks,
Charmer
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply