Need help with preconfiguration valdiation errors
-
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 package point to the development environment. The configuration is stored in a table. We’re 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 we’re having is that the production job history shows errors (even though the job succeeds). These errors aren't 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 I’m pretty sure that the issue is related to the fact that the production service account doesn’t 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. The errors don’t occur in test because the service account in the test environment has permissions to the development environment. Here’s a sample of the history output:
Date6/5/2012 3:45:33 PM
LogJob 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.
One problem with this is that the errors appearing in the job history are making it difficult to use that information for troubleshooting real errors. The bigger issue is that when the development environment is offline, these initial validation connections have to timeout instead of being actively refused. This slows down the execution of production packages.
My question is this: Can these pre-validation connections be disabled or turned off? I’ve tried setting DelayValidation at the component and the package level, but that didn’t change the behavior. I haven’t been able to find this specific issue documented anywhere. Assuming that there’s no “magic switch” that will fix this, I see that I only have a couple of options:
1Grant permissions to the production service account to the development databases. (This seems to be a bad solution. We don’t 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.)
2Point 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 you can provide.
-
Hi mleehayes
Based on your description, are your production packages using a SSIS Configurations table located on a Development Database hosted by a Development Server?
If that is the case, your SSIS Configurations table should be located on a database hosted by your production environment, and an Environment Variable could be used to point to an XML configuration file resolving the location of the SSIS Configuration on the production environment.
The model you are using expects an SSIS Configurations table hosted by a database on each environments, when you described the Test Environment referencing the configuration table on the development server, tell us that your implementation is not correct.
Would you be able to clarify that my understanding of your environment is correct? then we could explain this pattern.
Cheers,
Hope this helps,
Rock from VbCity -
The production configuration table is hosted on the production server. The development configuration table is hosted on the development server (likewise test on test).
The connections in the package are pointing to development databases when deployed. When the configuration database is read, the connections repoint to the production databases. This all works fine. The problem is that there must be a validation step that occurs while the connections are still pointing to development. That's being recorded in the job execution history.
Here's the command line I'm using for a test job that I've been playing around with (in our production environment):
/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
Mike Hayes
-
Hi mleehayes
Sorry for the late reply, it took me a while to digest the information found; let's review it:
From the following MSDN article: Behavior Changes to Integration Services Features in SQL Server 2008 R2
On its section Behavior Changes Related to Package Configurations it explains the steps followed by the DTEXEC command when applying the /CONNECTION switch
In SQL Server 2008 Integration Services, events occur in the following order:
1. The utility first applies the design-time configurations.
2. The utility then applies the run-time options that you specified on the command line when you started the utility.
3. Finally, the utility reloads and reapplies the design-time configurations.
If my understanding is correct, the DTEXEC command first applies the connection found in your package's connection manager, which is pointing to the development environment; this step might be causing the errors you are getting.
The second step of the procedure applies the CONNECTION you passed to the command line parameters, using it on its final step, these last two steps ensure your package will run on the appropriated environment.
SUGGESTION
If you agree with my interpretation, I will propose the following solution.
1. Add an XML file setting the configuration string, it will look like this:
<?xml version="1.0"?><DTSConfiguration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[CDW_ETL_Config].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=mpcdw02r\instance2;Initial Catalog=CDW_Config;Provider=SQLNCLI10.1;Integrated Security=SSPI;</ConfiguredValue>
</Configuration>
</DTSConfiguration>
2. You can assign any name to the config file, I am using *.dtsconfig as its type. The file could be located anywhere in your file system.
You may create this file at the folder C:\CDW_ETL_Config, initially on the server you will test drive this suggestion, if you are happy with this approach, the same folder-file will be created on the remaining environments.
3. On the offending package, the one you will be using to test this suggestion, add a Package Configuration (I believe, you already defined a package configuration), following the steps on the picture below:
NOTE
I am using an environment variable containing the location of the XML configuration file, the screenshot in this reply shows features of my implementation, I just tried to use what I got to explain the suggested solution.
MSDN articles
Behavior Changes to Integration Services Features in SQL Server 2008 R2
This suggestion is based on the way I understood the MSDN article, if you agree with me and try it out, let us know the outcome.
Cheers,
Hope this helps,
Rock from VbCity -
Thanks so much for taking all this time to reply to my message. It'll take a while for me to get this setup and tested. I'm not sure I'll be able to get to it today. If not, I'll work on it next week.
I'll let you know what I find out.
Thanks again,
Mike Hayes
-
No worries mate, take your time, it will be interesting to find out the outcome of my suggestion!
Cheers,
Hope this helps,
Rock from VbCity -
OK. Here's what I did.
I was getting an error on your XML Configuration file, so I created one with the following:
<?xml version="1.0"?>
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo
GeneratedBy="BCOHOME\E100557"
GeneratedFromPackageName="Load_mlh_test_job_history"
GeneratedFromPackageID="{2FB9E723-3F10-48EF-9F7F-D378EEC6FBB7}"
GeneratedDate="6/29/2012 3:06:19 PM"
/>
</DTSConfigurationHeading>
<Configuration ConfiguredType="Property" Path="\Package.Connections[CDW_ETL_Config].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=mpcdw02r\instance2;Initial Catalog=CDW_Config;Provider=SQLNCLI10.1;Integrated Security=SSPI;</ConfiguredValue>
</Configuration>
</DTSConfiguration>
I put this file in \\mpcdw02r\SSISR2Packages\CDW ETL\Load_mlh_test_job_history\config. This is a location accessible to the service accounts from all 3 environments. I couldn't reference a local path because I don't have access to the file systems on the servers except through shares.
I executed the job on dev (with the XML pointing to mdcdwssis01v - our dev server) and it succeeded without displaying any errors in the job history. When I changed the XML to point to production and executed the job on production, the result was the same as before:
Version 10.50.2500.0 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started: 3:14:31 PM
Error: 2012-06-29 15:14:31.89
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-29 15:14:31.90
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
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 3:14:31 PM
Finished: 3:14:32 PM
Elapsed: 0.453 seconds
As near as I can tell, in step one of your execution order above: "The utility first applies the design-time configurations", the errors get thrown because the production service account doesn't have access to the development server where the connection is initally pointed. Based on all the reading I've done (including some excellent sources that you listed), there's no way to control that initial behavior.
I welcome any other suggestions.
Thanks for the help.
Mike Hayes
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply