Greetings, We have an SSIS package that configures a database connection and the log file connection to point to different targets depending on the environment (development, test and production). The connections in the SSIS package point to the development environment. The configuration is stored in a table. Were executing the package through an agent job that executes an SSIS step. The configurations are all working correctly and the job succeeds in each environment. The issue were having is that the production job history shows errors (even though the job succeeds). These errors arent displayed for the job in test or development environments. The other relevant fact is that this job executes under a development service account in test and dev (the same account with the same permissions in both environments). In production it executes under the production service account with permissions only to the production environment. In fact this is occurring for all SSIS steps in all agent jobs in production, so Im pretty sure that the issue is related to the fact that the production service account doesnt have permissions to the development environment. I think that the errors being displayed in history are occurring in the initial validation phase before the configurations are applied. This doesnt happen in test because the service account in the test environment has permissions to the development environment. Heres a sample of the history output: Date 6/5/2012 3:45:33 PM Log Job History (Load_mlh_test_job_history) Started: 3:45:33 PM Error: 2012-06-05 15:45:33.77 Code: 0xC0202009 Source: Load_mlh_test_job_history Connection manager "CDW_ETL_Config" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'BCOHOME\service_cdw'.". End Error Error: 2012-06-05 15:45:33.79 Code: 0xC0202009 Source: Load_mlh_test_job_history Connection manager "CDW_ETL_Config" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'BCOHOME\service_cdw'.". End Error Error: 2012-06-05 15:45:33.80 Code: 0xC001401E Source: Load_mlh_test_job_history Connection manager "log" Description: The file name "\\mdcdwssis01v\SSISR2Packages\CDW ETL\Logging\Load_mlh_test_job_history\2012-06-05.log" specified in the connection was not valid. End Error etc DTExec: The package execution returned DTSER_SUCCESS (0). Started: 3:45:33 PM Finished: 3:45:34 PM Elapsed: 0.421 seconds Again, the job succeeds, the package succeeds, and the log file is created in the correct location in production. The main reason this is an issue is that the errors in the history view are pushing down actual errors, sometimes to the point that they scroll out of the field. My question is this: Is there a way to prevent the pre-configuration errors from showing up in the history? Ive tried setting DelayValidation at the component and the package level, but that didnt change the behavior. I havent been able to find this specific issue documented anywhere. Assuming that theres no magic switch that will fix this, I see that I only have a few options: 1 Live with the issue. Look to the log file for the errors when needed. 2 Grant permissions to the production service account to the development databases. (This seems to be a bad solution. We dont grant access to the production service account, because we want to be sure that the package is configured correctly and will fail if incorrectly pointed at dev.) 3 Point the connections to the production data sources in the package before deploying. This increases the risk that a developer will accidentally execute a package against production data also no good. Thanks for any help. Mike Hayes

Before promoting the package point to prod settings, Also you may want to disable the validation of the package as follows: DelayValidation property set to TRUEValidateExternalMetadata property set to FALSEWork Offine option ticked Arthur My Blog

Thanks for the reply: "Before promoting the package point to prod settings" >> I'm hoping to avoid that. "DelayValidation property set to TRUE" >> I tried this. No effect. "ValidateExternalMetadata property set to FALSE" >> I tried this. No effect. "Work Offine option ticked" >> I'm pretty sure that this only affects validation in BIDs. Right? Mike Hayes Mike Hayes

"Work Offine is only for BIDS and actually is not applicable to your scenario Mike. "DelayValidation" must be the one to use, but based on the errors you have a security issue, not a config issue, it seems more like you need to use a proxy to run your package via the SQL Agent Arthur My Blog

We are using a proxy account. The issue is not the account used, but the permissions granted the account in the development environment. Our package connections are configured, and point to development locations.The account used as the proxy does not have permissions in the development configuration table. (It turns out that the account does have permissions to the development databases. The configuration table is on a different instance.)The configuration connection is pointed to the production configuration table when scheduled to run through an agent job. When the package is intially validated, it throws errors when trying to connect to the development databases. After the new configuration is read, the connections validate successfully, and the job succeeds. I believe that this is normal behavior, I'd just like to turn off the initital validation errors. I'm convinced that the validation errors would go away if we just grant the permissions in the dev database. Mike HayesMike Hayes

yes, Mike, it is normal. and I trust setting DelayValidation to be True is needed.Arthur My Blog

Sorry for the delay in getting back to you. I just got time to come back to this. I understand what you are saying. The problem is that it doesn't work in our environment. I have a test job in our production environment, and I had the DBA change the permissions to the proxy account and here are the results: Delay Validation Permission Granted? Connection Error to configuration Table? False Y N False N Y True Y N True N Y Again, these are the results of job executions. Not executions from BIDs. Mike Hayes Mike Hayes

Permission granted = N and no error connecting? Does not make sense. How does the command line looks like of the job?Arthur My Blog

Sorry, I guess my table wasn't clear: Delay Validation: FalsePermission: Access GrantedError in history?: No Delay Validation: FalsePermission: No Access GrantedError in history?: Yes Delay Validation: TruePermission: Access GrantedError in history?: No Delay Validation: TruePermission: No Access GrantedError in history?: Yes Here's the command line: /FILE "\\mpcdw02r\SSISR2Packages\CDW ETL\Load_mlh_test_job_history\Load_mlh_test_job_history.dtsx" /CONNECTION "CDW_ETL_Config";"\"Data Source=mpcdw02r\instance2;Initial Catalog=CDW_Config;Provider=SQLNCLI10.1;Integrated Security=SSPI;Application Name=SSIS-Package-{1B6EEE3D-5207-4719-988C-238F220E130B}mdcdwssis01v.CDW_Config;Auto Translate=False;\"" /CHECKPOINTING OFF /REPORTING E Thanks, Mike Hayes Mike Hayes

I see you run it using the integrated [Windows] security thus you need the proxy with all the proper access rights, and hence each time I see Access Granted the Error in history? is a No, what is the big deal to make a proxy that has all the needed security set?Arthur My Blog

In my original posting I pointed out that that would work. The reason that I don't like that solution is that we are trying to keep a separation between development and production environments. Since that original posting, we had an outage in our development environment, and one of the consequences of this was that the initial validation of connections had to time out instead of being actively refused. Since our production process involves the execution of many SSIS packages, the consequence would be a significant increase in time it takes to get our overnight batch process completed because of those timeouts. Mike HayesMike Hayes