I am trying to develop an SSIS package that will be run on several different machines. I am stuck because I do not understand how to create a connection string that doesn't explicitly point to one machine.
An example connection string is below:
Data Source=myPC;User ID=Admin;Initial Catalog=TestLocal;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;
When I create a new connection in visual studio it asks for the server name…I want the server name to be equal to whatever the server name is where my package is being run. Is this possible?
edit:
The package is being executed as a multiserver sql server agent job. My machine is the master(msx). Should I be looking at making changes to the actual ssis package in visual studio, or focusing on changes in the ssms job? My intuition points me towards editing the package but this is where I have been left scratching my head.
following this guide: https://www.sqlshack.com/parameterizing-database-connection-sql-server-integration-services/ still has the package being called by the user on the target machine, but the package is executed on my machine… headscratcher..
This screenshot below we can see that the job is started on My Machine and is called by Machine2. My thought process here is that now Machine2 should be running this job ON Machine2. You can see that I have included a parameter called ServerName and have set the value to localhost thinking that this would cause the package to be executed on the local machine of the user that called it (in this case Machine2)?..
The result of the run in the image above has the database of Machine1 being updated, when we really want Machine2 to be receiving the update.
Best Answer
The dtsx package runs where you install and execute it. Usually in SSISDB. Then you can create a sql server agent job to run it.
The connection string specifies where the action (executing some sql script, or as a data source/destination in a dataflow as examples) will be executed.
You can install the package in every server you want and then refer in the connection string for example to localhost to run it against the same server.
You can also manage parameters and design a parametric connection string as you pointed out.
If you have a list of servers to contact with the connection string, you have to loop into the list and place the values in the connection string and then execute the task you want using parameterized connection string. So you will contact every server you want and execute what you need. But there will be always only one server hat runs the solution.
If you have a job in every of your servers that point to a local SSIDB with a local dtsx to execute against a local instance you have to manage them with a local environment that define a good connection string for each one of them.