Integration Services Connection Password

[rebelknight]

I have a straight forward BIDS developed package that performs an 'Execute T-SQL Statement' followed by a File System Task.

It works fine when I run it in BIDS.

I have imported the package to SQL Server as a File System package and set up an SQL Agent scheduled job to run it.

The problem is that the job fails on the username and password validation of the T-SQl statement. The SQL Server authenticated connetion does not save the password with the SQL server login name. I have used 'sa' up to now which I know needs to change but why is te password dropped and how can I configure an SQL authenticated connection in a package that will not drop the password?

 

[MDXer]

use package configurations to set your connection info.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[rebelknight]

Hmmmm.

I had considered that but it semed less secure to store connection details in an XML file rather than include it in the package. However I suppose there is no difference but it seems a drag to now have to use two files to set up a T-SQL task that DTS could have done in just one .dts file?.

Is this the standard way of handling a connection with an SQL Server 2005 package? Always a .dts and and xml config file?

 

[rebelknight]

I've read that setting a password against the package to encrypt it will save the password but it has not worked for me.

Anybody managed to achieve this?

 

[rebelknight]

OK sorted it.

Setting any level of password encryption does retain the password in the package. The pakage was falling over on a second error which I had not picked up.