February 25, 2010 at 8:56 pm
Hi,
I have a package runs on Windows 2003 Server and SQL 2005. The package tries to move files from web to SQL server, upload to staging table, procedures to process incoming records and finally update some tables.
Usually we have dtsx points dev environment (SQL server and file path) along with XML config file. Dev hands dtsx and config file to QA; QA just updated config file with QA SQL/path, the package works fine on QA. We then changed config file with production server and path before deploying to production. We deployed several other packages in the same way; they all work. Only this one, somehow it still points to dev environment. Config file has right prod info. Is there any other place I missed? Or any hint? Thanks.
Chris
February 25, 2010 at 9:11 pm
Have used a similar method before, only real difference is that ALL the config files were source controlled, and the DEV provided them all.
Have you verified the package is configured to use config files? Is the file in the right place? Is it configured to look at the right place. How is the package being called?
CEWII
February 25, 2010 at 11:00 pm
Hi Elliott,
Thanks for helping out.
Yes, one of my colleague opened dtsx thru VS and confirmed package was configured with XML. However, I found later there were 2 warning messages in VS:
1) …dtsx. The Configuration name”V:\SSISConfig\Import_xxx.dtsConfig” is not valid. Click the configuration….
2) Warniung loading Import_xxx.dtsx: Failed to load at least one of the configuration….
Made me wonder if drive, folder name and config file name between setup and actual match. I have to verify that tomorrow. But again, all we did was update content of config file with prod environment; there is no change to name, location….. It works in QA….
Also we opened dtsx under MSDB SSIS, General folder points to right prod server but connection manager folder has dev setting. We clicked check box and tried to change the setting but it did not work.
Just wonder if there is something else I could check… Again thanks.
Chris
February 26, 2010 at 8:41 am
Given that error message, you might verify that the dtsConfig file hasn't been corrupted, it doesn't take much, a > out of place can do it.
My thought is tied to order. By default a package will run with any values it has setup at design-time. Those values can be replaced by command line parameters (such as from a SQL agent job). And then package configurations. I'm wondering if some confluence of the order of application and the "error" with the file are all playing together. I'm wondering what would happen if the config file just wasn't available, I think you would get a different error.
Does that make any sense?
CEWII
March 4, 2010 at 2:38 pm
Hi Elliott,
Yes, it makes sense to me.
The package cut/paste csv files from web to SQL server for processing. After adding SQL Agent to web folder, I was able to see data uploaded into staging; however, it failed with connection manager issue again (could not recreate in dev nor QA). I turned on DelayValidation in dtsx itself. Hopefully it will fix rest of issues.
Thanks again,
Chris
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply