Trying to parse an SSIS connection string in Expression Builder

Question

In SSIS I am using Project.params to parameterize a database connection string. Using the windows authentication this is simple, however I would like to do this with SQL authentication instead. The connection will be used in an Execute SQL task to execute a stored proc that just gets a list of data. Generating the parameter with SQL auth. The password is not stored (this is fine), so I added four parameters: Server, DBName, DBUser, DBPass. I want to be able to dynamically change any of these at runtime. So my thought was to build the connection string on the connection string property of the db connection manager. Issue is that it doesn't seem to want to validate. Here is an idea of what I'm trying to do.

Expression:"Data Source="+ @[$Project::ServerName]+";Initial Catalog="+ @[$Project::DBName]+";User="+ @[$Project::DBUserName]+";password=" +@[$Project::DBPassword]+";"

I think that is a connection string for SQLClient. SSIS usually uses a OleDBClient — KeithL
– KeithL, Commented Oct 4, 2019 at 21:24
i just grabbed a connection string example out of visual studio Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False; — Doug Coats
– Doug Coats, Commented Oct 4, 2019 at 21:54
If this is in SSIS, you already have a context sensitive option called Parameterize... to parameterise all of this stuff. sqlchick.com/entries/2015/1/4/… — Nick.Mc
– Nick.Mc, Commented Oct 5, 2019 at 1:56
I've used the built in parameterization option however testing locally with the parameter connection in that way I can't acquire a connection. Is there a way to maintain connectivity? — Elizabeth Polk
– Elizabeth Polk, Commented Oct 7, 2019 at 12:53

Mark Wojciechowicz · Accepted Answer · 2019-10-05 14:38:24Z

1

Sensitive parameters (the password) cannot be used in expressions. You will see an error message like this:

Expression cannot be evaluated.

The expression will not be evaluated because it contains sensitive parameter 
variable "$Package::pw". Verify that the expression is used properly
 and that it protects the sensitive information.

You can do as Nick suggested and parameterize the connection manager directly. I would suggest only parameterizing the connectionstring and password (separately). This would allow you to alter between windows and sql authentication.
i.e.

Windows: Connectionstring: Data Source=;Initial Catalog=;Provider=SQLNCLI11.1;Integrated Security=SSPI;

Password: leave blank

SQL: ConnectionString: Data Source=;Initial Catalog=;Provider=SQLNCLI11.1;User Id= Password:

answered Oct 5, 2019 at 14:38

Mark Wojciechowicz

4,4871 gold badge20 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Trying to parse an SSIS connection string in Expression Builder

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related