Configurations

I have read many posts about configurations. However, in practice, I cannot solve a problem that is bothering me. I have connection managers (that manage SQL Server 2005 connections) configured in a local package. I have ProtectionLevel = 1 (By the way, where do you state that you want it "DontSaveSensitive", etc?). When I deploy the package to another computer (using the deployment utility) though, I get the error "The AcquireConnection method call to the connection manager ... failed". Could someone tell me, very explicitly, how can I use configurations to solve this problem? Or are there other ways to solve the problem? The problem, of course, is that the connection managers' passwords aren't being migrated from a computer o another. Thanks a lot.

Pedro Martins

<quote>

By the way, where do you state that you want it "DontSaveSensitive", etc?

</quote>

Right-click in any empty area of the control flow tab, and select properties.

Under Security section, you will see "ProtectionLevel" property.

STEP 1:

If you want to use DontSaveSensitive level, then define a variable (say DBConnection) and sets its value to this entire string: Data Source=dbserver\dbinstance;User ID=dbuser;Password=dbpassword;Initial Catalog=YourDBName;Provider=SQLNCLI.1;Auto Translate=False

STEP 2:

Now, in the "Connection Managers" tab (usually in a thin window at the bottom), select your database connection manager, right-click and select properties. You will see "Expressions", select it - hit the ... (three dots) that appear next to it. A property expression editor window appears. In the "Property" dropdown, select "ConnectionString". Hit the ... for the expression next to it. From the list of variables in the left hand side pane, drag and drop the above variable (DBConnection) in the "Expressions:" area. Hit OK.

STEP 3: Right click in an empty area in the Control Flow tab, and select "Package Configurations...". Add a new configuration using the wizard and make sure that you select the above variable (DBConnection) as part of the configuration.

HTH,

Nitesh

|||

Can you be a little bit more specific about step 3? Must I create an XML file? What must I do specifically?

Regards,

Pedro Martins

|||

yes, you need to create an XML configuration file!

-Jamie

|||

i was trying to build my project when I got the following error:

Error 8 System.ApplicationException: Could not copy file "\\Bi4all002\cpm@.bi4all\Configura??es\BIALL2006Staging.dtsConfig" to the deployment utility output directory "D:\Visual Studio 2005\Projects\BI4ALL IS\BI4ALL IS\bin\Deployment". > System.IO.IOException: The file 'D:\Visual Studio 2005\Projects\BI4ALL IS\BI4ALL IS\bin\Deployment\BIALL2006Staging.dtsConfig' already exists. at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.File.InternalCopy(String sourceFileName, String destFileName, Boolean overwrite) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CopyFiles(ICollection fileNames, String outputPath) End of inner exception stack trace at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CopyFiles(ICollection fileNames, String outputPath) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CreateDeploymentUtility(IOutputWindow outputWindow) 0 0

As my project consists of various packages, I created configurations for all of them. Is that correct or not? Does it have something to do the problem?

Regards,

Pedro Martins

|||

Pedro Martins wrote:

As my project consists of various packages, I created configurations for all of them. Is that correct or not?

There is no right or wrong answer. Generally speaking if you are connecting to the same "thing" from multiple packages then you should share a single configuration file between them.

-Jamie

|||

Yes, I did just that. I just don't understand the error I am receiving. Do you have any hints?

Pedro Martins