January 6, 2009 at 11:07 am
I have a co-worker who's trying to use a Configuration file for a Connection String set by a User variable. I've used a config file for setting the connection string before with no problems testing, and implemented through our 3 layers of escalation with no issue.
This new package needs to set the ole connection manager's connection string via a user variable as this value is also needed in a child package. So, the scenario works fine in testing, with a hard coded reference to the connection string in the user variable. Once the variable is populated via the config file, we'd like to remove this hard-coded value from the user variable. When that is done, the package throws an error. If the production server is referenced, then a security error gets thrown.
We are running this package via a command line passing the location of the config file. Basically there's only one variable in the file, the connection string user variable (so we can get to all the other configuration settings stored on that server).
Long story, but the question is straight forward. Why is SSIS trying to read the user variable value into the ole connection manager's connection string before the user variable gets populated from the configuration file? Anyone have a better way to get around this?
January 6, 2009 at 1:00 pm
If I understand your post correctly then it's probably because it's set to validate.
In your tak properties, check to see if the DelayValidation property is set to True. This allows the task to get past the validation process, set the variable and use the completed connection string at the appropriate time.
Now that being said, if I did not understand your question... can you give me a little more information and I'll see if I can help.
Thanks,
Sabrina
January 6, 2009 at 5:57 pm
Noffer (1/6/2009)
-- --Long story, but the question is straight forward. Why is SSIS trying to read the user variable value into the ole connection manager's connection string before the user variable gets populated from the configuration file? Anyone have a better way to get around this?
As Sabrina alluded, SSIS tries to validate package settings/properties/parameters in advance - and if this is what you want, it could save a lot of time - unless you set the delay validation flag.
Phil
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 7, 2009 at 12:06 pm
Thanks, we'll give that a try and post the results
January 7, 2009 at 1:58 pm
OK, so we turned all possible references of DelayValidation to true in the packages as well as the control flow components. The command we are using to reference the package and config seems straight forward...
DTEXEC /FILE "\\SERVER\Stage\SSISLoadPackages\Events.dtsx" /CONFIGFILE "\\SERVER\Stage\SSISLoadPackages\Config\DB.dtsConfig"
This command will produce the following error...
Error: 2009-01-07 14:23:38.06
Code: 0xC0202009
Source: Events Connection manager "Stage"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC Driv
ers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager] Data s
ource name not found and no default driver specified".
End Error
Yet there will be data in a table (on the server specified in the connection string) after the package completes. Now that I've got the error code logged, I see its the same for different scenarios, like adding the production server connection string as below...
Error: 2009-01-07 14:23:32.81
Code: 0xC0202009
Source: Events Connection manager "Stage"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult:
0x80004005 Description: "Login failed for user ''. The user is not associated w
ith a trusted SQL Server connection.".
End Error
So, I goggled the code and came up with the following page in the Microsoft Knowledgebase...
http://support.microsoft.com/kb/933835 Does this look like its the source of our problem? Or is this maybe just a generic error code?
January 7, 2009 at 2:25 pm
Hi,
Have you tried adding a script task to display the variable value to make sure that you are in fact reading the variable into the package?
Sabrina
January 7, 2009 at 4:36 pm
We know that the connection string gets populated into the variable because the first child package runs. Operations execute as follows...
1) writes to event log table via child package
2) writes bulk insert data to table
3) finishes the first child package
4) (either before 1 or after 3 or at some point along the way) writes error posted above to command window
5) parent package never calls the next child package
So, because we are getting any data loaded, we know that the variable is populating. We have also validated that the correct amount of data is being loaded. Not sure if that helps much, but that's waht we have going on.
January 7, 2009 at 8:13 pm
Noffer (1/7/2009)
---So, I goggled the code and came up with the following page in the Microsoft Knowledgebase...
http://support.microsoft.com/kb/933835 Does this look like its the source of our problem? Or is this maybe just a generic error code?
"Goggled" - like it 😀
I suggest that you try the suggested fix in that KB article, it looks relevant to me and not difficult to implement.
Phil
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 8, 2009 at 9:48 am
We've asked the DBA to follow the specified security changes in the KB article, but were told that the article does not apply. It only applies to jobs being run as a sql agent job. Using DTEXEC will not invoke the sql agent proxy account, and that's how we've been told to run jobs around here, so if anyone has any insights, we're all ears, otherwise, we'll just leave the variables populated with the correct values... which defeats the purpose of a configuration file, but at least it works!
Thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply