January 4, 2013 at 9:31 am
Hi Fellow Developers
I have been using a configuration setup as following:
I have an system environment variable called SSISConfig that points to a config file called SSISConfig.dtsConfig which looks like this:
<Configuration ConfiguredType="Property" Path="\Package.Connections[SSISConfig].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=ConfigServer;Initial Catalog=ConfigDB;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue>
</Configuration>
inside my package I always have a connection called SSISConfig.
I have enabled Package Configurations and have an:
-initial configuration which is an Indirect XML Configuration which maps the contents of the SSISConfig.dtsConfig file to the SSISConfig Connection via the environment variable.
-second configuration that is SQL Server type configuration and uses the SSISConfig as its connection. All my local package variables that are dynamic are stored in a configuration table called SSIS_Configuration
So thats basically the set up I have for configurations. The configurations are picked up from the entries in the SSIS_Configuration table when running from BIDS. They are also picked up correctly when running from within Integration Services.
The problem I am having is when I create a job agent and run the job manually or schedule it the configurations are not being picked up. In other words I have a variable called my_count that I set using the SSIS_Configuration table and this variable is not being set from the SSIS_Configuration table. I have noticed that the value the Job picks up is actually whatever value was in the SSIS_Configuration table at the time I uploaded the package from BIDS to MSDB.
I have tried restarting SQLServer thinking it may be a disconnect between the environment variable but that didnt solve the problem. I have also searched the dtsx files xml and cant see the particular value for my_count in the xml file so I am not sure where it is getting that value.
Also I have not specified any configuration information at the Job level.
Any Help would be appreciated.
January 4, 2013 at 11:50 am
... an update ... I was not able to recreate this scenario in our TEST environment ... in other words everything worked correctly with the job acting as I ran it in BIDS and picked up the correct value from the configuration table ... Still dont know whats happening in my DEV environment ...
The only thing I can think of is something is different with the service accounts ?
January 4, 2013 at 3:30 pm
ok fixed the issue it was related to the service agent account not having the correct rights
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply