April 6, 2016 at 3:52 pm
Hi Folks
I am not a frequent user of SSIS and would appreciate your expert opinion. I have run into a issue at the time of deploying the SSIS packages
Few things to note
- the package is executed with this command - Dtexec /f "C:\folder\package.dtsx" /conf "C:\folder\Config.dtsconfig" /va
- Delay validation = true ( for the connection manager)
- Expressions used in the connection manager
The problem
The connection manager with the expression seem to say they are in offline mode. When i open it in SSDT on the deploy server the and attempt to reset things on the connection manager, by bringing it online it still keeps changing back to the offline mode and none of the changes i make to the connection manager( server name , db, username , password) persists for more than a few seconds.
Cheers
April 7, 2016 at 3:25 pm
Quick update. So if i remove the expressions from the connection manager things work in development mode ( username , password , server name ) persists.
Any thoughts are much appreciated.
April 8, 2016 at 1:42 pm
Happy days are here again. Thought i will share my pain with and success with rest of you.
So it seems, having expression in the connection manager does have a problems. With a Expressions in the connection manager it gives you all sorts of weird errors when you open the package in SSDT that is not the dev server you developed the package. I haven't still found a workaround for this. But to get over the hump i removed the expressions and change the connection string and add the a expressions again. I am sure there is a much smarter way to doing this so please share how things get done in the actual world.
The roadblock that caused me to log this and share my pain was something totally different. I actually didn't want say it out there in the real world as it seemed quite straight forward and i though it would be really silly to ask a configuration question.
If you did recall i was attempting to execute the dtsx package using dtexec. As most of you may be aware the /conf parameter can't override the initial validation that SSIS engine does to verify the XML configuration file values. The initial validation attempts to use the XML patch that was used at the development phase and when it can't find it bombs out.( this shouldn't be a problem if your development location and the deployment locations have the same path for the XML ie. C:\folder\Config.dtsconfig). In my case there was no guaranty) . You would find more details on the /configfile more in here https://technet.microsoft.com/en-us/library/ms141682(v=sql.120).aspx
The workaround was simple. I went about developing the package as usual ( still kept using the XML configuration ). When it was time to deploy the package, i unchecked the configuration option at design time and copied package to the deploy server and executed with a similar command to the following. Dtexec /f "C:\folder\package.dtsx" /conf "C:\folder\Config.dtsconfig" /decrypt password /va
Hope this helps someone. I am sure there is other ways of doing this but i am starting my weekend on a high.
Cheers
April 8, 2016 at 2:00 pm
jude.pieries (4/8/2016)
Happy days are here again. Thought i will share my pain with and success with rest of you.So it seems, having expression in the connection manager does have a problems. With a Expressions in the connection manager it gives you all sorts of weird errors when you open the package in SSDT that is not the dev server you developed the package. I haven't still found a workaround for this. But to get over the hump i removed the expressions and change the connection string and add the a expressions again. I am sure there is a much smarter way to doing this so please share how things get done in the actual world.
The roadblock that caused me to log this and share my pain was something totally different. I actually didn't want say it out there in the real world as it seemed quite straight forward and i though it would be really silly to ask a configuration question.
If you did recall i was attempting to execute the dtsx package using dtexec. As most of you may be aware the /conf parameter can't override the initial validation that SSIS engine does to verify the XML configuration file values. The initial validation attempts to use the XML patch that was used at the development phase and when it can't find it bombs out.( this shouldn't be a problem if your development location and the deployment locations have the same path for the XML ie. C:\folder\Config.dtsconfig). In my case there was no guaranty) . You would find more details on the /configfile more in here https://technet.microsoft.com/en-us/library/ms141682(v=sql.120).aspx
The workaround was simple. I went about developing the package as usual ( still kept using the XML configuration ). When it was time to deploy the package, i unchecked the configuration option at design time and copied package to the deploy server and executed with a similar command to the following. Dtexec /f "C:\folder\package.dtsx" /conf "C:\folder\Config.dtsconfig" /decrypt password /va
Hope this helps someone. I am sure there is other ways of doing this but i am starting my weekend on a high.
Cheers
Hi, looks like you've had some serious 'fun' with this 🙂
Just to give you an idea how I do this.
Connection managers are always configured to point to local or dev servers. So when you open them as a developer, they work. And don't point somewhere they should not (just in case your web cam falls onto your F5 key by accident ;-))
When packages get deployed to QA/Pre-Prod/Production, appropriate config should take care of the modified connection strings.
I use SSISDB / project deployment model and SSISDB environments to do all config of project and package parameters and connections. No external config files or environment variables are required.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 10, 2016 at 3:03 pm
+1 to that Phil.
Will keep SSISDB in the feature.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply