December 19, 2017 at 9:56 am
Hello
Using SQL 2008R2
I work in a dev->test->prod environment
I have a package
In this package there are a number of connections
There are variables that control where the connection points (ServerName and InitialCatalog)
So for each connection, it utilises the content of the 2 variables and replaces via expressions
There's an initial Package Config that defines the initial source (so I have 1 for dev, 1 for test, ...)
The source for each variable is held in a table
So, I have a SQL task that extracts data from this table and allocates the value to the variable
So, if this runs on dev, it uses the dev package config for the initial server and database
Then the sql command runs against the Server/DB and extracts the dev connection sources
The rest runs against these sources
All good, as it works (appreciate there are other ways to do this but it is what it is)
I've just introduced a new source using the exact same methd as previous and now i'm getting security issues
Only when it runs as a job; If I run through SSIS, it works fine
Question
As this is allocating the connection dynamically, how do I establish exactly what it is pointing to
All I seem to get in the log (and I've looked at the options) is 'Failed to acquire connection <database>'
No server name or who it's running as
I suspect it's connected with the SSIS step in the job but really want to look at the detail behind its execution first
Thanks
Damian.
- Damian
December 19, 2017 at 11:14 am
The expression that contains that initial connection string, does it have a value or is it blank? Also, have you set your package, connection managers and tasks' "Delay Validation" property to true?
I've seen this happen at times because logging starts before configurations are applied, so if the connection string contains no value or if it is trying to validate connection managers you could see errors.
December 20, 2017 at 4:46 am
Thanks for the reply
The variable has a value in it. By default it points to my development server - although I expect this to change at run time
DelayValidation for the connection is set to false
For the package it's set to True
I did think the package took presedance
This doesn't look to have solved the problem
To get the run time values i've temporarily, built my own logging into the package
This writes away the variable values
It's proven that everything is working as expected with runtime setting
- Damian
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply