September 14, 2010 at 10:21 am
The packages I migrate to Prod have Sql Server Configuration built in and Delay Validation turned on wherever it is available. The packages know what server to find the configurations by looking at the value in a global variable (ServerName) that I have created.
It works great from within Visual Studio.
When migrating to Prod, I encounter 2 issues:
1) When I build the packages, the ServerName variable and datasources are pointing to Development Servers. When the DBA attempts to set up the sql agent job in Production, the packages display an error because Production does not have visibility to the Development server. Why is it validating this? Isn't this the whole purpose of setting up configurations? How do you get around that.
2) So, I built the packages changing the server to a server that Production can see. The only server available to me is our Clone server. This server mirrors our Production server and gets refreshed once per day. So, the DBA is able to set up the sql agent job and override the Data Sources. When the job is executing it fails because the Clone server doesn't have the db objects until the next day when it is refreshed. Why is it validating the values that it was built with rather than the values that we have used to override the Data Sources?
So, to sum it up, the Validation is making things difficult. The configuration works great but the validation defeats its purpose. I must be missing something.
September 14, 2010 at 2:09 pm
Hi Melissa
Can you please check the protection level of the package... I had similar errors, will post a doc when i get to work which might help
Cheers
vani
September 14, 2010 at 3:31 pm
I use Don'tSaveSensitive. I did realize that in this new package that I'm getting ready to deploy, I had the wrong ProtectionLevel. I will change that and retest.
I have to say though, that with my previous packages, having Don'tSaveSensitive, they still did the validation.
Another thing is, it seems to me that the only value we should need to override in the sql agent job, is the ServerName. Everything else should derive from this.
September 14, 2010 at 4:09 pm
Hi
What I usually do is change the package protection - Encrypt sensitive with password and provide a custom password...
then in the job when configuring the package - i enter this password in the job and it works ok... may be this could help not sure 🙂
gud luk
September 21, 2010 at 9:26 am
are you developing on the dev server or on a desktop?
i ususally develop on the dev server and use "localhost" in my connections...
September 21, 2010 at 9:33 am
Does your configuration include the connections, or just variables?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 24, 2010 at 11:35 pm
darth vodka (9/21/2010)
are you developing on the dev server or on a desktop?i ususally develop on the dev server and use "localhost" in my connections...
In my case I develop on a desktop and I use SQL Server aliases to control how it runs. So on my desktop and the dev server I create an alias called MainSQL that points to the dev server, and on the production server I create the alias pointing to the production server. Then I can run the package anywhere and it automatically runs against the correct server.
I understand that you can do something similar with configurations, but it seemed very complicated compared to using aliases.
October 1, 2010 at 3:03 pm
This would make it easier but we do not have localhost available in Prod
October 1, 2010 at 3:05 pm
my configuration includes the connection strings.
October 1, 2010 at 3:07 pm
Can you please tell me how you set up the alias and if you don't set up configurations how does your package pick it up?
October 1, 2010 at 3:23 pm
MelissaLevitt (10/1/2010)
my configuration includes the connection strings.
Are you using a config file, or an environment variable on the server (from which the package variable reads the info)?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 1, 2010 at 4:05 pm
MelissaLevitt (10/1/2010)
my configuration includes the connection strings.
What type of configuration are you using? XML, Environment, etc.
We use an XML file and only choose to save the Connection strings in them (none of the rest of the manager's properties). Then, we alter the XML file as necessary for each environment. Because the XML file is located in the same spot, and we call the XML file when running the package, it makes everything easy as pie.
October 1, 2010 at 4:10 pm
Brandie Tarvin (10/1/2010)
MelissaLevitt (10/1/2010)
my configuration includes the connection strings.What type of configuration are you using? XML, Environment, etc.
We use an XML file and only choose to save the Connection strings in them (none of the rest of the manager's properties). Then, we alter the XML file as necessary for each environment. Because the XML file is located in the same spot, and we call the XML file when running the package, it makes everything easy as pie.
thats exactly how we do it as well.
Or exactly how I am tying to get everyone to do it anyway.
---------------------------------------------------------------------
October 2, 2010 at 7:32 am
I'm using Sql Server configuration. I am also using a global variable called ServerName which tells the package what server to pick up the configurations from. Maybe this is the problem. I had to do this because when I initially implemented this package, the Production environment did not have Localhost. So, I could not setup configurations based on Localhost but rather a variable that would be set via the Sql Agent Job.
October 2, 2010 at 8:12 am
MelissaLevitt (10/2/2010)
I'm using Sql Server configuration. I am also using a global variable called ServerName which tells the package what server to pick up the configurations from. Maybe this is the problem. I had to do this because when I initially implemented this package, the Production environment did not have Localhost. So, I could not setup configurations based on Localhost but rather a variable that would be set via the Sql Agent Job.
The variable being passed into SSIS is somehow referring you to the wrong server. So, if you pass that in through the Agent Job, then I would start there to verify that the job was properly setup.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply