July 3, 2023 at 12:50 pm
Background
I'm connecting to a MySQL database using ODBC connections. This database can only accepts secure connections, so each server that is connecting requires its own username and password. This is been setup successfully as I can design my ssis package in visual studio on server A.
SSIS packages are then deployed to Integration Services Catalog on server B. Both servers have ODBC drivers setup with different credentials that are successful.
Issue
I want to execute the ssis package on server B and override the credentials.
I would have thought if I went to the configure option of the package > connection manager and updated the UserName and Password options to serverb, it will run successfully, however it fails.
Is there something I'm doing wrong?
Config & error details attached
July 3, 2023 at 1:06 pm
I can't see anything wrong with what you are trying. Are you able to access Server B from VS, using the same credentials as you typed into SQL Agent?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 4, 2023 at 2:54 pm
Permissions will be denied to access server B from VS as each server has different login connections to the MySQL database as it's based on IP address of the server.
In my connection manager in VS, I'm connecting to a system DSN with server A as the user name (see attached image).
So the package will be deployed with that username onto server B.
I've not created a SQL Agent job yet as testing the package from Integration Services Catalog. I'm thinking it is here I can change the username and when I manually execute it will pass server B credentials as this servers ODBC connection is setup with server B as the uid? This is where it is failing.
The workaround I have for now is that within my VS, i've got a DEV & PRD environments, so i can set different uid in the project params and when I deploy to server B, it take PRD parameters and thus successful.
July 4, 2023 at 3:05 pm
Are you certain that access from server B has been configured correctly? You can test this by changing the default creds in VS and redeploying to server B.
The standard way of passing different sets of connection information at runtime, to packages deployed to SSISDB, is through the use of SSISDB environments. If you are not sure how to set these up, please take a look here: https://www.sqlservercentral.com/steps/ssis-catalog-environments-step-20-of-the-stairway-to-integration-services
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 6, 2023 at 11:54 am
yes , it looks like I need to setup environments.
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply