dtsConfig file not dynamically picked up

Question

dtsConfig file not dynamically picked up

Express12

SSCoach

Points: 18514
More actions
August 23, 2007 at 1:38 pm

#118299

Trying to set up an SSIS pkg using XML .dtsConfig files. Works fine on ServerA. I enable Package Configs from Control Flow tab referencing my ServerA configFiles on e:\configFiles drive, rename my connection manager connections to reference the newly added configFiles, etc.. When I DEPLOY the pkg (via manifest) to target ServerB with the .dtConfig files also located in e:\configFiles -- the pkg still operates against ServerA. Am I missing something? (I'd like the migration of SSIS pkg's across servers to be completely transparent -- meaning there's no changes necessary to connection strings.
BT

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Dan Colbert-387424 SSCrazy Points: 2195 More actions · Answer 1

It's always a challenge to troubleshoot from a distance, but here goes...

Try checking the permissions on ServerB to make sure the Agent account can access that directory. If it doesn't have access, the package will fire with the default connection settings. (That may actually be happening on ServerA, too - but the default config is set up right.)

We use a .dtsConfig file, but reference it using an Environment variable (set for each server/dev/test machine that runs our packages). And the only thing the .dtsConfig file sets is the connection string for our SQL Server configuration types. That way, nothing ever needs to change when pushing from Dev to Test to Prod. Have you tried that approach?

Hope this helps,

Dan

Rob McCauley SSC Veteran Points: 205 More actions · Answer 2

I'm having a similar problem. I use a package configuration, specify the .dtsConfig file, and it works fine on my PC.

I cracked open the .dtsx and notice the configuration file reference is hardcoded including the full file path!

i.e.

C:\code\reporting\ReportingETL\reportingDB.dtsConfig

I can't deploy the package to the server like this. The deploy folder will be different.

I wish I were able to specify a local configuration file that will deploy in the same folder as the package but it is not possible in the configuration wizard,

i.e.

.\reportingDB.dtsConfig

This seems to limit the usefulness of the configuration file concept.

kramaswamy SSCoach Points: 18181 More actions · Answer 3

Yeah I find it's much better to use a database table for configuration variables. That way it becomes easily portable to a new machine.

Rob McCauley SSC Veteran Points: 205 More actions · Answer 4

I agree, and most of the application settings in my app do come from the database. However how do I know which database to connect to initially? It is usually "localhost", but some customers may deploy it to an alternate instance (localhost\instance) and so this type of setting should be configurable.

Microsoft confirms the full path hardcoding of the configuration file location, and recommends using an "Indirect Configuration" (see link) with the file location in an environment variable.. something I'd like to avoid.

http://msdn.microsoft.com/en-us/library/ms141682.aspx