February 23, 2010 at 4:24 pm
Hello,
I've searched the internet far and wide, tried infinite escape combinations, but can't get Dtexec to work with this parameter. I'm at a loss.
I have a parent package that calls other packages. We pass into this parent package the dtexec string it needs to execute the child package. It executes dtexec from an Execute Process task, appending the parameter string to call the child package.
That's it in a nutshell, and its worked pretty well until now when we added some parameters. Whenever we add the /CONN parameter to the string it blows up.
Here's part of what I want to pass in as a /SET parameter:
/CONN "VirtusCommunication_dynamic";"Data Source=200168-DEV2\CDO_SUITE_LAST_N;Initial Catalog=VirtusComm;Integrated Security=True;"
So the unescaped call looks like this:
dtexec /SQL "Package1" /SERVER "SERVER-DEV2\mydb" /SET Package.Variables[User::parm_pkg_dtexec_args].Properties[Value];/CONN "CommConn_dynamic";"Data Source=SERVER-DEV2\dev;Initial Catalog=CommDB;Integrated Security=True;"
I've tried all kinds of escaping and such to no avail, so I ask Dtexecui and it says the above call should be this:
dtexec /SQL Package1 /SERVER "SERVER-DEV2\mydb" /SET "\Package.Variables[User::parm_pkg_dtexec_args].Properties[Value]";"\"/CONN ""CommConn_dynamic"";""Data Source=SERVER-DEV2\dev;Initial Catalog=CommDB;Integrated Security=True;""\""
But that fails with Option "Source=SERVER" is not valid..
Please help if you have any ideas, I'm about to pull all my hair out.
Thanks,
Scott
February 23, 2010 at 6:27 pm
I am not sure how to get around this the way you've done it. However, I have an alternate way, why don't you write the command into a table and then pass a value into the package, it then uses that value to retrieve the command from the table and use it, this way you don't have to try and pass it through the command line but you can still get it there.
You could even create the table in tempdb, build a sproc to manage the process, if the table doesn't exist it creates it and then fills it, if it does exist fill it.
Thoughts?
CEWII
February 23, 2010 at 9:40 pm
Interesting, would work fine but I want to keep these as stand alone as possible. I think that solution might make unit testing and running these individually on the fly ( when there's trouble ) more difficult.
February 23, 2010 at 10:09 pm
I don't think I agree about testing. Trying to manipulate a command line to pass to another seems to be pretty problematic..
I understand the stand-alone aspect, you could certainly tie it in to this process alone, I just think that passing a single very simple value that could be logged as well as the extracted command would be a very simple solution and not require what you have been trying to make work. It seems you have put a lot of effort into trying to do it the way you've laid out but I think other alternatives should be considered and the one I suggested is only one possible.
CEWII
February 23, 2010 at 10:40 pm
Fair enough, but the beauty of it is the package gets everything it needs in one manner, from the parent variables. I used to get some of the metadata from the database which was kept in tables similar to what you propose, but more static. I just don't want to mix and match - and I like that the package can run without a database (for its config metadata at least ).
Config files could probably do some of what I need. This is one component I wouldn't mind mixing in, but they seem a bit problematic what with the absolute paths and all. Absolutes are necessary perhaps, but I couldn't find many workarounds for this shortcoming when you have multiple environments, much less multiple instances in one environment. For example the absolute path has to be the same in all environments, so assuming that's possible on all servers, what about when dev/test share the same box but need distinct connection config files?
Sorry for the little rant, just frustrated with all the SSIS configurations. They were a great idea but none of them seem to quite go all the way.
February 23, 2010 at 10:54 pm
Thats ok, I've done this in a lot of different ways, I just didn't want to get stuck on a single solution..
I like parent package variables except they are a hassle to debug.
Config files are too static for stuff like this. Great for settings that could change but not often..
CEWII
February 23, 2010 at 11:14 pm
Your ideas are much appreciated!
Actually I may be able to get around this by passing the connection metadata into a separate parent package variable. The dtexec arg string is ultimately built by an expression anyway, that concatenates a few goodies.
The parent package just iterates said folder and hands off matching filenames to the the child package for processing, then archives the files. All parameter driven. This could also be easily driven by a sproc instead.
All our ideas mean more work though, and I was really hoping to resolve it without going down other paths. One would think there'd be some rhyme or reason to the dtexec escaping.
February 24, 2010 at 7:54 am
You would think there would be.. I just try and avoid it altogether..
CEWII
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply