February 10, 2016 at 2:37 am
Hi,
I'm deploying some SSIS solutions to the SSIS catalog by the project deployment model.
I'd like to use the environment variables in the catalog in order to parameterize the SSIS solutions and
to act on the prod environment in a separate manner rather than the dev environment.
I've performed some proofs and I'd like to understand when it is right the environment variables.
In particular, I want to use them to parameterize the connection string to a SQL Server source,
specifying the name of the data source, the initial catalog, the SQL Server login (no Windows account)
and the corresponding passwor to pass as a sensitive data (not in cleared test).
Obviously, I need to avoid a login failed issue.
For your experiences, how can I solve this issue?
I need to obtain the best practices to deploy a SSIS solution in the catalog of a prod environment.
Many thanks
February 10, 2016 at 9:07 pm
What problem are you experiencing?
Are you saying you're getting a login failed error?
February 11, 2016 at 1:41 am
Hi,
based on experiences I'd like to understand better when it is appropriate using environment variables in the SSIS catalog and the project parameter, thinking to obtain some best practices to manage the deployment phase and the prod environment.
I'm trying to create environment variable to parameterize SQL server connection string and the corresponding password to access by two separate variables. In particular, in the SQL Server connection string I need to write a SQL Server login.
Sometimes it occurs login failed error for jobs that call SSIS packages in the catalog and use the related environment variables.
Have you experienced with the environment variables of the SSIS catalog to parameterize SQL Server connection string and password (handled as a sensitive data)?
Thanks
February 11, 2016 at 2:08 am
In terms of best practice I like to parameterize variables that I know tie my package or project to a particular environment. Things like connection strings, file paths and user accounts are obvious candidates. By doing that you don't have to modify the package(s) itself when promoting to various environments. You're just configuring the environment variables with their correct values.
You're able to pass in a username and password variable when configuring the connection strings and you can also mark the password as sensitive to prevent it from being displayed.
With regards to your error when running the package, you'd have to provide your error message as this could be a number of things. I also like to use a proxy account and grant only the necessary permissions required to run the SSIS package.
February 11, 2016 at 3:58 am
Hi,
I'm trying to use an environment variable for SQL Server connection string (data source + initial catalog + SQL Server login)
and an environment variable for the corresponding (sensitive) password.
Sometimes (not always) I've obtained a "Login failed for user ..." error. A preceeding message says:
"Failed to acquire connection "SQL_Server_Connection". Connection may not be configured correctly or you may not have the right
permissions on this connection."
Obviously, the connection string and the password have the right value!
Have you encountered a such error for a similar case (connection string + sql server login + environment variables)?
Perhaps, the (sensitive) password is lost if passed by an environment variable. Perhaps, it is more right to write it manually for the project parameter deployed in the SSIS catalog with the solution.
For these reasons, I'd like to read replies from who has experienced with this subject.
In a such scenario, where I need to use SQL Server login and not Windows credential, perhaps using a SSIS proxy isn't the better solution.
Many thanks
February 11, 2016 at 2:27 pm
I've seen a similar error with other types of connections where the wrong runtime is being used. Maybe you could check that?
Otherwise confirm you've correctly mapped the environment variables to the project parameters and that the appropriate DB permissions are in place.
February 12, 2016 at 2:22 am
Mapping between environment variables and project parameters is OK.
DB permissions are OK.
Thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply