asked
Calling an SSIS Package from DTExec - Security
I have created an SSIS package that needs to run as a SQL Server User. The package runs fine from within BIDS and when calling from a job and being able to add in the security information. However as the SSIS Package is saved in the file system I can't see how to call it from DTExec and pass it the user credentials to call it with. All connections within the package are set to the correct user however this appears to be overridden when run from the commandline. This is what I'm using in the Command line: DTExec /file "D:\Annette\SSISDataImports\SSISDataImport.dtsx" /set \package.variables[User::SourceDB_A].Value;Wilma /set \package.variables[User::SourceServer_A].Value;ALLENA1 /set \package.variables[User::SourceDB_B].Value;Fred /set \package.variables[User::SourceServer_B].Value;ALLENA1 I have changed the names of some of the variables, there is actually 10 variables to pass in but you get the idea!! It works perfectly from the command line however it uses windows authentication and not the required user. Any help would be greatfully received.
DTEXEC always runs under a domain account. You can connect to a SQL Server database using a SQL Server user, but you need to specify this in the connection manager or in a configuration.
I can't see how to do this because when I add the User and Password in the command line it says this can't be done using a package saved in the file system. Can you help please?
The username and password for DTEXEC are not for running the package as a SQL Server user. It is used to retrieve the package from the MSDB database when you store your SSIS package inside SQL Server. If you want to use a SQL Server user to connect to a database, you need to specify the credentials in the connection manager and set the protection level of the package to save sensitive data. The other option is to use a package configuration to pass the credentials to the connection manager (the preferred method in my opinion).
13 People are following this question.
