 Jamie Thomson wrote: |
|
-clipped-
That kinda makes sense does it not The whole point of encrypting it is so no-one can get at it. That's my understanding anyway. |
|
In a sense, I totally agree, however in I'm not trying to access the contents, and I have the permissions I need to backup/restore. The task is meant to transfer a database. Whether an object is encrypted or not should be irrelevant to the task. The transfer works in offline mode, but in online mode I get the above stated error. I'm not trying to access/modify it, I need to move it, and I have the SQL permissions to do so.
After some thought, I could increase the Minimum Error Count to 2, but my package will break if any more Ecrypted Stored Procs are added. I'd have to add another script that counts the number of encrypted procs and sets the Min Err count manually. Again, it seems like a LOT of work to copy a database, and if it's a known issue, should there have been functionality built into the xfer database task to compensate
I should just be able to drop-and-go with the xfer database task. Any intricacies related to the mechanics of performing the move should be handled by the task itself, and that should include encrypted procs.
| Jamie Thomson wrote: |
|
-clipped-
I've never used Copy SQL Server Objects Task but can you not loop over your objects using ForEach Loop's SMO Enumerator (or other) and move them one by one. |
|
Well that's really the main issue. The TablesList is a StringCollection property. Properties that are objects can't be set with expressions. This means that variables that are objects have little value other than to pass objects between script tasks. The ForEach loop sets a property on every iteration. There's no way for me to, within the same package, programmatically create a list and pass the list to the Copy SQL objects task. The only way for me to do this would be to create another package that manipulates this package programatically. I'm assuming there was a good reason why the Script Task doesn't have visibility outside of itself other than variables
It doesn't make sense to me why the script task can't programatically change properties of other tasks at run-time. Combined with the property/object/expression limitation, any properties within SSIS that are not strings or numbers can ONLY bet set at design time, unless you create another package that alters your package programatically. That's not what I wouldhave expected from SSIS, or MS for that matter. It's a LOT of work to perform mostly simple tasks. I want to build a list of objects, and pass that list to the Copy SQL objects task.
I would assume I could just create a variable, drop my list in that variable, and then assign that variable to a property with an expression. That seems logical and simple. I guess the point I'm trying to make is that if it was a design requirement that the tasks should only communicate with each other through variables, then limiting expressions to strings and numbers greatly imhibits the ability of SSIS, negating a lot of the value SSIS and variables provide. Also, if it was a known limitation that expressions couldn't assign obejcts to properties, the next logical step would be to allow the scripts to programatically change properties. Disallowing changing properties at run-time completely because of the limitations of the expression language (which arguably shouldn't even exist) or because someone chose to limit the visibility of the script object seems like a decision made in err.
| Jamie Thomson wrote: |
|
What else would you like to store I haven't yet come across anything that cannot be stored in vars. |
|
In theory, you should be able to store and assign any object type that can also be a Task Property. As of right now, you can't programatically alter any properties unless they are a number or string, because of the limitation.
| Jamie Thomson wrote: |
|
Yeah alot of people don't like this but there are good justifications for it. Quite simply, the expression syntax is optimised for what it is provided for. Greasy fast speed. And faster than .Net. Hopefully someone from MSFT will reply will teh proper justification. |
|
Not that I don't believe you, but it's hard for me to believe speed was the driving factor. .NET is compiled. An expression syntax would be interpreted, unless SSIS compiles it (which again, would seem like they're re-inventing the wheel). I would assume that once a package is run for the first time, all of the .NET would be compiled and cached. At that point I can't see it being slower at all than an interpreted expression syntax. If they are compiling the syntax, it's hard for me to believe that the minute amount of speed in using a custom-compilation would offset the functionality provided by enabling the .NET framework.
| Jamie Thomson wrote: |
|
It is a problem yeah. The reason is that the scripting environment is VSA - something completely seperate to SSIS. VSA only supports VB.Net. The inclusion of scripting was late in the dev cycle for SSIS v1 and hence they didn't have time to build something that used C#. Given the choice between having VB.Net or nothing at all I know what I'd got for. In the next version VSA is expected to get replaced with VSTO and this will give full C# support. |
|
In theory, wouldn't adding C# support to VSA also implicitly add C# support in 2005 Is there a reason they're moving to another framework rather than just adding C# support to VSA
I also found a couple of bugs so far. Where do I report them. If I try and set the ListTables property from the VS property window, when I hit ADD, I get a "Could not find a ctor for the String type" error. When adding an item to the project, if I click around and then press cancel, I get some "could not find proxy" error or something like that.
Thanks for your help so far Jamie.
Jamie Thomson wrote:
