SSIS 2016 bids connection deployment issues

0
votes

enter image description hereI have a package that simply pulls data from a SQL source and dumps into another. Source system uses SQL credits to login. Environment 1: QA . Pull works correctly. Deployed to SSIS and ran the job manually and via SQL job agents and runs correctly. Environment 2: PROD. Locally in bids package runs correctly. But when I deploy and try to run the package via SSIS manually or SQL job agent it shows running and no data is inserted into underlying destination table. When I look at the detailed report of package execution I see that source connection connected in .0062 sec but destination connection shows nothing.

I have a feeling that in PROD envrionment my service account credentials are no good. And advice in right direction would be greatly appreciated. Thanks, Jin.

1
sql-serverssisdatabase-connectionbidsssis-2016
If your credentials didn't work in prod, the package would fail. Are you connections parameterized? If not, how are you handling configuration/parameterization? – Jeremy J.
Hi Jermey, I added a screen shot of the problem. 2 params at package level for start/stop dates. 2 connections also at param level for source and destination. Locally in bids the package runs fine. As you can see from screen shot above its the SA account that is having issues. 1) I think its either firewall issues of connecting via SA account to source. OR 2) Source needs to make this above SA account admin. – junketsu

1 Answers

0
votes

If the Protection Level is set to EncryptSensitiveWithUserKey, check the Creator in your SSIS package and compare it with the SQL Server Agent Service account.

usually happens when you are using Windows Authentication for your data source. In this case, need to make sure that the SQL Server Agent Services service account or your Proxy account has the permission to access your database.

Going to be working this off with network team to resolve. Will confirm if this fixed the issue.

Worse case: Firewall is rejecting connection on source db as it is on different domain.