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.