I 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.
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.