CODEWITHSUNDEEP
sql-serverssissql-server-2012
vstrien
vstrien

Reputation: 2605

How to retrieve the SQL Server instance name from within a SSIS package

From SQL Server 2012 on, SSIS can be deployed to a SSIS catalog. Behind the scenes, this catalog is just a SQL Server database.

In one of my packages, I currently have an expression that makes up a connection string. To set the connection to the right server instance, the connection string of a connection manager is currently defined by the following expression:

"Data Source=" + @[$Project::ServerInstance] + ";Initial Catalog=InitialCatalog;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"

So the SQL Server instance is provided by a project parameter, which is in turn filled by an environment variable.

This seems unnecessary to me: the package is stored in a database, which runs already within a SQL Server instance. I just need the name of that particular instance. Is that name accessible from within SSIS in project deployment mode?

Upvotes: 2

Views: 1943

Answers (1)

Esc
Esc

Reputation: 195

I had the same issue and I did not find a way to get that value by native SSIS means.

My solution: I just wrote 

Data Source=(local);

Downside of this solution is in necessity of your development computer to also have a local SQL Server database with all tables.

Possible alternative solution: You might be able to get something using Scrip Task and C# libraries. I never ventured into this but I am sure of its big potential.

Upvotes: 1

Related Questions