I have created an SSIS package (stored on the file system) that loads a csv file into an SQL table. The package was created with protectionlevel EncryptSensitiveWithPassword. I run a 'bat' file containing the following:
cd c:\Program Files (x86)\Microsoft SQL Server\100\DTS
DTExec.exe /f "C:\package.dtsx" /De "packagepassword"
This all appears to work and loads the data. I would like to do this without password protecting the package. I don't want to do it with a userkey, I want to put it on a couple of other workstations. If I use DontSaveSensitive, I lose my database connection information and the package doesn't work when I run the 'bat' file.
Is there a way of doing this without password protecting the package?
You can put the sensitive information into a package configuration, but that is going to possibly expose that information to prying eyes. There are a few methods using SQL Encryption for handling this.
You can use the EncryptSensitiveWithUserKey option. You would have to set the sensitive information when developing the package running as a particular user. Then you run the package as that user.
Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com
As long as these workstation and SQL Server belongs to the same domain or forest, you can use Windows authentication instead of SQL Server authentication.
In such scenario you grant privileges to windows logins of users who are allowed to run the package and domain controller will take care of the rest.
With SQL Server authentication you have to save password somewhere...
You may also save package without sensitive and then set connection string in batch file, but it is even worse...
dtexec .... /SET "\Package.Connections[ConnectionManagerName].Properties[ConnectionString]";"connection with password"