October 24, 2008 at 4:37 pm
I have developed packages and have tested them on a test server in conjunction with using XML configuration files. Everything has worked fine in development. When I go to deploy to the production server I change the values in the configuration file to be that of the production server and everything seems to work just fine.
However, when I run the package it doesn't execute on the production server but rather for some reason still executes on the development server. When installing the package I did notice that the Path has the development sever name in the path but I don't see a way to change it.
I'm sure I'm missing something simple but I am struggling with why this isn't working.
Thanks.
October 24, 2008 at 4:53 pm
Hi,
Did you copy the configuration files from test to development. Make sure you change all the values here...connection string values etc....At run time the values are taken from this configuration file..
VG
October 27, 2008 at 4:05 pm
I did copy the configuration files and used the manifest to install the packages on the production server. When I did that I changed the values from the test server to the production server. The package runs but still is runs all the actions on the test server.
If I open the configuration files up in a editor and change the values there then the package fails.
October 27, 2008 at 4:23 pm
Hi,
What is the error message you are getting after changing the values in configuration files and running , because that is how it should be...May be a permission problem..
-VG
October 28, 2008 at 5:25 am
This sounds like the design time configurations are being used, ie. the Dev environment.
From your discussion it sounds like you are not that familiar with SSIS configs, which is ok, it explains you understandable confusion.
Here's what to do:
Determine the location on Dev of the config files.
Determine the location on Production of the config files.
If you don't want to have to configure the location of the config files (via Operating System variable or the like) then make these paths the SAME on Dev and Live and copy the config files to all locations, e.g. C:\Config\SSIS\cfg_DBCon.dtsconfig, C:\Config\SSIS\cfg_LoggingPath.dtsconfig. Edit the files so they are correct for the SQL server they are on.
Set the Package configurations up to use those locations. For a detailed dicussion on using Package Configurations and variables, then using those variables in Expressions, etc see Conchango.Com (http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx and the Configurations like to blogs on sqlis.com)
If you are running the packages in jobs you can (will) configure the location of the Config files on the server RUNNING THE JOB.
If you want to run the SSIS Package interactively then you will confiure the lcoations again for that run (right click server in SSMS and select Run Package and set option there).
It might be worth noting on the package in a annotation that it is using configurations for DB connections, etc. Someone who isn't aware will find it's behaviour very strange - chaning and setting connections to different servers on the connetion has no effect when run (because the config is overridding any deisgn-time setttings).
HTH's a bit, JB
As an aside I have found the configs to be useful but too cumbersome for our development environment. The lack of an integrated Config management tool to swtich between 1 of 5 development environments means using XML that some are unhappy with. It should be in VS.
October 28, 2008 at 9:15 am
Johnathan,
Thanks for your input. I've read all the links you included and I I'm confused because, as far as I can tell, I am following everything you said as well as the links and still no luck. The configurations are in the same location on both the dev and production servers. In the configuration file I am specifying the ConnectionString and ServerName. On the production server I am opening the config file in notepad and changing the Data Source in the ConnectionString to the production server and the ServerName attributes. (As a side note, when installing the packages it appears that the wizard allows you to change these attributes in the configurations but that doesn't seem to work.)
After having changed these attributes in the configuration files the package fails when running it as a job. It is indicating that it needs new Metadata. The same thing happens if I change the configuration files and then try to re-install them. I get validation warnings in the installation indicating the same thing. Here is the actual error from the history log:
Executed as user: EPNT\SQL2005. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:07:16 AM Error: 2008-10-28 10:07:17.37 Code: 0xC004706B Source: DTF Loading Employee Dimension DTS.Pipeline Description: "component "OLE_SRC_Employee" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA". End Error Error: 2008-10-28 10:07:17.37 Code: 0xC004700C Source: DTF Loading Employee Dimension DTS.Pipeline Description: One or more component failed validation. End Error Error: 2008-10-28 10:07:17.37 Code: 0xC0024107 Source: DTF Loading Employee Dimension Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:07:16 AM Finished: 10:07:17 AM Elapsed: 0.828 seconds. The package execution failed. The step failed.
Conceptually, configuration files sound great and are a usefull tool. Unfortunately, I am missing something and it is probably a pretty easy item but I just can't seem to find it. Thanks for your help and hopefully you'll be able to indicate what I'm missing.
October 28, 2008 at 10:21 am
Hi,
One more suggestion, In metadata, defalut is "fail component on failure : and change it to Ingnore falire and then save tha package re-deploy it. While deploying you can also make sure when it comes to config files wizard step, open each config files and see what values it is picking..
VG
October 28, 2008 at 10:29 am
ahlinger,
Are you sure you have setup the configuration files in the job?
If you haven't doen that it would exactly match the symptoms you describe and is a step to easily miss.
Regards
JB
October 28, 2008 at 11:46 am
Current update and I'm going to do more with this this afternoon, but I found that if I make the user a db_owner in the source database then all the metadata issues go away and it works on the production server. Not sure if that makes sense or not but it's working.
As far as listing the configurations in the job I hadn't done that and still haven't but it's working now. If the package already knows to use configurations then why would you need to put them in the job step again anyway?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply