Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-01-04 : 08:24:16
|
| David writes "Problem with run-time execution of design-time Workflow settings between Dynamic Properties/ActiveX Script tasks and their dependent Data TransformationMy DTS Package is implemented as a Linked Server using the DSO Rowset Provider. The package contains an ActiveX script that decrypts a password from an encrypted password file and sets the Connection password. This ActiveX script is setup to run first; i.e using an On Success workflow connector between the script and the input Connection. Am getting a logon failure message; have absolutely verified that the password is correct. In fact if I run the package first from the SEM, and then save it - the package runs as expected if a global variable has been used to pass the password to the Connection.Otherwise the package is behaving as if it is attempting to do it's data processing BEFORE the Connection password field has been populated.Have attempted to put the script into a Dynamics properties task, and set it up as a precedent for the Data transform task - that doesn't work either !What's particularly interesting is that I have other packages where this strategy works - the only difference being is that the others are not using the DSO Rowset Provider, and are database to flat file transformations - not database to database transformations (password setting is for the source database)Have even attempted to set the Packages max parallel tasks to 1, without success - still behaves as if the Connection is taking place BEFORE the password field is populated in spite of the appropriate workflow setup ??? Have also verified that ConnectImmediate=0 for this connection - which I guess is the default.This is being done in SQL Server 2000 SP2, using a structured file package.Have spent an awful amount of time trying to get this to work properly. My only option appears to be what I referred to earlier - to actually run the Package in the SEM - then just the decryption/password population step runs, the transformation doesn't even attemp to run, and then save the package - which is definitely not secure. BTW a global variable linked to a password field, revelas the password in plain text !!!! In fact while in the SEM if I turn off the DSO Rowset Provider and run the Package, it runs thru to completion, executing in the correct sequence.Has ANYBODY comes across this problem before, and if so what was your solution.Woulld be grateful to anybody that could provide any help with this issue !" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-01-04 : 12:08:05
|
| You could set the properties from an sp before the package is runseehttp://www.nigelrivett.net/DTS/sp_oacreateLoadDTSpackage.htmlSounds a bit like your activex script is setting the password on the wrong connection.Are you sure there's nothing in the package that uses the same connection and doesn't have the precedence?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
|
|
DTS in SQL Server 2000 (workflow sequencing problem)