Workbench: Targeting Separate SQL Server Instances (dev, qa, prod)

owen_geoghegan

Hello,

 

I have a Workbench job bringing in data from our on-prem SQL Server setup via a connection builder. This works in production, but I'm wondering if it is possible to target separate (fragmented) instances of SQL Server?

Our SQL setup is as follows:
 
dbname01 = production
dbname01\dev
dbname01\qa

 

When targeting production, the job completes without issue. When attempting to target another instance, the job completes without issue, but still brings in production data (which it should not). When inspecting further, even when the connection string has been altered to target dbname01\dev or dbname01\qa, workbench ignores the modification and still targets the production instance.

 

Does anyone have experience with this, or a similar issue that may provide helpful insight? Your help is appreciated!

dthierjung

So using the connection builder won't work for some reason, however you can use the Connection String connection type and that will allow for slashes to be properly parsed in the server name.

 

Example:

 

Driver={SQL Server};Server=TestServer\dev;Database=TestDatabase